Home > SQL Errors, SQL Tips > UPDATE statement conflicted with the REFERENCE constraint

UPDATE statement conflicted with the REFERENCE constraint


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

About these ads
  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.

  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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 411 other followers

%d bloggers like this: