Foreign Key Constraint is a type of constraint in SQL Server to enforce a relation between two tables with a column or a set of columns to ensure a data integrity.

Let us quickly look at how do we create a foreign key in SQL Server

Create Table ParentTable (ParentCol int Primary Key NOT NULL)
Create Table ChildTable (ChildCol int Primary Key NOT NULL, ParentCol int NULL references ParentTable(ParentCol))

The above will create foreign key from ChildTable to ParentTable on column ParentCol. If we carefully look at the definition of ParentCol in ChildTable, we can see its a NULL-able column. If there is a requirement that we should not have NULL value, then we can make the column as NOT NULL.

Cascade Actions:

As we see in the above screenshot, delete_action/update_action are called as cascade actions. This can be used to define the actions on delete/update operations on Primary table. Let us examine with an example as below.

First off, let us try to insert some data in both tables and try to delete from Parent which has reference record in ChildTable.

Insert into ParentTable Select 100
Insert into ParentTable Select 200
Insert into ParentTable Select 300
Insert into ChildTable Select 1, NULL
Insert into ChildTable Select 2, NULL
Insert into ChildTable Select 3,300

Select * From ParentTable
Select * From ChildTable

--Now try to delete a record from ParentTable which has a corresponding record in ChildTable
Delete From ParentTable where ParentCol =300

The DELETE operation fails as there is a record in ChildTable which prevents the DELETE operation on ParentTable.

Now, let us have a CASCADE action on DELETE to define delete on ChildTable as well.

ALTER TABLE ChildTable DROP constraint[FK__ChildTabl__Paren__58D1301D]

ALTER TABLE [dbo].ChildTable  WITH CHECK ADD  CONSTRAINT [FK__ChildTabl__Paren__58D1301D] FOREIGN KEY(ParentCol)
REFERENCES [dbo].ParentTable (ParentCol)
ON DELETE CASCADE
GO

/*Now try to delete a record from ParentTable which has a corresponding record in ChildTable*/
Delete From ParentTable where ParentCol =300


Select * From ParentTable
Select * From ChildTable

Now, we can see the DELETE operation is successful and the Delete action on the table has been changed to Cascade as below.

Points to ponder:

1. You need to have the column in the Parent Table part of Primary Key
2. There would not be any indexes created on the Child table for the referenced key by default. Sometimes, indexing of the foreign key column helps a lots on transactional databases/tables.
3. Foreign key constraints are advised to create when two tables are tightly coupled and if we use these tables in a join condition to check its existence on Parent tables, because the key constraints takes care of it by default.
4. If we have a CASCADE update, then SQL Server will not allow to create Instead of Trigger on the table.

If you enjoyed this blog post, feel free to share it with your friends!