How to create Foreign Key Constraint with ON DELETE CASCADE in SQL Server - SQL Server / TSQL Tutorial Part 80

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 be deleted from Parent Table ( Foreign Key Constraint Table) as well.

Solution:

SQL Server let us use the the setting with Foreign Key Constraint called On DELETE CASCADE. If this rule is implemented, whenever record is deleted from Referenced Table( Primary Key Column Table), it will also be deleted from Parent Table ( Foreign Key Constraint Table).

Let's test this scenario with 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 CASCADE
    )

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



Check the data in tables by using select query.

How to enable ON Delete CASCADE rule with Foreign Key Constraint in SQL Server

Let's delete the row from Referenced Table( Primary Key Column Table) and see if it also deletes from Parent Table ( Foreign Key Constraint Table) 


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


Check the tables again to see if record is deleted from both tables due to ON Delete Cascade rule on Foreign Key Constraint.
How to use ON Delete Cascade to delete records from multiple Tables in SQL Server Table

As we can see that the records are deleted from both tables due to ON DELETE CASCADE rule of Foreign Key Constraint. 


Video Demo:How to create Foreign Key Constraint with ON DELETE CASCADE in SQL Server

No comments:

Post a Comment