Referential Integrity in SQL Server

In this section, we will cover the list of Referential Integrity in SQL Server and their advantages. The following is 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, it would automatically assign No Action. If you set the Referential Integrity as No Action and perform an update or delete in the parent table, it will throw an error message.
  • Cascade: If you set the 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. When performing 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 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

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

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

Department Table 1

And the data inside the tblEmployee table is:

employee Foreign Key Table 2

To demonstrate 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

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 

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
Referential Integrity in SQL Server 3

Let me show you the same in the designer window.

Insert and Update Specific Delete Rule and Update Rule Set to No action 4

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

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 the Delete or Update option if there is any Foreign key dependency.

Referential Integrity in SQL Server 5

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 referential integrity to default. It means whenever we perform Delete or Update on the Master Table, then the default value is loaded in the Dept Id column (tblEmployee). I suggest you refer to the Default Constraint article.

-- Set Default 

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
Command(s) completed successfully.

Let me show you the same in the designer window.

Referential Integrity in SQL Server 7

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

DELETE FROM [tblDepartment]
WHERE ID = 6
(1 row(s) affected)

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

View Employee and Department rows 9

SET NULL Referential Integrity in SQL Server Example

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

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

-- SET NULL

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
Command(s) completed successfully.

Let me show you the same in the designer window.

Referential Integrity in SQL Server 11

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

DELETE FROM [tblDepartment]
WHERE ID = 1
(1 row(s) affected)

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

View Data in Tables 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 that whenever we perform Delete on Master table (tblDepartment), corresponding or dependent records from tblEmployee will delete.

-- Cascade

ALTER TABLE [dbo].[tblEmployee]  
ADD CONSTRAINT [FK_tblDepartment_tblEmployee_DeptID] FOREIGN KEY([DeptID])
        REFERENCES [dbo].[tblDepartment] ([ID])
        ON UPDATE CASCADE ON DELETE CASCADE
Command(s) completed successfully.

Let me show you the same in the designer window.

Change Insert and Update Specific to Cascade 15

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

-- Cascade

DELETE FROM [tblDepartment]
WHERE ID = 2
(1 row(s) affected)

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

View Data in Tables 17

Use SSMS to Set referential Integrity

If you are comfortable using Management Studio, then follow 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.

Modify Referential Integrity in SQL Server 18

Once you click the Modify option, the 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.

Change Delete Rule to Cascade 19