Referential Integrity in SQL Server

In this article we will show you the list of Referential Integrity in SQL Server and their advantages. Following are the list of Referential Integrity options available 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 update or delete in the parent table then it will throw error message.
• Cascade : If you set the Referential Integrity as Cascade, and if you perform update or delete in the parent table then those changes will automatically applied to the dependent table rows.
• Set Default : If you set the Referential Integrity as Cascade, and if you perform update or delete in the parent table then the foreign key column values will be replaced by the default value.
• Set Null : If you set the Referential Integrity as Set Null, and if you perform update or delete in the parent table then 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 to refer Create Table article to understand the steps involved in creating a Table.

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 tblEmployee table is:

In this article 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. Remember, this is the default option, even if you haven’t mentioned the integrity. By default SQL will assign this option. As you can see from the below screenshot we are using the Alter Table Statement to alter the table content, and then used the ADD Constraint statement to add 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

OUTPUT

Let me show you the same in designer window.

Now let me perform a Delete operation on tblDepartment. It means, deleting main table data (primary key value). From the below code snippet you can see, we are deleting 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. This 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 Foreign key constraint with Set Default referential Integrity. It means, whenever we perform Delete, or Update on Master table then the default value will be loaded in the Dept Id column (tblEmployee). I Suggest you to refer 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

Let me show you the same in designer window.

Now let me perform Delete operation on tblDepartment. Lets 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

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

SET NULL Referential Integrity in SQL Server Example

In this example we will add Foreign key constraint with Set Null referential Integrity. It means, whenever we perform Delete, or Update on Master table (tblDepartment) then 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

Let me show you the same in designer window.

Now let me perform Delete operation on tblDepartment. It means, deleting 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

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

Cascade Referential Integrity in SQL Server Example

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

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

Let me show you the same in designer window.

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

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

OUTPUT

Let us see the data in tblEmployee, and tblDepartment. As you can see from the below screenshot, ID number 6 is deleted from tblDepartment, and all the dependent records in tblEmployee are also deleted

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, 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 as per your requirement.

Thank You for Visiting Our Blog