SQL FOREIGN KEY Constraint

A FOREIGN KEY is a key in another table that relates from the PRIMARY KEY in the parent table. A Foreign Key is needed in the related table to connect data from the primary table.

Let's take a look at the following example

Course Table

Students Table

From the above table, Smith has taken the course Math 101 and the other three English 101.

Here's how to create Foreign Key and Primary Key.

Example For Oracle/MS Access/SQL Server

  CREATE TABLE Students
  (
  StudentID Int  PRIMARY KEY,
  Firstname text NOT NULL,
  Lastname text NOT NULL,
 
  CourseID int FOREIGN KEY (CourseID) REFERENCES Course(CourseID)
  )

Example For MySQL

  CREATE TABLE Students
  (
  StudentID Int,
  Firstname text NOT NULL,
  Lastname text NOT NULL, CourseID int NOT NULL,
  PRIMARY KEY (StudentID),
  FOREIGN KEY (CourseID) REFERENCES Course(CourseID)
  )

ALTER TABLE Syntax

Example

  ALTER TABLE Students
  ADD FOREIGN KEY (CourseID)
  REFERENCES Course(CourseID)

TO REMOVE FOREIGN KEY

Example

  ALTER TABLE Students
  DROP FOREIGN KEY