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 Department table.

• 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. E.g. Employee is a child table and Department is 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:

• Using T-SQL
• Using SQL Server Management Studio

Create a Foreign Key using T-SQL

A foreign key can be configured in the create table T-SQL script. Append 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 Department table.

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 CASCADEON 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 DepartmentID column.

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 HR database. 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 Department table. 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 Employee table.

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 Employee and Department table by setting a foreign key on the DepartmentID column in the Employee table, as shown below.