Foreign Keys in SQL Server:
Here you will learn what is a foreign key and how to established a relationship between two tables using a foreign key in the SQL Server database.
What is Foreign Key?
The foreign key establishes the relationship between the two tables and enforces referential integrity in the SQL Server.
For example, the following
Employee table has a foreign key column
DepartmentID that links to a primary key column of the
- A foreign key column can be linked to a primary key or a unique key column of the same or another table.
The table having the foreign key constraint is called the child table, and the table being referenced by the foreign key is called the parent table.
Employeeis a child table and
Departmentis a parent table.
- A value other than NULL is entered in the column of the foreign key constraint, that value must already exist in the referenced column of the parent table. Else you will get a foreign key violation error.
- Foreign key constraints can reference tables within the same database in the same server.
- Foreign key constraints can be defined to reference another column in the same table. This is referred to as a self-reference.
- A foreign key constraint on a single column (Column level constraint) can reference only one column in the parent table and should have the same data type as the referenced column.
- A foreign key constraint defined at the table level (on a combination of columns) should have the same number of reference columns as the number of columns defined in the constraint list. The data type of each column in the constraint must be the same as the corresponding column in the column list.
- There is no limit on the number of foreign key constraints a table can contain that references other tables. However, it is limited by the hardware configuration and the database design.
- Foreign key constraints are not enforced on temporary tables.
Create Foreign Key Constraint in SQL Server
Foreign key constraints can be created in two ways in SQL Server:
Create a Foreign Key using T-SQL
A foreign key can be configured in the create table T-SQL script.
CONSTRAINT REFERENCES statement at the end of all column declaration.
CONSTRAINT <foreignkey_name> FOREIGN KEY (<column_name>) REFERENCES <reference_tablename> (<column_name>) [ON DELETE CASCADE] [ON UPDATE CASCADE]
In the above syntax, <foreignkey_name> is the name of a foreign key that should be in the
FK_TableName_ReferenceTableName format to recognize it easily.
This will give you an idea about the reference table.
<reference_tablename> is the name of the table where the referred column is defined as primary key or unique key.
The following T-SQL script creates a new table
Employee and configures a foreign key constraint
FK_Employee_Department on the
DepartmentID column, which references the
DepartmentID primary key of the
CREATE TABLE Employee( EmployeeID int IDENTITY (1,1) NOT NULL, FirstName nvarchar (50) NOT NULL, LastName nvarchar (50) NOT NULL, DepartmentID int NULL, CONSTRAINT PK_EmployeeID PRIMARY KEY (EmployeeID), CONSTRAINT FK_Employee_Department FOREIGN KEY (DepartmentID) REFERENCES Department (DepartmentID) ON DELETE CASCADE ON UPDATE CASCADE)
ON DELETE CASCADE: When we create a foreign key using the delete cascade option, it deletes the referencing columns in the child table whenever the referenced row in the parent table with the primary key is deleted.
ON UPDATE CASCADE: When a foreign key is created with the update cascade option, the referencing rows in the child table are updated whenever the referenced row in the parent table with the primary key is updated.
Create a Foreign key in an Existing Table
Use the ALTET TABLE ADD CONSTRAINT statement to create a foreign key in an existing table.
ALTER TABLE <table_name> ADD CONSTRAINT <foreignkey_name> FOREIGN KEY (<column_name>) REFERENCES <reference_tablename> (<column_name>) [ON DELETE CASCADE] [ON UPDATE CASCADE]
The following query adds a new foreign key constraint
FK_Employee_Department on the
ALTER TABLE Employee ADD CONSTRAINT FK_Employee_Department FOREIGN KEY (DepartmentID) REFERENCES Department (DepartmentID) ON DELETE CASCADE ON UPDATE CASCADE
Create a Foreign Key using SSMS
Here, we will configure the
DepartmentID column as a foreign key in the
Employee table that points to the
DepartmentID PK column of the
Department table using SQL Server Management Studio.
Open SSMS and expand the
Right-click on the
Employee table and click on the Design option, as shown below.
This will open the
Employee table in the design mode.
Now, right-click anywhere on the table designer and select Relationships..., as shown below.
This will open the Foreign Key Relationships dialog box, as shown below.
Now, click on the Add button to configure a new foreign key, as shown below.
Now, to configure the primary key and foreign key relationship, click on the Tables and Column Specification [...] button. This will open Tables and Columns dialog box where you can select primary key and foreign key relationship.
Here, we are configuring the
DepartmentID column in the
Employee table as a foreign key, which points to the primary key column
DepartmentID of the
So, select primary table and key in the left side and foreign key table and column in the right side, as shown below.
The following defines a foreign key
DepartmentID in the
Click OK to create the relationship and click on Close to close the dialog box.
Now, save your changes. This will create a one-to-many relationship between the
Department table by setting a foreign key on the
DepartmentID column in the
Employee table, as shown below.