How to create Foreign Key Constraint With ON UPDATE CASCADE in SQL Server - SQL Server / TSQL Tutorial Part 79

Scenario:

In previous posts, we learn that if we have Foreign key Constraint with default setting and we try to update the value in column in Reference Table which is used as Reference Column in Foreign Key Constraint, we get error. We discussed multiple ways to handle the situation, please check this link.

Foreign Key Constraint does provide the option to set the Cascading action, we can create Foreign Key Constraint with Cascading Update. 

If Update Cascading settings is used, when we update the value in Referenced Table , it will also update the value in parent table (Foreign Key Table) column.

Let's test this scenario. Create two tables dbo.Customer and dbo.Orders with Foreign Key Relationship by given 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 UPDATE CASCADE
    )


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



Let's check the data in tables by using select query
How to create Foreign Key Constraint with Update Cascade in SQL Server


Let's run our update statement on CustomerId in dbo.Customer table and see if it also update the column value in dbo.Orders for Customer_id.

    update dbo.Customer
    set Customerid=100


Let's check the data again in our tables 
How to enable Update Cascading with Foreign Key Constraint in SQL Server 

As we can see that the value is also updated in dbo.Orders.Customer_id column.


Video Demo:How to create Foreign Key Constraint With ON UPDATE CASCADE in SQL Server

No comments:

Post a Comment