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.
This question is related to
sql-server
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;
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
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.
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:
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:
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.
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
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
Source: Stackoverflow.com