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 SQL 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 tables called Employee and Contact_Info with the following records:
Table 1: Employee table
EmpID | Name | City | Age | Salary |
---|---|---|---|---|
1 | John | London | 25 | 3000 |
2 | Marry | New York | 24 | 2750 |
3 | Jo | Paris | 27 | 2800 |
4 | Kim | Amsterdam | 30 | 3100 |
Table 2: Contact_Info table
Phone_Number | EmpID | Address |
---|---|---|
+1-80XXXXX000 | 2 | XXX, Brooklyn, New York, USA |
+33-14XXXXX01 | 3 | XXX, Grenelle, Paris, France |
+31-20XXXXX19 | 4 | XXX, Geuzenveld, Amsterdam, Netherlands |
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 statement
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 statement
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_Employee 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;
❮ SQL Keywords