Sql error on update : The UPDATE statement conflicted with the FOREIGN KEY constraint

Question

I have a table called patient_address, which reference a PK key in patient table. But if I try to run one of the following statements :

update patient set id_no='7008255601088' where id_no='8008255601089'
update patient_address set id_no='7008255601088' where id_no='8008255601089'

I get this error message:

"The UPDATE statement conflicted with the REFERENCE constraint "FK__patient_a__id_no__27C3E46E". The conflict occurred in database "PMS", table "dbo.patient_address", column 'id_no'." or "The UPDATE statement conflicted with the FOREIGN KEY constraint "FK__patient_a__id_no__27C3E46E". The conflict occurred in database "PMS", table "dbo.patient", column 'id_no'." .

Does any body know the possible cause ? Thanks.

The column `dbo.patient_address.id_no` allows `NULL`s ? If yes, then you could SET `dbo.patient_address.id_no` to NULL, update the `dbo.patient.id_no` and then SET `dbo.patient_address.id_no` to the new value. — Bogdan Sahlean, May 25 '14 at 15:08
The problem was a cascade thing in Answer 4. I'm sorted now. Thank you — chosenOne Thabs, May 25 '14 at 15:22

Milica Medic Kiralj · Accepted Answer · 2014-05-25T15:21:13.653

61

This error is encountered when the primary key of a table is updated but it is referenced by a foreign key from another table and the update specific is set to No action. The No action is the default option.

If this is your case and No action is set on the update operation you can change the foreign-key definition to Cascade.

Right click your foreign key and select Modify. In the Foreign key relationships dialog under the INSERT and UPDATE specifics set the UPDATE rule on Cascade:

enter image description here

You can also set the rule using T-SQL:

ALTER TABLE YourTable
DROP Constraint Your_FK
GO

ALTER TABLE YourTable
ADD CONSTRAINT [New_FK_Constraint]
FOREIGN KEY (YourColumn) REFERENCES ReferencedTable(YourColumn)
ON DELETE CASCADE ON UPDATE CASCADE
GO

Hope this helps

edited May 25 '14 at 15:21

answered May 25 '14 at 15:14

Milica Medic Kiralj

3,580
31
31

6

I have set the 'ON DELETE CASCADE ON UPDATE CASCADE' as you have advised here. Still I am getting the same error. Do you have any idea? – Kushan Randima Jun 08 '16 at 12:58
@KushanRandima Make sure you update the primary key table, not the dependent one. For example, if Order references User, do `UPDATE User SET Id = ...`, not `UPDATE Order SET UserID = ....` – Daria Sep 22 '16 at 11:08
Saved me the effort of dropping all constraints and recreating them after the update statement. Timesaver! – brz Jan 11 '19 at 10:55
How do you open that foreign key relationship dialog in Visual Studio 2019? Anyone? – zhrgci Dec 01 '19 at 11:32
@Zheng-rongCai That dialog is from SQL Server Management Studio. – Niklas May 22 '20 at 06:53

score 18 · Answer 2 · answered Jul 11 '19 at 21:12

If you don't want to change your table structure, you can run the following query:

ALTER TABLE [UserStore] 
NOCHECK CONSTRAINT FK_UserStore_User_UserId

ALTER TABLE [UserIdentity]
NOCHECK CONSTRAINT  FK_UserIdentity_User_UserId

BEGIN TRAN

UPDATE  [user] 
SET Id = 10
WHERE Id = 9

UPDATE  [dbo].[UserStore]
SET UserId = 10
WHERE UserId = 9

UPDATE  [dbo].UserIdentity
SET UserId = 10
WHERE UserId = 9

COMMIT TRAN

ALTER TABLE [UserStore] 
CHECK CONSTRAINT FK_UserStore_User_UserId

ALTER TABLE UserIdentity 
CHECK CONSTRAINT FK_UserIdentity_User_UserId

score 4 · Answer 3 · answered Feb 06 '19 at 10:06

4

It sometimes happens when you try to Insert/Update an entity while the foreign key that you are trying to Insert/Update actually does not exist. So, be sure that the foreign key exists and try again.

answered Feb 06 '19 at 10:06

Masoud Darvishian

3,754
4
33
41

score 1 · Answer 4 · answered Aug 06 '16 at 03:41

In MySQL

set foreign_key_checks=0;

UPDATE patient INNER JOIN patient_address 
ON patient.id_no=patient_address.id_no 
SET patient.id_no='8008255601088', 
patient_address.id_no=patient.id_no 
WHERE patient.id_no='7008255601088';

Note that foreign_key_checks only temporarily set foreign key checking false. So it need to execute every time before update statement. We set it 0 as if we update parent first then that will not be allowed as child may have already that value. And if we update child first then that will also be not allowed as parent may not have that value from which we are updating. So we need to set foreign key check. Another thing is that if you are using command line tool to use this query then put care to mention spaces in place where i put new line or ENTER in code. As command line take it in one line, so it may happen that two words stick as patient_addressON which create syntax error.

score 1 · Answer 5 · answered Nov 08 '18 at 23:28

This was the solution for me:

-- Check how it is now
select * from patient
select * from patient_address

-- Alter your DB
alter table patient_address nocheck constraint FK__patient_a__id_no__27C3E46E
update patient 
set id_no='7008255601088'
where id_no='8008255601088'

alter table patient_address nocheck constraint FK__patient_a__id_no__27C3E46E
update patient_address 
set id_no='7008255601088'
where id_no='8008255601088'

-- Check how it is now
select * from patient
select * from patient_address

score 1 · Answer 6 · answered Apr 03 '19 at 10:37

Reason is as @MilicaMedic says. Alternative solution is disable all constraints, do the update and then enable the constraints again like this. Very useful when updating test data in test environments.

exec sp_MSforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"

update patient set id_no='7008255601088' where id_no='8008255601088'
update patient_address set id_no='7008255601088' where id_no='8008255601088'

exec sp_MSforeachtable "ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all"

Source:

https://stackoverflow.com/a/161410/3850405

score 0 · Answer 7 · edited May 27 '17 at 03:25

0

I guess if you change the id_no, some of the foreign keys would not reference anything, thus the constraint violation. You could add initialy deffered to the foreign keys, so the constraints are checked when the changes are commited

edited May 27 '17 at 03:25

jezzah

91
1
9

answered May 25 '14 at 14:45

wastl

2,643
14
27

According to this link : http://stackoverflow.com/questions/998095/deferrable-constraints-in-sql-server, SQL Server doesn't support Deffered. Is this true ? I was busy figuring out how to apply Deffered on foreign keys – chosenOne Thabs May 25 '14 at 14:56
It depends on the server, i think postgrsql supports it. For other servers, i dont know – wastl May 25 '14 at 14:58
Unfortunately my server doesn't support postgrsql. – chosenOne Thabs May 25 '14 at 15:14

Charan Raj · Answer 8 · 2016-01-27T20:35:21.037

I would not change the constraints, instead, you can insert a new record in the table_1 with the primary key (id_no = 7008255601088). This is nothing but a duplicate row of the id_no = 8008255601088. so now patient_address with the foreign key constraint (id_no = 8008255601088) can be updated to point to the record with the new ID(ID which needed to be updated), which is updating the id_no to id_no =7008255601088.

Then you can remove the initial primary key row with id_no =7008255601088.

Three steps include:

Insert duplicate row for new id_no
Update Patient_address to point to new duplicate row
Remove the row with old id_no

Sql error on update : The UPDATE statement conflicted with the FOREIGN KEY constraint

8 Answers8

Linked