Scenario:
You are working as SQL Server developer, You wrote an update statement for one of the table and getting below error.Msg 547, Level 16, State 0, Line 32
The UPDATE statement conflicted with the REFERENCE constraint "FK_".
The conflict occurred in database "YourDatabaseName", table "SchemaName.YourTableName", column 'ColumnName'.
The statement has been terminated.
How to resolve this issue?
Solution:
Let's create this error first by using below script. We are going to create two tables dbo.Customer and dbo.Orders. The tables has Primary-Foreign Key Relationship.
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) ) --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)
How to update record when Column is referenced by Foreign Key Constraint in SQL Server |
Now let's say if you feel that CustomerId value is incorrect in dbo.Customer and need to be updated. You wrote below update statement to update CustomerId to 100.
update dbo.Customer set Customerid=100
You will get below error.
Msg 547, Level 16, State 0, Line 33
The UPDATE statement conflicted with the REFERENCE constraint "FK__Orders__Customer__1ED998B2".
The conflict occurred in database "YourDatabaseName", table "dbo.Orders", column 'Customer_id'.
The statement has been terminated.
As there is no Customer_id value=100 in dbo.Orders table, You can't update the record in reference table. Now you thought that let's fix the Parent table first ( dbo.Orders) and then I can update the dbo.Customer table.
update dbo.Orders set Customer_Id=100
Again you got the error as shown below, because we don't have CustomerId=100 available in dbo.Customer table.
Msg 547, Level 16, State 0, Line 36
The UPDATE statement conflicted with the FOREIGN KEY constraint "FK__Orders__Customer__1ED998B2".
The conflict occurred in database "YourDatabaseName", table "dbo.Customer", column 'Customerid'.
The statement has been terminated.
From here we can come with with multiple solutions
1) Instead of updating the record, Insert the record in Reference Table ( Dbo.Customer), Then update the record in Parent table (Dbo.Orders) and finally delete the existing records from Reference Table.
--Insert Record in Reference Table First insert into dbo.Customer (CustomerId,FName, LName,SSN) values (100,'Aamir','Shahzad','000-000-00') --Update the Records in Parent Table update dbo.Orders set Customer_Id=100 --Delete the old record from Reference Table Delete from dbo.Customer where CustomerId=1
Check the records in table now.
How to update Column Value when referenced by Foreign Key Constraint in SQL Server |
2) Disable the Foreign Key Constraint and Update the Values Manually
Another solution can be, disable the Foreign Key constraint, update the records and finally enable the Foreign key again.
--Find the Foreign Key Constraint with Table Name USE YourDatabaseName GO Select Schema_name(Schema_id) as SchemaName, object_name(Parent_object_id) as TableName, name as ForeignKeyConstraintName from sys.foreign_keys
Disable the Foreign Key Constraint by using below statement
Syntax
ALTER TABLE SchemaName.ParentTableName
NOCHECK CONSTRAINT Constraint_Name
I used below statement to disable Foreign Key constraint on dbo.Orders table.
--Disable Foregin Key by using NOCHECK ALTER TABLE dbo.Orders NOCHECK CONSTRAINT FK__Orders__Customer__2A4B4B5E --Run Update Statements update dbo.Customer set Customerid=100 update dbo.Orders set Customer_Id=100
Enable Foreign Key Constraint Syntax
ALTER TABLE SchemaName.ParentTableName
CHECK CONSTRAINT Constraint_Name
I execute below script to Enable Foreign Key Constraint on dbo.Orders table.
--Enable Foreign Key Constraint by using CHECK ALTER TABLE dbo.Orders CHECK CONSTRAINT FK__Orders__Customer__2A4B4B5E
Video Demo : The UPDATE statement conflicted with the REFERENCE Constraint
It worked, thanks!
ReplyDelete