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