Home > SQL Errors, SQL Tips > SQL Error – UPDATE statement conflicted with the FOREIGN KEY/REFERENCE constraint

SQL Error – UPDATE statement conflicted with the FOREIGN KEY/REFERENCE constraint

December 21, 2010 Leave a comment Go to comments

Just replied on MSDN forum’s following post: http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/289c4ca5-6ab2-4ed6-b446-0300f5c7dc88

When tables are referenced by Foreign Key constraints it gets difficult to UPDATE the key columns.
An example shows this issue and how one can perform UPDATE on FK columns.

USE [tempdb]
GO

-- CREATE test tables
CREATE TABLE dept (d_id INT PRIMARY KEY, dept VARCHAR(50))

CREATE TABLE emp (sn INT PRIMARY KEY, ename VARCHAR(50),
	d_fk INT CONSTRAINT fk_dept_d_fk FOREIGN KEY (d_fk) REFERENCES dept(d_id))

-- INSERT test data
INSERT INTO dept
SELECT 1, 'finance' UNION
SELECT 2, 'hr'

INSERT INTO emp
SELECT 1, 'Manoj', 1 UNION
SELECT 2, 'Saurabh', 2 UNION
SELECT 3, 'Kanchan', 1 UNION
SELECT 4, 'Pooja', 2

-- SELECT the INSERTed data
SELECT * from dept
d_id	dept
1	finance
2	hr	-- DeptID needs to be updated to 3
SELECT * from emp
sn	ename	d_fk
1	Manoj	1
2	Saurabh	2	-- DeptID should also be updated here to 3
3	Kanchan	1
4	Pooja	2	-- DeptID should also be updated here to 3
-- Now lets UPDATE the FK column values
UPDATE dept SET d_id=3 WHERE d_id=2
Error Message:
Msg 547, Level 16, State 0, Line 1
The UPDATE statement conflicted with the REFERENCE constraint "fk_dept_d_fk".
The conflict occurred in database "tempdb", table "dbo.emp", column 'd_fk'.
The statement has been terminated.
UPDATE emp SET d_fk=3 WHERE d_fk=2
Error Message:
Msg 547, Level 16, State 0, Line 1
The UPDATE statement conflicted with the FOREIGN KEY constraint "fk_dept_d_fk".
The conflict occurred in database "tempdb", table "dbo.dept", column 'd_id'.
The statement has been terminated.

We can disable the FK constraints by ALTER statement before the UPDATE and enable them after the UPDATE, like:

-- Disable FK Constraint
ALTER TABLE emp NOCHECK CONSTRAINT fk_dept_d_fk

-- Perform UPDATE
UPDATE dept SET d_id=3 WHERE d_id=2
UPDATE emp SET d_fk=3 WHERE d_fk=2

-- Enable FK Constraint
ALTER TABLE emp WITH CHECK CHECK CONSTRAINT fk_dept_d_fk

-- Following SELECTS will show the new values in updated records:
SELECT * from dept
d_id	dept
1	finance
3	hr	-- Updated record
SELECT * from emp
sn	ename	d_fk
1	Manoj	1
2	Saurabh	3	-- Updated record
3	Kanchan	1
4	Pooja	3	-- Updated record
-- Final Cleanup, DROP the tables.
DROP TABLE emp
DROP TABLE dept

MSDN BOL: http://msdn.microsoft.com/en-us/library/10cetyt6(v=VS.80).aspx

Advertisement
  1. Ben Holley
    April 5, 2011 at 4:11 pm

    Really excellent explanation. I wish it was always this easy to find a concise answer to the error code.

  2. Mike
    January 31, 2012 at 7:43 pm

    Thank You

  3. Pete
    May 25, 2012 at 9:06 am

    Thanks Manoj, the clear example and explanation helped me to quickly get over this issue

  4. Ashish
    May 28, 2012 at 1:26 pm

    Thanks. It worked for me.

  5. July 9, 2012 at 4:42 pm

    Good artical.Worked for me.
    Thanks.

  6. Pankaj
    July 25, 2012 at 11:33 pm

    What if we don’t want to disable the constraint ? Is there any alternate solution to this ?

    Thanks,
    Pankaj T.

  7. Neelam
    April 27, 2013 at 6:40 pm

    Thanks!! Good one

  8. anjaneya raju kollati
    November 21, 2013 at 6:41 pm

    thank you very much! helped a lot.

  9. Mona
    March 12, 2015 at 8:42 pm

    Rally easy and helpful

  10. Hamdi Kendil
    October 17, 2019 at 9:05 pm

    comprehensible and clear
    thank you

  1. March 14, 2012 at 1:59 pm

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: