'Foreign Key Constraint' is used on a column to establish a relationship that exists between that column in a table and another
column in a different table.
Foreign Key Constraint
The 'Foreign Key Constraint' establishes that there is a relationship between two tables. The 'Foreign Key' of one
table points to a 'Primary Key' of another table. It protects the integrity of the foreign key table by allowing insertion of
data into the foreign key column unless it is one of the data that is present in the primary key table.
SQL 'Foreign Key Constraint' Syntax
CREATE Table tablenameB
(
column1 datatype PRIMARY KEY,
column2 datatype,
column3 datatype,
column4 datatyp FOREIGN KEY REFERENCES tablenameA(column1) ON DELETE Cascade,
.
.
.
columnn datatype
)
The above method of creating a table creates an 'Foreign Key' constraint on column4 at the column level. It references the primary key
column - column1 of another table as given above. The 'On Delete Cascade' clause means that if the primary key record on say
tableA is deleted, all the corresponding records in this tableB that has the primary key of tableA will also be deleted.
You can change the action of the 'On Delete' to either 'No Action', 'Set Null', 'Set Default' instead of 'Cascade' based on your requirements.
The Foreign Key Constraint Example
CREATE TABLE Department
(
DeptID smallint PRIMARY KEY,
DeptName nvarchar(50) NOT NULL,
)
Above is the 'Department' table.
CREATE TABLE Employees
(
EmployeeID smallint PRIMARY KEY,
EmployeeName nvarchar(50) NOT NULL,
DateOfBirth smalldatetime NOT NULL,
DesignationID smallint NULL,
DeptID smallint NULL,
PhoneNo nvarchar(12) NULL,
City nvarchar(50) NOT NULL,
Salary decimal(5, 2) NULL
)
Above is the 'Employees' table.
Note: Let us assume that there can be no employees without a department existing
before. So the 'Department' table is the main table or master table and since every employee has to belong to
some department, the 'Employees' table becomes the child table as it cannot exist without department table.
So when we create an employee, he/she has to belong to a department defined in the department table. Therefore we
make 'DeptID' in the 'Employees' table as a 'Foreign Key' to the 'DeptID' in the 'Department' table which acts as a
primary key for the 'Department' table. So if we add an employee into the 'Employee' table, we have to ensure that
he/she is assigned to a 'DeptID' that exists on the 'Department' table. Also if we remove a department from the
'Department' table, all the employees under that 'DeptID' will be affected. In such cases based on the action
chosen against the foreign key, the changes will be effected.