更新时出现 Sql 错误:UPDATE 语句与 FOREIGN KEY 约束冲突 [英] Sql error on update : The UPDATE statement conflicted with the FOREIGN KEY constraint

问题描述

我有一个名为 patient_address 的表，它引用了 patient 表中的一个 PK 密钥.但是，如果我尝试运行以下语句之一:

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'

我收到此错误消息:

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

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

右键单击您的外键并选择修改.在 INSERT 和 UPDATE 细节下的外键关系对话框中，在 Cascade 上设置 UPDATE 规则:

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:

您也可以使用 T-SQL 设置规则:

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 

希望能帮到你

这篇关于更新时出现 Sql 错误:UPDATE 语句与 FOREIGN KEY 约束冲突的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！