SQL Server - FOREIGN KEY

The SQL Server (Transact-SQL) FOREIGN KEY constraint 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 Server FOREIGN KEY constraint with CREATE TABLE

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

CREATE TABLE Contact_Info (
  Phone_Number VARCHAR(100),
  EmpID INT NOT NULL,
  PersonName VARCHAR(255),
  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 statement is given below:

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

The statement given below demonstrates how to apply FOREIGN KEY constraint on multiple columns (EmpID and PersonName). Please note that, the Employee table must have PRIMARY KEY constraint on these columns - EmpID and Name.

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

SQL Server 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 statement is given below:

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 statement is given below:

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

The statement given below demonstrates how to enforce a FOREIGN KEY constraint on multiple columns (EmpID and PersonName). Please note that, the Employee table must have PRIMARY KEY constraint on these columns - EmpID and Name.

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

DROP FOREIGN KEY constraint

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

ALTER TABLE Contact_Info
DROP CONSTRAINT FK_Contact_Info;