Cascading Referential Integrity Constraint in SQL Server

Cascading Referential Integrity Constraints in SQL Server

In this article, I am going to discuss the Cascading Referential Integrity Constraints in SQL Server with examples. Please read our previous article before proceeding to this article where we discussed How to make Primary Key and Foreign key relationship between more than two tables in SQL Server with examples.

The Cascading Referential Integrity Constraints in SQL Server are the foreign key constraints which tell SQL Server to perform certain actions whenever a user attempts to delete or update a primary key to which an existing foreign keys point. 

Actions Performed By SQL Server:

In order to tell the SQL Server what actions to perform whenever a user trying to delete or update a primary key value to which existing foreign key points, we are provided with the following options while working with Cascading Referential Integrity Constraints

SET NULL

If a user tries to delete or update statement(s) which will affects rows in the foreign key table, then those values will be set to NULL when the primary key record is deleted or updated in the Primary key table. The important thing that we need to keep in mind that the foreign key columns affected must allow NULL values.

CASCADE

If a user tries to delete the statement(s) which will affect the rows in the foreign key table, then those rows will be deleted when the primary key record is deleted. Similarly, if an update statement affects rows in the foreign key table, then those rows will be updated with the value from the primary key record after it has been updated.

SET DEFAULT

If a delete or update statement affects rows in a foreign key table, then all rows containing those foreign keys are set to the default value. All foreign key columns in the related table must have default constraints defined on them.

NO ACTION

This is the default action that SQL Server performs. This specifies that if an update or deletes statement affects rows in foreign key tables, then the action will be denied and rolled back. An error message will be raised.

Cascading Referential Integrity Constraints in SQL Server with one example

Let us understand the Cascading Referential Integrity Constraints in SQL Server with one example. In order to understand this, we need two database tables. So lets first create two tables (Person and Gender) and enforce primary and foreign key constraints as shown below.

Create Gender Table

CREATE TABLE Gender 
( 
    Id     INT PRIMARY KEY, 
    Gender NVARCHAR(50) 
)

-- Insert some test data in Gender Table
Insert into Gender values (1, 'Male')
Insert into Gender values (2, 'Female')
Insert into Gender values (3, 'Unknown')

Select * from Gender

The above SQL statement will give the following result

Cascading Referential Integrity Constraints in SQL Server

Create Person Table

CREATE TABLE [Person](
  [Id] [int] PRIMARY KEY,
  [Name] [varchar](100) NOT NULL,
  [Email] [varchar](100) NOT NULL,
  [GenderID] [int] NULL
)

In Person table, the GenderID column is the foreign key which is referencing to the ID column of the Gender table. The Foreign key constraints can be added graphically using SSMS or using a query as shown below.

-- Add a foreign key reference using query
Alter table Person 
add constraint Person_GenderId_FK FOREIGN KEY (GenderId) references Gender(ID)

--Insert some test data in Person Table
Insert into Person values (1, 'abc','abc@gmail.com',1)
Insert into Person values (2, 'pqr','pqr@gmail.com',2)
Insert into Person values (3, 'xyz','xyz@gmail.com',3)

Select * from Person

The above SQL Select query will give us the following result

Cascading Referential Integrity Constraints in SQL Server

For example, consider the above 2 tables (Person and Gender). If we delete the row with ID = 1 from Gender table then row with ID = 1 from Person table becomes an orphan record. We will not be able to tell the Gender for this row. So, the Cascading referential integrity constraint in SQL Server can be used to define the actions that Microsoft SQL Server should take. By default, we get an error and the DELETE or UPDATE statement is rolled back. 

If we want to delete or update a record in the parent table (in this case Gender table) when they have corresponding child record in the child table (in this case Person table) we are provided with a set of rules to perform delete and update operations known as DELETE rules and UPDATE rules as shown in the below image.

Cascading Referential Integrity Constraints in SQL Server

