Foreign key is a field or combination of fields which matches its values with a Primary key in another table. That means, foreign key is like a link between two tables. This is also called as a Referencing key.
The table containing the foreign key is called as the ‘Referencing table’ and the table containing the primary key is called as the ‘Referenced table’. The data type of the foreign key should be matched with the primary key in the referenced table.
- The main purpose of the foreign key is to ensure the referential integrity of the data. i.e. it allows only the values that are already existing in the referenced table.
- It is used to prevent the records which destroy the link between the two tables.
CONSTRAINT FKName FOREIGN KEY (<Foreign Key Column>)
REFERENCES PrimaryKey Table(<Primary Key Column>)
Foreign key example.
IF OBJECT_ID (‘dbo.Employee’, ‘U’) IS NOT NULL
DROP TABLE [dbo].[Employee];
— Referenced Table.[Primary key table]
CREATE TABLE [dbo].[Employee]
[Emp_ID] int PRIMARY KEY,
IF OBJECT_ID (‘dbo.Dept’, ‘U’) IS NOT NULL
DROP TABLE [dbo].[Dept];
— Referencing Table.[Foreign key table]
CREATE TABLE [dbo].[Dept]
CONSTRAINT fk_Emp_ID FOREIGN KEY ([Emp_ID])
A foreign key can hold NULL values when it refers to a Unique key.