Delete records of table which has 2 foreign keys that reference to same table

Question

I have 2 tables, first one is Compartment and second one is AboveCompartment. Please see the below. Above compartment has 2 columns which are foreign keys and reference to the Compartment table. When I set the delete and update action as cascade for 2 foreign keys, I get the error below.

Introducing FOREIGN KEY constraint 'FK_AboveCompartment_Compartment1' on table 'AboveCompartment' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.

Below CompId and AboveCompId are foreign keys and reference to the Compartment table. Which way should I follow to add delete cascading? I used the trigger below but it also didn't work and get error added below.

AboveCompartment

Compartment

Trigger

ALTER TRIGGER [dbo].[delFromCompartment] on [dbo].[Compartment]
    FOR DELETE
AS
    DELETE FROM dbo.AboveCompartment
    WHERE CompId IN(SELECT deleted.Id FROM deleted)

Error

Answer 1

You cannot implement this using cascades, as SQL Server does not let you.

You also cannot implement it using triggers, because the foreign key is enforced before you get to the trigger.

You need to write a stored procedure that first deletes the parent table rows, then the child table

CREATE OR ALTER PROCEDURE dbo.Delete_Compartment
  @CompartmentId int
AS

SET XACT_ABORT, NOCOUNT ON;   -- always use XACT_ABORT if you have a transaction

BEGIN TRAN;

DELETE AboveCompartment
WHERE CompId = @CompartmentId;

DELETE AboveCompartment
WHERE AboveCompId = @CompartmentId;

DELETE Compartment
WHERE Id = @CompartmentId;

COMMIT;

I must say, this table design is somewhat suspect. AboveCompId as a column name implies that it represents a single parent for multiple children, rather than multiple parents for multiple children.

If so then you should instead implement this as a self-referencing foreign key. Drop the AboveCompartment table, and add a column

ALTER TABLE Compartment
  ADD AboveCompId int NULL REFERENCES Compartment (Id);

This foreign key also cannot be cascading. But now the delete is only on one table, but you can do it in a recursive fashion. As long as you delete all rows in one go, you shouldn't have an issue with foreign key conflicts.

CREATE OR ALTER PROCEDURE dbo.Delete_Compartment
  @CompartmentId int
AS

SET NOCOUNT ON;

-- no transaction needed as it's one statement

WITH cte AS (
    SELECT @CompartmentId AS Id

    UNION ALL

    SELECT c.Id
    FROM Compartment c
    JOIN cte ON cte.Id = c.AboveCompId;
)
DELETE c
FROM Compartment c
JOIN cte ON cte.Id = c.Id;