SQL-Foreign Constraint

In this tutorial, you will learn how to use the SQL Server foreign key constraint is used to prevent actions that would destroy links between tables.

FOREIGN KEY Constraint

The FOREIGN KEY constraint is used to prevent actions that would destroy links between tables or A foreign key means that values in one table must also appear in another table. A FOREIGN KEY is a field (or collection of fields) in one table, that refers to the PRIMARY KEY in another table.

We will create two new tables to understand the FOREIGN KEY constraint functionality. The first table will act as the parent table with the ID column defined as a PRIMARY KEY column. The second table will act as the child table, with the ID column defined as the FOREIGN KEY column that references the ID column on the parent table.

Look at the following two tables:

CREATE TABLE ConstraintParentTable
(
ParentID INT PRIMARY KEY,
Name VARCHAR(100) NULL
)
GO
CREATE TABLE ConstraintChildTable
(
ChildID INT PRIMARY KEY,
ID INT FOREIGN KEY REFERENCES ConstraintParentTable(ParentID)
)

Now press F5 to execute table script on query window.

Output

Now insert data in parent table - ConstraintParentTable.

INSERT INTO ConstraintParentTable ([ParentID],[NAME]) VALUES (1,'Rohatash'),(2,'Ravindar'),(3,'Ritesh')

Output

Now insert data in child table - ConstraintChildTable.

INSERT INTO ConstraintChildTable (ChildID,ID) VALUES (1,1)
GO
INSERT INTO ConstraintChildTable (ChildID,ID) VALUES (2,4)
GO

The operation will fail, as the ID value of 4 does not exist in the parent table and breaks the referential integrity between the child and parent tables, as seen in the following error message:

Output

Checking the parent and child tables content, you will see that only one record is inserted into the child table, as you can see below:

The ON UPDATE and ON DELETE specify which action will execute when a row in the parent table is updated and deleted. The following are permitted actions : NO ACTION, CASCADE, SET NULL, and SET DEFAULT

Delete and Update Action of Rows in the Parent Table

The following below points describe delete and update action of rows in the parent table.

Delete actions of rows in the parent table

If you delete one or more rows in the parent table, you can set one of the following actions:

• ON DELETE NO ACTION - SQL Server raises an error and rolls back the delete action on the row in the parent table.
• ON DELETE CASCADE - SQL Server deletes the rows in the child table that is corresponding to the row deleted from the parent table.
• ON DELETE SET NULL - SQL Server sets the rows in the child table to NULL if the corresponding rows in the parent table are deleted. To execute this action, the foreign key columns must be nullable.
• ON DELETE SET DEFAULT - SQL Server sets the rows in the child table to their default values if the corresponding rows in the parent table are deleted. To execute this action, the foreign key columns must have default definitions. Note that a nullable column has a default value of NULL if no default value specified.

By default, SQL Server applies ON DELETE NO ACTION if you don’t explicitly specify any action.

Update action of rows in the parent table

If you update one or more rows in the parent table, you can set one of the following actions:

• ON UPDATE NO ACTION - SQL Server raises an error and rolls back the update action on the row in the parent table.
• ON UPDATE CASCADE - SQL Server updates the corresponding rows in the child table when the rows in the parent table are updated.
• ON UPDATE SET NULL - SQL Server sets the rows in the child table to NULL when the corresponding row in the parent table is updated. Note that the foreign key columns must be nullable for this action to execute.
• ON UPDATE SET DEFAULT - SQL Server sets the default values for the rows in the child table that have the corresponding rows in the parent table updated.