自引用外键约束并删除

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

在SQL-Server中处理自引用外键约束的推荐方法是什么?

Table-Model:

fiData references a previous record in tabData. If i delete a record that is referenced by fiData, the database throws an exception:

fiData引用tabData中的上一条记录。如果我删除了fiData引用的记录,则数据库会抛出异常:

"The DELETE statement conflicted with the SAME TABLE REFERENCE constraint "FK_tabDataPrev_tabDataNext". The conflict occurred in database "MyDataBase", table "dbo.tabData", column 'fiData'"

“DELETE语句与SAME TABLE REFERENCE约束冲突”FK_tabDataPrev_tabDataNext“。冲突发生在数据库”MyDataBase“,表”dbo.tabData“,列'fiData'”

if Enforce Foreignkey Constraint is set to "Yes".

如果Enforce Foreignkey Constraint设置为“Yes”。

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?

我不需要级联删除被引用的记录,但我需要在引用它的地方设置fiData = NULL。我的想法是将Enforce Foreignkey Constraint设置为“No”并创建一个删除触发器。这是推荐还是有更好的方法?

Thank you.

3 个解决方案

#1

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:

与Andomar不同,我很乐意使用触发器 - 但我不会删除约束检查。如果将其实现为而不是触发器,则可以在执行实际删除之前将其他行重置为null:

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)

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).

它简短,简洁,如果SQL Server可以处理同一个表的外键级联(在其他RDBMS中,您可能只能为外键约束YMMV指定ON DELETE SET NULL)则没有必要。

#2

Triggers add implicit complexity. In a database with triggers, you won't know what a SQL statement does by looking at it. In my experience triggers are a bad idea with no exceptions.

触发器增加了隐式复杂性。在具有触发器的数据库中,您不会通过查看它来了解SQL语句的作用。根据我的经验,触发器是一个坏主意,没有例外。

In your example, setting the enforced constrained to "No" means you could add a nonexistent ID. And the query optimizer will be less effective because it can't assume the key is valid.

在您的示例中,将强制约束设置为“否”意味着您可以添加不存在的ID。并且查询优化器效率较低,因为它无法假定密钥有效。

Consider creating a stored procedure instead:

请考虑创建存储过程:

create procedure dbo.NukeTabData(
    @idData int)
as
begin transaction
update tabData set fiData = null where fiData = @idData
delete from tabData where idData = @idData
commit transaction
go

#3

This very late to answer.

这个回答很晚。

But for some one who is searching like me.

但对于像我这样的人来说。

and want to cascade

并希望级联

here is very good explanation

这里有很好的解释

http://devio.wordpress.com/2008/05/23/recursive-delete-in-sql-server/

The Problem Although you can define a foreign key with CASCADE DELETE in SQL Server, recursive cascading deletes are not supported (i.e. cascading delete on the same table).

问题虽然您可以在SQL Server中使用CASCADE DELETE定义外键,但不支持递归级联删除(即在同一个表上进行级联删除)。

If you create an INSTEAD OF DELETE trigger, this trigger only fires for the first DELETE statement, and does not fire for records recursively deleted from this trigger.

如果创建INSTEAD OF DELETE触发器,则此触发器仅触发第一个DELETE语句,并且不会触发从此触发器递归删除的记录。

This behavior is documented on MSDN for SQL Server 2000 and SQL Server 2005.

SQL Server 2000和SQL Server 2005的MSDN上记录了此行为。

The Solution Suppose you have a table defined like this:

解决方案假设您有一个如下定义的表:

CREATE TABLE MyTable (
    OID    INT,        -- primary key
    OID_Parent INT,    -- recursion
    ... other columns
)

then the delete trigger looks like this:

然后删除触发器如下所示:

CREATE TRIGGER del_MyTable ON MyTable INSTEAD OF DELETE
AS
    CREATE TABLE #Table(
        OID    INT
    )
INSERT INTO #Table (OID)
SELECT  OID
FROM    deleted

DECLARE @c INT
SET @c = 0

WHILE @c <> (SELECT COUNT(OID) FROM #Table) BEGIN
    SELECT @c = COUNT(OID) FROM #Table

    INSERT INTO #Table (OID)
    SELECT  MyTable.OID
    FROM    MyTable
    LEFT OUTER JOIN #Table ON MyTable.OID = #Table.OID
    WHERE   MyTable.OID_Parent IN (SELECT OID FROM #Table)
    AND     #Table.OID IS NULL
END

DELETE  MyTable
FROM    MyTable
INNER JOIN #Table ON MyTable.OID = #Table.OID

GO