September 30, 2010 at 11:02 am
I am trying to update a sql server 2005 table row, getting below error.
Msg 547, Level 16, State 0, Line 14
The UPDATE statement conflicted with the FOREIGN KEY constraint "IUser1". The conflict occurred in database "DBName", table "dbo.Table2".
The statement has been terminated.
I am using the below statement for update
Use DBName
UPDATE User
SET StatusID = 1
WHERE UserID = 46
Go
Please advice
September 30, 2010 at 11:39 am
A FOREIGN KEY constraint specifies that the value(s) for some column(s) must uniquely identify one row in the referenced table. You have a foreign key constraint "IUser1" on your User table that references Table2. The value you have specified for StatusID causes your FOREIGN KEY to no longer match any record in Table2. Since the updated value doesn't match a record in Table2, your update fails.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
September 30, 2010 at 12:04 pm
Thanks for your reply.
This is production and I need to update the record. What is solution for this?
September 30, 2010 at 12:08 pm
You are going to need to find out what the constraint is that the Update is falling afoul of. I would imagine that the problem is with the StatusID.... since that is what you are trying to change. Maybe you have a Status Table with a list of ID's, and the #1 ID has been removed for some reason...???
September 30, 2010 at 1:28 pm
Below are the 3 related tables. I want update User table, change the statusID to 1 for users 46 and 48.
TableName: User
UserID(PK) StatusID(FK) StatusReasonID(FK)
46 4 2
48 4 2
50 1 NULL
TableName : Status
StatusID(PK,FK) StatusName
1 Ictive
2 Idle
3 Reg complete
4 In-Active
TableName: StatusReason
StatusID(pk) StatusReasonID StatusReasonName
4 1 account closed
4 2 user no longer wishes to participate
4 3 user no longer wishes to practice
4 4 other
September 30, 2010 at 2:25 pm
Your FOREIGN KEY is referencing a table called Table2. You probably need to change it to reference the Status table.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
September 30, 2010 at 2:33 pm
Table2 name is StatusReason
October 1, 2010 at 8:13 am
laddu4700 (9/30/2010)
Table2 name is StatusReason
The foreign key should be referencing Status, not StatusReason.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
October 1, 2010 at 9:21 am
here is the error
Msg 547, Level 16, State 0, Line 14
The UPDATE statement conflicted with the FOREIGN KEY constraint "IUser1". The conflict occurred in database "DBName", table "dbo.StatusReason".
The statement has been terminated.
October 1, 2010 at 9:51 am
laddu4700 (9/30/2010)
Below are the 3 related tables. I want update User table, change the statusID to 1 for users 46 and 48.TableName: User
UserID(PK) StatusID(FK) StatusReasonID(FK)
46 4 2
48 4 2
50 1 NULL
TableName : Status
StatusID(PK,FK) StatusName
1 Ictive
2 Idle
3 Reg complete
4 In-Active
TableName: StatusReason
StatusID(pk) StatusReasonID StatusReasonName
4 1 account closed
4 2 user no longer wishes to participate
4 3 user no longer wishes to practice
4 4 other
If you look at what you are doing, you are attempting to change StatusID = 1, when the only StatusID in StatusReason is 4.
Your foreign key for StatusID should be pointing to the Status table, not the StatusReason table.
Hence, your error.
October 1, 2010 at 12:58 pm
Thanks to all.
Can I any body provide the solution/script to update the statusID in User table.
October 1, 2010 at 1:10 pm
laddu4700 (10/1/2010)
Thanks to all.Can I any body provide the solution/script to update the statusID in User table.
The initial script you wrote will work if you change the foreign key to point to the Status table.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply