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  8008255601088  update patient address set id no  7008255601088  where id no  8008255601088    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

User · Answer

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

User · Answer

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

User · Answer

If column dbo patient address id no allows NULLs then you could use this solution   SET XACT ABORT ON  BEGIN TRANSACTION   -- I assmume that  id  is the primary key of patient address table  single column key  -- replace the name of  id  column with the name of PK column from patient address table -- replace INT data type with the proper type DECLARE  RowsForUpdate TABLE  id  INT PRIMARY KEY    UPDATE patient address  SET id no   NULL OUTPUT deleted  id  INTO  RowsForUpdate   id   WHERE id no  8008255601088   UPDATE patient  SET id no  7008255601088   WHERE id no  8008255601088   UPDATE patient address  SET id no  7008255601088   WHERE  id  IN  SELECT u  id  FROM  RowsForUpdate u   COMMIT

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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     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

User · Answer

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

User · Answer

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

[sql-server] Sql error on update : The UPDATE statement conflicted with the FOREIGN KEY constraint

Examples related to sql-server