Lỗi the delete statement conflicted with the reference constraint

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)

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

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

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

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.

You don't need to use the on delete cascade. Somebody (the schema design author) had made sure you cannot delete a person that is still referenced by an article. It succeeded, you were just trying to do this and was blocked, kudos to the designer.

Now go and talk with that somebody that designed the schema and knows the constraints and ask him how to properly delete the records you're trying to delete, in the correct order and taking the proper precautions to keep the database consistent.

answered Apr 4, 2013 at 12:02

Remus RusanuRemus Rusanu

51.7k3 gold badges94 silver badges172 bronze badges

You have two real choices here, you can disable constraints on the table. This usually not a great idea as you can end up with a bad data condition if you're messing with data that relates to other tables, but not know the full extent of your schema and it may suit your purposes:

ALTER TABLE [workdemo.no].[dbo].[M06Persons] NOCHECK CONSTRAINT [FK_M02ArticlePersons_M06Persons]

Remember to turn the constraint back on after the delete with

ALTER TABLE [workdemo.no].[dbo].[M06Persons] WITH CHECK CHECK CONSTRAINT [FK_M02ArticlePersons_M06Persons]

The second choice would be to drop and re-add the constraint with the ON DELETE CASCADE option using:

ALTER TABLE [workdemo.no].[dbo].[M06Persons] DROP CONSTRAINT [FK_M02ArticlePersons_M06Persons]
ALTER TABLE [workdemo.no].[dbo].[M06Persons] WITH NOCHECK ADD CONSTRAINT [FK_M02ArticlePersons_M06Persons] FOREIGN KEY(M06PersonId)
REFERENCES <parent table here> (<parent column here>)
ON DELETE CASCADE

Based on your FK name it looks like your parent table is M02ArticlePersons and the parent column is M06Persons.

If you did not author this schema please try to consider why the constraints may be present, and understand that violating them in this manner may have unintended side effects.

answered Apr 4, 2013 at 12:10

0

dbo.M02ArticlePersons table of column M06PersonId is reffered in another table. So before delete statement, disable this relationships and try again

below is for disbling the foreign key

 ALTER TABLE dbo.M02ArticlePersons NOCHECK CONSTRAINT FK_M02ArticlePersons_M06Persons
DELETE FROM [workdemo.no].[dbo].[M06Persons] 
  WHERE ID > '13'
GO

and this is to enable it

ALTER TABLE dbo.M02ArticlePersons CHECK CONSTRAINT FK_M02ArticlePersons_M06Persons

Hope this will work

answered Apr 4, 2013 at 12:04

1

There is another manual option too:

You can go to the child table and delete the child rows referenced by the parent key. Then you can delete the parent row. This is essentially what the cascade delete does. This way, you do not have to drop/recreate/alter your constraints.

answered Apr 4, 2013 at 14:49

StanleyJohnsStanleyJohns

5,9622 gold badges21 silver badges44 bronze badges

This little code will help for any table that you want to delete records from. It takes care of referential integrity as well ...