SQL 使用外键更新表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2670170/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL update a table with a foreign key
提问by derek
I have a categories table, which one of the fields serves as the foreign key for a sub-categories table. One field that serves as part of the primary key for each table is the language id. I need to update these in both tables. Basically, wherever the language id = x in both tables, I need to set it to y.
我有一个类别表，其中一个字段用作子类别表的外键。作为每个表的主键的一部分的一个字段是语言 ID。我需要在两个表中更新这些。基本上，无论两个表中的语言 id = x，我都需要将其设置为 y。
When I try to do an update on either table, I get a 'The UPDATE statement conflicted with the REFERENCE constraint..' which refers to the foreign key constraint.
当我尝试对任一表进行更新时，我收到一条“与 REFERENCE 约束冲突的 UPDATE 语句......”，它指的是外键约束。
How can I update the language field on both of these tables?
如何更新这两个表上的语言字段？
回答by KM.
If you are doing a 1 time fix, drop the constraint, UPDATE and then add the constraint back.
如果您要进行 1 次修复，请删除约束、更新，然后重新添加约束。
If you need to do this as part of the application, insert one category row, update the sub-categories row, and then delete original category row.
如果您需要在应用程序中执行此操作，请插入一个类别行，更新子类别行，然后删除原始类别行。
回答by RedFilter
You need to drop the constraints, update the values, and then re-add the constraints.
您需要删除约束，更新值，然后重新添加约束。
回答by Kevin LaBranche
回答by Mark Byers
You could change your constraint to ON UPDATE CASCADE.
您可以将约束更改为ON UPDATE CASCADE.
回答by Philip Kelley
I'm always leery about disabling constraints, and you reallydon't want to do that if this is a common operation.
我总是对禁用约束持怀疑态度，如果这是一个常见的操作，你真的不想这样做。
An admittedly ugly alternative is to: - Create a row in the parent table, based on the row to be updated but containing the new foreign key value - Update all child rows where the foreign key contains the old value with the new value. - Delete the now-unused parent key row
一个公认的丑陋替代方法是： - 基于要更新但包含新外键值的行在父表中创建一行 - 用新值更新外键包含旧值的所有子行。- 删除现在未使用的父键行
This is awkward for any number of obvious reasons, and may not be suitable to your implementation, but it maintains referential integrity within the database.
由于许多明显的原因，这很尴尬，并且可能不适合您的实现，但它保持了数据库内的引用完整性。
回答by Mehsaan1719
Use this, no need to remove and add as well.
使用这个，不需要删除和添加。
ALTER TABLE Table_Name
NOCHECK CONSTRAINT FoerignKey_Name
--update query here
ALTER TABLE Table_Name
CHECK CONSTRAINT FoerignKey_Name
For more info MSDN link : https://docs.microsoft.com/en-us/sql/relational-databases/tables/disable-foreign-key-constraints-with-insert-and-update-statements?view=sql-server-ver15
有关更多信息 MSDN 链接：https: //docs.microsoft.com/en-us/sql/relational-databases/tables/disable-foreign-key-constraints-with-insert-and-update-statements?view=sql-服务器-ver15
