A foreign key (FK) is a column or combination of columns that is used to establish and enforce a link between the data in two tables. You can create a foreign key by defining a FOREIGN KEY constraint when you create or modify a table.
In a foreign key reference, a link is created between two tables when the column or columns that hold the primary key value for one table are referenced by the column or columns in another table. This column becomes a foreign key in the second table.
This is our parent table. In this DeptNo is Primary Key. There are three rows present in that table.
This is our child table. In this EmpNo is Primary Key, and DeptNo is Foreign Key. Here we can insert data for EmpNo 1 and 2. When we try to insert data for EmpNo 3 we get error. DeptNo = 105 can not be inserted into Employee(child) table because DeptNo = 105 not present in the Department(parent) table. These operations are done by Foreign Key.
-- Create 'Department' table
Create table Department
DeptNo int primary key identity(101,1),
-- Insert data into 'Department' table.
insert into Department values('Finance')
insert into Department values('Production')
insert into Department values('Marketing')
-- Select data from 'Department' table.
select * from Department
-- Create 'Employee' table
Create table Employee
EmpNo int primary key identity(1,1),
DeptNo int constraint FK_DeptNo references Department(DeptNo)
-- Insert data into 'Employee' table.
insert into Employee values('AAA', 1000, 101)
insert into Employee values('BBB', 2000, 102)
-- Select data from 'Employee' table.
select * from Employee
insertinto Employee values('CCC', 3000, 105)
DeptNo = 105 can not be inserted into Employee(child) table because DeptNo = 105 not present in the Department(parent) table. We will get following error:
The INSERT statement conflicted with the FOREIGN KEY constraint "FK_DeptNo". The conflict occurred in database "Test", table "dbo.Department", column 'DeptNo'.
We can enter the values in to the child table which are present in parent table only.
The data type of both the columns is same.
The column of parent table should be a PRIMARY KEY or UNIQUE KEY.
We can't delete/update a record from parent table which has reference in the child table.