NOTE: The Delete rules and update rules were not imposed on the master table, they are imposed on the child table that too on the foreign key column.

Syntax:
<COLUMN><DATATYPE><CONSTRAINT NAME> REFERENCES <MASTER TABLE NAME> (<PRIMARY KEY COLUMN>)
ON DELETE {NO ACTION / CASCADE / SET NULL / DEFAULT}
ON UPDATE {NO ACTION / CASCADE / SET NULL / DEFAULT}
Example:

Let’s delete the existing Person table and again create the person table as shown below

CREATE TABLE [Person](
  [Id] [int] PRIMARY KEY,
  [Name] [varchar](100) NOT NULL,
  [Email] [varchar](100) NOT NULL,
  [GenderID] [int] CONSTRAINT FK_Person_GenderID FOREIGN KEY REFERENCES dbo.Gender(Id) 
  ON DELETE CASCADE 
  ON UPDATE CASCADE
)

Now insert the following data into Person table

--Insert some test data in Person Table
Insert into Person values (1, 'abc','abc@gmail.com',1)
Insert into Person values (2, 'pqr','pqr@gmail.com',2)
Insert into Person values (3, 'xyz','xyz@gmail.com',3)

After making the relationship with cascade rules now we can perform the update and delete operations on parent table reference column data and affected the corresponding child table reference column data also.

Examples:

Insert into Person values (4, ‘pranaya’,’pranaya@g.com’,4) — Not Allowed

When we execute the above query it will give us the below error

Cascading Referential Integrity Constraints in SQL Server

DELETE FROM Gender WHERE Id = 2  — ALLOWED

Once we delete the record from Gender table with Id = 2, it also automatically delete the record(s) from the Person table where GenderId = 2. Fetch the records from the Person table and observe.

Select * from Person

Cascading Referential Integrity Constraints - Person Table Updated Data

UPDATE Gender SET Id = 4 where Gender = ‘Male’  — Allowed

Once we update the record from Gender table with Id = 2 where Gender = ‘Male’, it also automatically update the record(s) in the Person table. Fetch the records from the Person table and observe.

Cascading Referential Integrity Constraints - Person Table Updated Data

What is SELF REFERENTIAL INTEGRITY?

This is the same as the referential integrity we have learned earlier. In earlier cases, we are binding one column of a table with another column of another table whereas in self-referential integrity we bind a column of a table with another column of the same table i.e. both the foreign key and primary key will be present in one table only.

Let us see an example for understanding this concept.

We have the following employee table having the following records.

EMPLOYEE TABLE
Primary key column EmpID Ename Job Foreign key column ManagerID
1000

1001

1002

1003

1004

1005

1006

AAA

BBB

CCC

DDD

EEE

FFF

GGG

PRESIDENT

MANAGER

SALESMAN

CLERK

MANAGER

ANALYST

CLERK

NULL

1000

1001

1001

1000

1004

1010 (INVALID)

In the above table, we are binding the column ManagerID (foreign) with another column of the same table i.e. EmpID (Reference key) to verify the value entered into ManagerID column to be existing under EmpNo column

Creating Employee table using all constraints in column level
CREATE TABLE Employee 
( 
    EmpID  INT CONSTRAINT EmpID_PK PRIMARY KEY, 
    Ename  VARCHAR(50) NOT NULL, 
    Job    VARCHAR(50), 
    ManagerID    INT CONSTRAINT ManagerID_FK REFERENCES Employee(EmpID), 
    Salary MONEY DEFAULT 3000 CONSTRAINT Salary_CK CHECK (Salary BETWEEN 5000 AND 15000) 
) 

In the next article, I am going to discuss the Identity column in SQL Server with examples.

SUMMARY

In this article, I try to explain Cascading Referential Integrity Constraints in SQL Server step by step with some simple example. I hope this article will help you with your need. I would like to have your feedback. Please post your feedback, question, or comments about this article. 

Leave a Reply

Your email address will not be published. Required fields are marked *