SQL FOREIGN KEY Keyword

The SQL FOREIGN KEY keyword is a constraint and it is used to link two tables together. A FOREIGN KEY consist of single or multiple columns in one table that refers to the PRIMARY KEY in another table. The table containing the foreign key is known as the child table. The table which is referenced is called the referenced or parent table.

The FOREIGN KEY constraint helps to preserve links between two tables. It prevents invalid data from being inserted in the foreign key column. The value which can be inserted into the foreign key column must be the values contained in the referenced table.

Consider a database containing tables called Employee and Contact_Info with the following records:

Table 1: Employee table

Table 2: Contact_Info table

The EmpID column in the Employee table is the PRIMARY KEY in the Employee table. The EmpID column in the Contact_Info table is a FOREIGN KEY in the Contact_Info table.

SQL FOREIGN KEY constraint with CREATE TABLE

The below mentioned SQL code creates the Contact_Info table with FOREIGN KEY constraint applied on column EmpID.

SQL Server / Oracle / MS Access

CREATE TABLE Contact_Info (
  Phone_Number VARCHAR(100),
  EmpID INT NOT NULL FOREIGN KEY REFERENCES Employee(EmpID),
  Address VARCHAR(255)
);

MySQL

CREATE TABLE Contact_Info (
  Phone_Number VARCHAR(100),
  EmpID INT NOT NULL,
  Address VARCHAR(255),
  FOREIGN KEY (EmpID) REFERENCES Employee(EmpID)
);

To provide a name to FOREIGN KEY constraint, and to define a FOREIGN KEY constraint on multiple columns, the SQL code is given below:

MySQL / SQL Server / Oracle / MS Access

CREATE TABLE Contact_Info (
  Phone_Number VARCHAR(100),
  EmpID INT NOT NULL,
  Address VARCHAR(255),
  CONSTRAINT FK_Contact_Info FOREIGN KEY (EmpID) 
  REFERENCES Employee(EmpID)
);

SQL FOREIGN KEY constraint with ALTER TABLE

In the above example, a table called Contact_Info is created. To enforce FOREIGN KEY constraint on EmpID column, the SQL code is given below:

MySQL / SQL Server / Oracle / MS Access

ALTER TABLE Contact_Info
ADD FOREIGN KEY (EmpID) 
REFERENCES Employee(EmpID);

To provide a name to FOREIGN KEY constraint, and to define a FOREIGN KEY constraint on multiple columns, the SQL code is given below:

MySQL / SQL Server / Oracle / MS Access

ALTER TABLE Contact_Info
ADD CONSTRAINT FK_Contact_Info 
FOREIGN KEY (EmpID) 
REFERENCES Employee(EmpID);

DROP FOREIGN KEY constraint

To drop FK_Contact_Info FOREIGN KEY constraint from table called Contact_Info, the SQL code is given below:

MySQL

ALTER TABLE Contact_Info
DROP FOREIGN KEY FK_Contact_Info;

SQL Server / Oracle / MS Access

ALTER TABLE Contact_Info
DROP CONSTRAINT FK_Contact_Info;