Конфликт инструкции update с ограничением reference

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.

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

answered Jul 11, 2019 at 21:12

answered Feb 6, 2019 at 10:06

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.

answered Aug 6, 2016 at 3:41

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

answered Nov 8, 2018 at 23:28

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

answered Apr 3, 2019 at 10:37

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:

1. Insert duplicate row for new id_no
2. Update Patient_address to point to new duplicate row
3. Remove the row with old id_no

answered Jan 15, 2016 at 18:14

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