SQL Server:如何知道是否有任何行引用要删除的行
如果任何行通过 FK 引用要删除的行,则无法删除该行。
在执行 DELETE 语句之前是否可以知道是否有行正在引用要删除的行?
You cannot delete a row if any row is referencing the row to delete via a FK.
Is it possible to know if any row is referencing the row to delete before executing a DELETE statement?
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(4)
此脚本将显示所有包含引用您要删除的行的行的表:
假设外键不是复合键。
This script will show all the tables that have rows that reference the row you are trying to delete:
Assumption that foreign key is not composite.
选项 1(检测):
您执行
Select 语句
来确定是否有任何记录引用要删除的记录 - 如果您愿意, ,手动删除那些引用它的记录。这也可以使用触发器来完成,尽管我建议不要使用触发器,因为它们往往会给人们(和你自己)带来惊喜。选项 2(自动化):
您可以查看 级联删除,如果配置正确,将导致引用要删除的记录的所有记录也被删除。
何时使用级联删除(摘自 Joel Coehoorn)
这里有关于 stackoverflow 上的级联删除的精彩讨论。
Option 1 (Detection):
You perform a
Select Statement
to determine if any records are referencing the Record-to-be-deleted -- and, if you wish, manually delete those records that do reference it. This can also be accomplished using a trigger, although I recommend against triggers, because they tend to surprise people (and yourself) down the road.Option 2 (Automation):
You can look into Cascading Deletes which, if configured correctly, will cause all records referencing the Record-to-be-deleted to also be deleted.
When to use Cascading Deletes (Paraphrased from text written by Joel Coehoorn)
Here's a great discussion on Cascading Deletes on stackoverflow.
没有人提到它,但只是为了记录,我使用了很多过程
来查找与 dbo.mytable 相关的所有约束,以及哪些表引用 dbo.mytable。
我发现它非常有用且方便。
nobody has mentioned it, but just for the record I use a lot the procedure
in order to find all the constraints related to dbo.mytable, and which tables reference dbo.mytable.
I find it very useful and handy.
我改进了 Alex Aza 的解决方案。
我使用的是softdelete,所以有必要在条件“where”中添加一列“delete”。
我在 TSQL 中创建了一个函数,当表代表 NHibernate 中继承的对象时,它会发现该函数,并且 PK 是来自父表的 FK。
遵循:
依赖函数:
I improved the Alex Aza's solution.
I use softdelete, so It's necessary add a column "delete" in the condition "where" .
And more I made a function in TSQL that it discovers when the table represents the object inherited in NHibernate, and the PK is the FK from parent table.
Follow:
Depedencies Functions: