SQL Server - 在没有 CASCADE 和 INSTEAD OF 触发器的情况下维护引用完整性
我有一个表（TableB）与父表（TableA）有外键关系。
当我删除表 A 中的记录时，我希望通过删除表 B 中引用表 A 中已删除记录的所有记录来保持引用完整性。
通常我会删除级联。然而，由于 SQL Server 中的表结构和针对多个级联路径的过度保护措施，对于这种特定关系来说这是不可能的。
我也无法使用 INSTEAD OF 触发器，因为 TableA 本身具有 CASCADE 外键关系。
我正在考虑做的是将 TableA 和 TableB 之间的关系更改为 ON DELETE SET NULL，然后创建一个 AFTER 触发器来清理 TableB 中的 NULL 记录。
有没有更好的方法来处理这种情况？
I have a table (TableB) that has a foreign key relationship with a parent table (TableA).
When I delete a record in Table A, I want to preserve referential integrity by deleting all records in TableB that reference the deleted record in TableA.
Normally I would ON DELETE CASCADE. However due to the table structure and the overprotective safeguards against multiple cascade paths in SQL Server, this is not possible for this specific relationship.
I also cannot use an INSTEAD OF trigger as TableA itself has a CASCADE foreign key relationship on it.
What I'm thinking of doing is changing the relationship between TableA and TableB to ON DELETE SET NULL, then creating an AFTER trigger to cleanup the NULL records in TableB.
Are there better ways of dealing with this scenario?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
您能否更改阻止您将此约束添加为
ON DELETE CASCADE的其他约束？您能否添加一个
DeleteMe列，然后发出UPDATE A SET DeleteMe = 1，然后使用 after 触发器首先删除表 B 行，然后删除请求的表 A 行？您能否以某种方式拆分或合并表（即垂直地）以分离它们互斥的依赖关系？
Can you change the other constraint that is preventing you from adding this one as
ON DELETE CASCADE?Can you add a
DeleteMecolumn, then issue anUPDATE A SET DeleteMe = 1, then use an after trigger to delete Table B rows first, then the requested table A rows?Can you split or merge tables (vertically, that is) in some way that separates their mutually exclusive dependencies?