The UPDATE statement conflicted with the REFERENCE constraint - SQL Server / TSQL Tutorial Part 76

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

1 comment: