Error Message:The UPDATE statement conflicted with the REFERENCE constraint

30 June,2020 by Jack Vamvas

During an  UPDATE statement on a particular row on a SQL Server table , this  error message appeared:

Error Message:The UPDATE statement conflicted with the REFERENCE constraint "FK_My_Constraint". The conflict occured in database "MyDB",table "dbo.MyTable". The statement has been terminated

The initial input  value was incorrect , so I needed to update the value - but there was a foreign key constraint in place to retain integrity between the tables. The way to fix this issue is to temporarily disable the FOREIGN KEY constraint - change the values and the set the FOREIGN KEY constraint again. 

SQL Server has a built-in facility via the ALTER TABLE command to disable - enable FOREIGN KEY constraints

This sequence of sql statements will disable and then re enable the FOREIGN KEY constraint - using the NOCHECK and CHECK CHECK switches  with ALTER TABLE commands

-- Disable single FOREIGN KEY constraint

ALTER TABLE MyTable NOCHECK CONSTRAINT MyConstraint

-- Enable single FOREIGN KEY constraint

ALTER TABLE MyTable WITH CHECK CHECK CONSTRAINT MyConstraint

 

These type of errors can also occur in a DELETE statement - if there is a FOREIGN KEY constraint - for more information conflicts during a DELETE statement - read The DELETE statement conflicted with the REFERENCE constraint

Some helpful links to managing FOREIGN KEYS

How to list constraints of a table      

List Foreign Key Constraints -MS SQL     <===Old School method

How to drop a SQL column and find foreign keys with sp_fkeys

 


Author: Jack Vamvas (http://www.sqlserver-dba.com)


Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on Error Message:The UPDATE statement conflicted with the REFERENCE constraint


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer