How to Create Foreign Key Constraint with ON DELETE SET NULL Option in SQL Server - SQL Server / TSQL Tutorial Part 81

Scenario:

You are working as SQL Server developer, you need to create two tables with Primary -Foreign Key Relationship. You want to create Foreign Key Constraint with setting if record will be deleted from Referenced Table (Primary Key Column Table), it should not be deleted from Parent Table ( Foreign Key Constraint Table) instead the value should be updated to Null.

Solution:

We can use ON DELETE SET NULL with Foreign Key Constraint definition to implement above requirement. 

Let's perform by using below script.

USE YourDatabaseName
GO

CREATE TABLE dbo.Customer (
    Customerid INT PRIMARY KEY
    ,FName VARCHAR(100)
    ,LName VARCHAR(100)
    ,SSN VARCHAR(10)
    )


    CREATE TABLE dbo.Orders (
    OrderId INT Identity(1, 1)
    ,OrderitemName VARCHAR(50)
    ,OrderItemAmt INT
    ,Customer_id INT FOREIGN KEY REFERENCES
     Customer(CustomerId) ON DELETE SET NULL
    )

    --insert sample data
     insert into dbo.Customer 
    (CustomerId,FName, LName,SSN)
     values
    (1,'Aamir','Shahzad','000-000-00')

    insert into dbo.Orders
    (OrderItemName,OrderItemAmt,Customer_Id)
    values ('TV',1,1)

Check the data in tables by using Select query 

How to create Foreign Key Constraint with ON DELETE SET NULL in SQL Server 

Let's delete the row from Referenced Table( Primary Key Column Table) and check if records still exists in Parent Table ( Foreign Key Constraint Table) and column value is updated to Null.

    --Delete the Record from Referenced Table(PK Column Table)
    Delete from dbo.Customer
    where CustomerId=1

How to use ON DELETE SET NULL option with Foreign Key Constraint in SQL Server

As we can see that the record is deleted from Referenced Table( Primary Key Column Table) but still present in Parent Table ( Foreign Key Constraint Table)  but value is updated to Null as expected.

Video Demo : How to Create Foreign Key Constraint with ON DELETE SET NULL Option in SQL Server