CODEWITHSUNDEEP
sql-server
chosenOne Thabs
chosenOne Thabs

Reputation: 1640

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

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.

Upvotes: 44

Views: 182116

Answers (8)

Bartho Bernsmann
Bartho Bernsmann

Reputation: 2503

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

Upvotes: 20

Ogglas
Ogglas

Reputation: 69968

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

Upvotes: 1

Masoud Darvishian
Masoud Darvishian

Reputation: 3964

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.

Upvotes: 5

Francesco Mantovani
Francesco Mantovani

Reputation: 12267

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

Upvotes: 1

wastl
wastl

Reputation: 2641

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

Upvotes: 0

YATIN GUPTA
YATIN GUPTA

Reputation: 955

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.

Upvotes: 1

Charan Raj
Charan Raj

Reputation: 481

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

Upvotes: 0

Milica Medic Kiralj
Milica Medic Kiralj

Reputation: 3750

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

Upvotes: 66

Related Questions