Referential Integrity in SQL Server

In this section, we will cover the list of Referential Integrity in SQL Server and their advantages. The following are the available list of Referential Integrity constraint options in SQL Server

• No Action: This is the default behavior. If you created a Foreign key without mentioning the Referential Integrity, then SQL will automatically assign No Action to it. If you set the Referential Integrity as No Action, and if you perform an update or delete in the parent table, then it will throw an error message.
• Cascade: If you set the SQL Referential Integrity as Cascade. And if you perform an update or delete in the parent table, then those changes will automatically be applied to the dependent table rows.
• Set Default: If you set the SQL Server Referential Integrity as Default. And perform an update or delete in the parent table, the foreign key column values will be replaced by the default value.
• Set Null: If you set the SQL Referential Integrity as Set Null, and perform an update or delete in the parent table, the dependent records will become Nulls.

Before we get into the example, let me show you the table definition behind the tblDepartment table

USE [SQLTEST]
GO

CREATE TABLE [dbo].[tblDepartment](
	[ID] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
	[DepartmentName] [varchar](50) NULL,
)

And the table definition behind the tblEmployee table is as shown below. I suggest you refer to Create Table article

USE [SQLTEST]
GO

CREATE TABLE [dbo].[tblEmployee](
	[EmpID] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
	[FirstName] [nvarchar](255) NULL,
	[LastName] [nvarchar](255) NULL,
	[Education] [nvarchar](255) NULL,
	[YearlyIncome] [float] NULL,
	[Sales] [float] NULL,
	[DeptID] [int] NULL CONSTRAINT [DF_tblEmployee_DeptID] DEFAULT (2),
	CONSTRAINT [FK_tblDepartment_tblEmployee_DeptID] FOREIGN KEY([DeptID])
		REFERENCES [dbo].[tblDepartment] ([ID])
) 
GO

Let me show you the data inside tblDepartment table

And the data inside the tblEmployee table is:

To demonstrate Sql Server referential integrity, we will recreate the same Foreign key again and again. To achieve the same, we use the DROP Constraint statement along with the ALTER TABLE Statement to delete the existing Foreign key constraint

USE [SQLTEST]
GO
ALTER TABLE [dbo].[tblEmployee]  
DROP CONSTRAINT [FK_tblDepartment_tblEmployee_DeptID]

NO ACTION Referential Integrity in SQL Server Example

As we deleted the existing constraint from tblEMployee. Let me add the Foreign key constraint along with NO ACTION referential integrity in Sql Server.

Remember, this is the default option, even if you haven’t mentioned the integrity. By default, SQL Server will assign no action option.

We are using the Alter Table Statement to alter the table content. Then we used the ADD Constraint statement to add the Foreign key constraint.

-- No Action Referential Integrity in SQL Server
USE [SQLTEST]
GO
ALTER TABLE [dbo].[tblEmployee]  
ADD CONSTRAINT [FK_tblDepartment_tblEmployee_DeptID] FOREIGN KEY([DeptID])
        REFERENCES [dbo].[tblDepartment] ([ID])
        ON UPDATE NO ACTION ON DELETE NO ACTION

Let me show you the same in the designer window.

Now let me perform Delete operation on tblDepartment. It means deleting the main table data (primary key value). From the below code snippet you can see, we are deleting a record from tblDepartment whose ID = 1.

-- No Action Referential Integrity in SQL Server
USE [SQLTEST]
GO
DELETE FROM [tblDepartment]
WHERE ID = 1

As you can see from the below screenshot, it is throwing an error. It is because No ACTION referential integrity will not allow you to perform Delete or Update option if there is any Foreign key dependency.

SET DEFAULT Referential Integrity in SQL Server Example

In this example, we will add a Foreign key constraint with Set Default referential Integrity in Sql Server.

Let me set the SQL Server referential integrity to default. It means whenever we perform Delete or Update on the Master Table, then the default value loaded in the Dept Id column (tblEmployee). I suggest you refer to the Default Constraint article.

