Self referencing foreign-key constraints and delete

what is the recommended way to handle self-referencing foreignkey constraints in 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:

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

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?

Thank you.

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)

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

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.

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.

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

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

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.

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

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