SQL Server：删除带有外键约束的行：事务可以覆盖约束吗？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2740098/
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 Server: Deleting Rows with Foreign Key Constraints: Can Transactions override the constraints?
提问by Mark Redman
I have a few tables where Foreign Key constraints are added. These are used with code generation to set up specific joins in generated stored procedures.
我有几个表，其中添加了外键约束。这些与代码生成一起用于在生成的存储过程中设置特定的连接。
Is it possible to override these constraints by calling multiple deletes within a transaction, specifically "TransactionScope" in C# or is cascaded deleting absolutely required?
是否可以通过在事务中调用多个删除来覆盖这些约束，特别是 C# 中的“TransactionScope”，或者绝对需要级联删除？
回答by HLGEM
Do not use cascade delete, you can cause serious performance issues that way. The best procedure is to do the deletes in order from the lowest child table up to the parent table.
不要使用级联删除，否则会导致严重的性能问题。最好的程序是从最低的子表到父表按顺序进行删除。
Disabling the foreign keys is a prescription for having data integrity problems. The only time something like that should be done is by a DBA who is extremely experienced and well aware of the issues that could cause. If you are asking this question, you are not yet experienced enough to use that technique. Remember when you disable the FK, you disable it for everyone not just your process.
禁用外键是解决数据完整性问题的方法。唯一应该由经验丰富且非常了解可能导致的问题的 DBA 来完成此类操作。如果您问这个问题，说明您还没有足够的经验来使用该技术。请记住，当您禁用 FK 时，您会为所有人禁用它，而不仅仅是您的进程。
回答by OMG Ponies
The only way to "override" a foreign key constraintis to disable it:
“覆盖”外键约束的唯一方法是禁用它：
Disabling a FOREIGN KEY constraint enables data in the table to be modified without being validated by the constraints. Disable a FOREIGN KEY constraint during INSERT and UPDATE statements if new data will violate the constraint or if the constraint should apply only to the data already in the database.
禁用 FOREIGN KEY 约束可以修改表中的数据，而无需通过约束验证。如果新数据将违反约束或约束应仅应用于数据库中已有的数据，则在 INSERT 和 UPDATE 语句期间禁用 FOREIGN KEY 约束。
You need to use the ALTER TABLEcommand to disable a constraint, using the NOCHECKkeyword. IE:
您需要使用该ALTER TABLE命令来禁用约束，使用NOCHECK关键字。IE：
ALTER TABLE dbo.cnst_example NOCHECK CONSTRAINT salary_cap;
The only other alternative is to drop the constraint, and re-add when necessary.
唯一的其他选择是删除约束，并在必要时重新添加。
The necessity of doing this should lead to discussions about how to model the tables so this is not necessary.
这样做的必要性应该引起关于如何对表格建模的讨论，因此这不是必需的。
回答by Otávio Décio
You can't override FK constrains, if you could what would be the point of creating them in the first place?
您不能覆盖 FK 约束，如果可以的话，首先创建它们有什么意义？
回答by Philippe Grondier
If your FK constraints are specifically set for specific use in stored procedures, these are not reallyFK's, aren't they? a nice solution would be to update the corresponding code by creating the constraints at the beginning of the proc, and clearing them when your code is done. Do not forget then to deal with the case where your temporary constraint cannot be checked against the data.
如果您的 FK 约束是专门为在存储过程中的特定用途而设置的，那么这些就不是真正的FK，不是吗？一个不错的解决方案是通过在 proc 开始时创建约束并在代码完成后清除它们来更新相应的代码。不要忘记处理无法根据数据检查临时约束的情况。
回答by jm7
Constraints can be set to either immediate, or delayed to the end of a transaction. Delaying to the end of a transaction allows you to violate the constraints while you are building the transaction, but enforce them at the end of the transaction. From what I understand, delaying to the end of a transaction is what you are probably after.
约束可以设置为立即或延迟到事务结束。延迟到事务结束允许您在构建事务时违反约束，但在事务结束时强制执行它们。据我了解，延迟到交易结束可能是您所追求的。