-- Set Default Referential Integrity in SQL Server
USE [SQLTEST]
GO
ALTER TABLE [dbo].[tblEmployee]  
ADD CONSTRAINT [FK_tblDepartment_tblEmployee_DeptID] FOREIGN KEY([DeptID])
        REFERENCES [dbo].[tblDepartment] ([ID])
        ON UPDATE SET DEFAULT ON DELETE SET DEFAULT

Output

Command(s) completed successfully.

Let me show you the same in designer window.

Now let me perform Delete operation on tblDepartment. Let’s delete a record from tblDepartment whose ID = 6.

-- Set Default Referential Integrity in SQL Server
USE [SQLTEST]
GO
DELETE FROM [tblDepartment]
WHERE ID = 6

Output

(1 row(s) affected)

Let us see the data in tblEmployee and tblDepartment. As you can see from the below screenshot, ID number 6 deleted from tblDepartment, and all the dependent records in tblEmployee replaced with the default value (i.e., 2)

SET NULL Referential Integrity in SQL Server Example

In this example, we will add a Foreign key constraint with Set Null referential Integrity in Sql Server.

It means, whenever we perform Delete, or Update on Master table (tblDepartment) then a NULL value will be loaded / Updated in the Dept Id column (tblEmployee).

-- Set Null Referential Integrity in SQL Server
USE [SQLTEST]
GO
ALTER TABLE [dbo].[tblEmployee]  
ADD CONSTRAINT [FK_tblDepartment_tblEmployee_DeptID] FOREIGN KEY([DeptID])
        REFERENCES [dbo].[tblDepartment] ([ID])
        ON UPDATE SET NULL ON DELETE SET NULL

Output

Command(s) completed successfully.

Let me show you the same in the designer window.

Now let me perform Delete operation on tblDepartment. It means deleting the primary key value. From the below code snippet, you can see, we are deleting record from tblDepartment whose ID = 1.

-- Set Null Referential Integrity in SQL Server
USE [SQLTEST]
GO
DELETE FROM [tblDepartment]
WHERE ID = 1

Output

(1 row(s) affected)

Let us see the data in tblEmployee and tblDepartment. As you can see from the following screenshot, ID number 6 removed from tblDepartment, and all the dependent records in tblEmployee replaced with NULLS (i.e., Emp ID 1 and 13)

Cascade Referential Integrity in SQL Server Example

In this example, we will add a Foreign key constraint with Cascade referential Integrity in SQL Server. It means, whenever we perform Delete on Master table (tblDepartment), corresponding or dependent records from tblEmployee will delete.

-- Cascade Referential Integrity in SQL Server
USE [SQLTEST]
GO
ALTER TABLE [dbo].[tblEmployee]  
ADD CONSTRAINT [FK_tblDepartment_tblEmployee_DeptID] FOREIGN KEY([DeptID])
        REFERENCES [dbo].[tblDepartment] ([ID])
        ON UPDATE CASCADE ON DELETE CASCADE

Output

Command(s) completed successfully.

Let me show you the same in the designer window.

Now let me perform Delete operation on tblDepartment. From the below code snippet you can see, we are deleting a record from tblDepartment whose ID = 2.

-- Cascade Referential Integrity in SQL Server
USE [SQLTEST]
GO
DELETE FROM [tblDepartment]
WHERE ID = 2

Output

(1 row(s) affected)

Let us see the data in tblEmployee, and tblDepartment. As you can see, ID number 6 removed from tblDepartment, and all the dependent records in tblEmployee also removed

Use SSMS to set referential Integrity in SQL

If you are comfortable to use SQL Server Management Studio, then following the below steps to set referential integrity.

Within the object explorer, Expand the Database folder in which the table exists. Please select the table on which your Foreign key holds, then go to the Keys folder. Next, Right-click on the key name will open the context menu. Please select the Modify option

Once you click the Modify option, the SQL management studio will open the corresponding table in design mode, along with the Foreign Key Relationships window. Here set the Delete Rule and Update Rule property for SQL Server referential integrity.