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 :
我有一个名为patient_address的表，它引用患者表中的PK密钥。但是，如果我尝试运行以下语句之一：
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'." .
“UPDATE语句与REFERENCE约束冲突”FK__patient_a__id_no__27C3E46E“。冲突发生在数据库”PMS“，表”dbo.patient_address“，列'id_no'。”或“UPDATE语句与FOREIGN KEY约束冲突”FK__patient_a__id_no__27C3E46E“。冲突发生在数据库”PMS“，表”dbo.patient“，列'id_no'。” 。
Does any body know the possible cause ? Thanks.
有没有人知道可能的原因？谢谢。
5 个解决方案
#1
33
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.
更新表的主键时会遇到此错误，但是来自另一个表的外键引用该错误，并且特定于更新的设置为“无操作”。 No操作是默认选项。
If this is your case and No action is set on the update operation you can change the foreign-key definition to Cascade.
如果是这种情况并且在更新操作上未设置任何操作，则可以将外键定义更改为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:
右键单击外键，然后选择“修改”。在INSERT和UPDATE细节下的外键关系对话框中，在Cascade上设置UPDATE规则：
You can also set the rule using T-SQL:
您还可以使用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
希望这可以帮助
#2
1
In MySQL
在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.
请注意，foreign_key_checks仅临时设置外键检查false。所以它需要在update语句之前每次执行。我们将其设置为0，好像我们先更新父级，然后不允许这样，因为孩子可能已经具有该值。如果我们首先更新孩子，那么也不允许这样做，因为父母可能没有我们正在更新的那个值。所以我们需要设置外键检查。另一件事是，如果您使用命令行工具来使用此查询，那么请注意提到我在代码中添加新行或ENTER的位置。由于命令行将其放在一行中，因此可能会发生两个单词作为patient_addressON而产生语法错误。
#3
0
If column dbo.patient_address.id_no allows NULLs then you could use this solution:
如果列dbo.patient_address.id_no允许NULL，那么您可以使用此解决方案：
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;
#4
0
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.
我不会更改约束，而是可以使用主键（id_no = 7008255601088）在table_1中插入新记录。这不过是id_no上的重复行= 8008255601088.所以现在与外键约束（id_no上= 8008255601088）patient_address可以被更新为指向新ID（ID这需要更新），这是更新记录id_no到id_no = 7008255601088。
Then you can remove the initial primary key row with id_no =7008255601088.
然后，您可以删除id_no = 7008255601088的初始主键行。
Three steps include:
三个步骤包括：
- Insert duplicate row for new id_no
- 为新id_no插入重复行
- Update Patient_address to point to new duplicate row
- 更新Patient_address以指向新的重复行
- Remove the row with old id_no
- 删除旧id_no的行
#5
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
我想如果你改变id_no，一些外键不会引用任何东西，因此违反约束。您可以将initialy deffered添加到外键，因此在提交更改时会检查约束
#1
33
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.
更新表的主键时会遇到此错误，但是来自另一个表的外键引用该错误，并且特定于更新的设置为“无操作”。 No操作是默认选项。
If this is your case and No action is set on the update operation you can change the foreign-key definition to Cascade.
如果是这种情况并且在更新操作上未设置任何操作，则可以将外键定义更改为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:
右键单击外键，然后选择“修改”。在INSERT和UPDATE细节下的外键关系对话框中，在Cascade上设置UPDATE规则：
You can also set the rule using T-SQL:
您还可以使用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
希望这可以帮助
#2
1
In MySQL
在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.
请注意，foreign_key_checks仅临时设置外键检查false。所以它需要在update语句之前每次执行。我们将其设置为0，好像我们先更新父级，然后不允许这样，因为孩子可能已经具有该值。如果我们首先更新孩子，那么也不允许这样做，因为父母可能没有我们正在更新的那个值。所以我们需要设置外键检查。另一件事是，如果您使用命令行工具来使用此查询，那么请注意提到我在代码中添加新行或ENTER的位置。由于命令行将其放在一行中，因此可能会发生两个单词作为patient_addressON而产生语法错误。
#3
0
If column dbo.patient_address.id_no allows NULLs then you could use this solution:
如果列dbo.patient_address.id_no允许NULL，那么您可以使用此解决方案：
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;
#4
0
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.
我不会更改约束，而是可以使用主键（id_no = 7008255601088）在table_1中插入新记录。这不过是id_no上的重复行= 8008255601088.所以现在与外键约束（id_no上= 8008255601088）patient_address可以被更新为指向新ID（ID这需要更新），这是更新记录id_no到id_no = 7008255601088。
Then you can remove the initial primary key row with id_no =7008255601088.
然后，您可以删除id_no = 7008255601088的初始主键行。
Three steps include:
三个步骤包括：
- Insert duplicate row for new id_no
- 为新id_no插入重复行
- Update Patient_address to point to new duplicate row
- 更新Patient_address以指向新的重复行
- Remove the row with old id_no
- 删除旧id_no的行
#5
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
我想如果你改变id_no，一些外键不会引用任何东西，因此违反约束。您可以将initialy deffered添加到外键，因此在提交更改时会检查约束