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
How to drop a SQL column and find foreign keys with sp_fkeys