自引用外键约束和删除 [英] Self referencing foreign-key constraints and delete

问题描述

在SQL-Server中处理自引用外键约束的建议方法是什么？

what is the recommended way to handle self-referencing foreignkey constraints in SQL-Server?

表模型：

code> fiData 引用tabData中的上一条记录。如果我删除由 fiData 引用的记录，数据库会引发一个异常：DELETE语句与SAME TABLE REFERENCE约束FK_tabDataPrev_tabDataNext 。冲突发生在数据库MyDataBase，表dbo.tabData，列'fiData'如果 Enforce Foreignkey Constraint 设置为是的。

fiData references a previous record in tabData. If i delete a record that is referenced by fiData, the database throws an exception: "The DELETE statement conflicted with the SAME TABLE REFERENCE constraint "FK_tabDataPrev_tabDataNext". The conflict occurred in database "MyDataBase", table "dbo.tabData", column 'fiData'" if Enforce Foreignkey Constraint is set to "Yes".

我不需要级联删除被引用的记录，但我需要设置 fiData = NULL 引用它。我的想法是将强制外键约束设置为否并创建一个删除触发器。这是可推荐的还是有更好的方法？

I don't need to cascade delete records that are referenced but i would need to set fiData=NULL where it's referenced. My idea is to set Enforce Foreignkey Constraint to "No" and create a delete-trigger. Is this recommendable or are there better ways?

谢谢。

推荐答案

p>不像Andomar，我会很高兴使用触发器 - 但我不会删除约束检查。如果将其实现为而不是触发器，则可以在执行实际删除之前将其他行重置为null：

Unlike Andomar, I'd be happy using a trigger - but I wouldn't remove the constraint checking. If you implement it as an instead of trigger, you can reset the other rows to null before performing the actual delete:

CREATE TRIGGER T_tabData_D
on tabData
instead of delete
as
    set nocount on
    update tabData set fiData = null where fiData in (select idData from deleted)
    delete from tabData where idData in (select idData from deleted)

简短，简洁，如果SQL Server可以处理外键关联到同一个表（在其他RDBMS'，你可以只是指定 ON DELETE SET NULL 用于外键约束YMMV）。

It's short, it's succinct, it wouldn't be necessary if SQL Server could handle foreign key cascades to the same table (in other RDBMS', you may be able to just specify ON DELETE SET NULL for the foreign key constraint, YMMV).

这篇关于自引用外键约束和删除的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！