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

1 comment:

  1. AHSEC 12th Class Syllabus 2022 for Science, Arts, Commerce Subjects Wise Download our Website form of Pdf Format, With Exams just Assam 12th Class Syllabus Around the Corner, All the HS Students would be Surrounded by a lot of Study Material, hectic Preparation Schedule and Summing up the Assignment works. at this Time, Students Should not forget to go Through the Syllabus so that they don’t miss any Important topic to Prepare for the finals AHSEC HS Syllabus 2022 Download and Students Regular Reading Subject Wise Syllabus for Higher Secondary Final Examination Best Performance in 2022, So Students our Website Visit Regular More Update Assam HS new Syllabus 2022 Exam Pattern Pdf Format File Available here our Web Portal Providing the Assam Board HS Syllabus for All Important Subjects of Modern Indian Languages include: Assamese, Bengali, Bodo, Garo, Hmar, Hindi, Khasi, Manipuri, Mizo, Nepali & Urdu.*Advance Languages include: Advance Assamese, Advance Bengali, Advance Bodo, Advance Hindi & Advance Manipuri. Music (Group A) include: Hindustani Vocal, Kathak Dance, Tabla, Violin & Sitar Music (Group B) include: Sattriya Vocal Music (Borgeet), Sattriya Dance & Khol Music (Group C) include: (i) Assamese Folk Music/ (ii) Assamese Folk Dance for the Upcoming Annual Examination 2022. Assam HS Syllabus 2022 for All important Subjects Available here.

    ReplyDelete