Solving ON DELETE CASCADE cycles with triggers on MS SQL Server

Question

I have code that is working fine in PostgreSQL and I now have to port it to MS SQL Server. It involves tables with potential cycles on delete/update events and SQL Server is complaining about it:

-- TABLE t_parent
CREATE TABLE t_parent (m_id INT IDENTITY PRIMARY KEY NOT NULL, m_name nvarchar(450));

-- TABLE t_child
CREATE TABLE t_child (m_id INT IDENTITY PRIMARY KEY NOT NULL, m_name nvarchar(450),
    id_parent int CONSTRAINT fk_t_child_parent FOREIGN KEY REFERENCES t_parent(m_id)
    --ON DELETE CASCADE ON UPDATE CASCADE
);

-- TABLE t_link
CREATE TABLE t_link (m_id INT IDENTITY PRIMARY KEY NOT NULL,
    id_parent int CONSTRAINT fk_t_link_parent FOREIGN KEY REFERENCES t_parent(m_id)
    -- ON DELETE CASCADE ON UPDATE CASCADE
    , id_child int CONSTRAINT fk_t_link_child FOREIGN KEY REFERENCES t_child(m_id)
    -- ON DELETE SET NULL ON UPDATE CASCADE
    , link_name nvarchar(450));

I have commented out the ON DELETE/UPDATE constraints that were accepted by PostgreSQL, which show the exact behavior I'm trying to reproduce in MS SQL Server, otherwise I'm getting the error:

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

So I removed them (equivalent to NO ACTION from the documentation) and decided to go the trigger way (as hinted by several sites) to delete related t_link rows when the related t_parent is deleted:

CREATE TRIGGER trg_delete_CASCADE_t_link_id_parent ON t_parent AFTER DELETE AS BEGIN
    DELETE FROM t_link WHERE id_parent IN (SELECT m_id FROM DELETED)
END;

What I'm trying to have overall is:

• all t_child records deleted when their related t_parent record is deleted (ON DELETE CASCADE), and t_link records related to deleted t_child deleted as well
• all t_link records deleted when their related t_parent record is deleted (ON DELETE CASCADE)
• t_link.id_child set to NULL when their related t_child record is deleted or deleted as well, if it makes things easier (ON DELETE SET NULL or ON DELETE CASCADE)

Then I insert a few test data and try :

insert into t_parent (m_name) values('toto');
insert into t_link (id_parent, id_child, link_name) values (1, NULL, 'chan');
delete from t_parent where m_id = 1;

ERROR: The DELETE statement conflicted with the REFERENCE constraint "fk_t_link_parent". The conflict occurred in database "DBTest", table "dbo.t_link", column 'id_parent'.

I'm guessing the problem is my trigger is not called because it happens after the delete itself, which fails with the above message; and there is no BEFORE DELETE trigger type (which would sound like something I'd like to have).

Now I have to say that the SQL is all generated by a Java JPA-like program that has to cope with the different DBMS (one subclass for PostgreSQL, one for SQL Server, ...) so I should stay generic: I can't put ON DELETE CASCADE constraints on one table and use triggers (or any other method you might know) with others (I could, but at the cost of a code over-complexification that I'm trying to avoid).

The SQL Server is a Docker image so I'm not sure I could have debug output somewhere (unless in sqlcmd command). If it's of any relevance, the version is 2017.

The only way out of this I see is just dropping the reference constraint and handle it all manually with triggers. But then: what's the point in having foreign key constraints?

---

EDIT: After David's answer, I should clarify a few points:

The CREATE TABLE and CREATE TRIGGER SQL is generated by code, each time a new table is to be added (from an SQL-agnostic configuration file). As SQL Server can refuse to create ON DELETE CASCADE constraints due to potential cycles, I decided to just indicate the FOREIGN KEY constraint and then have each table referencing t_parent create a FOR DELETE trigger, each performing the CASCADE or SET NULL operation on its own rows.

The suggested INSTEAD OF DELETE trigger is definitely the mechanics I'm looking for, but only a single instance of such trigger can be created for a table (which makes sense) so I didn't go that way.

I might end up creating stored procedures instead of my current triggers, and update the INSTEAD OF trigger each time a new referencing table (and procedure) is added, calling each stored procedure.

Answer 1

You're close. AFTER triggers happen after foreign key constraint checking. So you need an INSTEAD OF trigger. That way you can modify the child tables before performing the DELETE on the target table.

eg

-- TABLE t_parent
CREATE TABLE t_parent 
(
  m_id INT IDENTITY PRIMARY KEY NOT NULL, 
  m_name nvarchar(450)
);

-- TABLE t_child
CREATE TABLE t_child 
(
    m_id INT IDENTITY PRIMARY KEY NOT NULL, 
    m_name nvarchar(450),
    id_parent int CONSTRAINT fk_t_child_parent FOREIGN KEY REFERENCES t_parent(m_id)
      ON DELETE CASCADE ON UPDATE CASCADE
);

-- TABLE t_link
CREATE TABLE t_link (m_id INT IDENTITY PRIMARY KEY NOT NULL,
    id_parent int CONSTRAINT fk_t_link_parent FOREIGN KEY REFERENCES t_parent(m_id)
      ON DELETE NO ACTION
    , id_child int CONSTRAINT fk_t_link_child FOREIGN KEY REFERENCES t_child(m_id)
      ON DELETE CASCADE
    , link_name nvarchar(450));


    go

CREATE OR ALTER TRIGGER trg_delete_CASCADE_t_link_id_parent 
ON t_parent INSTEAD OF DELETE 
AS 
BEGIN

    SET NOCOUNT ON
    DELETE FROM t_link WHERE id_parent IN (SELECT m_id FROM DELETED);
    DELETE FROM t_parent WHERE m_id IN (SELECT m_id FROM DELETED);

END;

go

insert into t_parent (m_name) values('toto');
insert into t_link (id_parent, id_child, link_name) values (1, NULL, 'chan');
delete from t_parent where m_id = 1;

This way t_parent->t_child->t_link uses CASCADE DELETES, and t_parent->t_link is handled by the INSTEAD OF trigger.

Answer 2

For simplicity in the SQL generator, I ended up dropping all foreign key constraints and used AFTER DELETE triggers on tables to better address deletion of t_link records (I want to delete them when their parent and child are deleted/NULL):

CREATE TABLE t_parent (m_id INT IDENTITY PRIMARY KEY NOT NULL, 
    m_name NVARCHAR(450));

t_child table looses it CONSTRAINT xxx FOREIGN KEY, replaced with an AFTER DELETE trigger:

CREATE TABLE t_child (m_id INT IDENTITY PRIMARY KEY NOT NULL, 
    id_parent INT,
    m_name NVARCHAR(450));

-- ON DELETE SET NULL equivalent
CREATE TRIGGER trg_delete_nulls_t_child_t_parent ON t_parent AFTER DELETE AS BEGIN
    UPDATE t_child SET id_parent = NULL WHERE id_parent IN (SELECT m_id FROM DELETED);
END;

Same for t_link, which is an ON DELETE SET NULL promoted to CASCADE if both id_parent and id_child are NULL (i.e. deleted/non-existent):

CREATE TABLE t_link (m_id INT IDENTITY PRIMARY KEY NOT NULL,
    id_parent INT, id_child INT,
    link_name NVARCHAR(450));

-- Trigger ON DELETE SET NULL when t_parent is deleted, or ON DELETE CASCADE if no linked t_child
CREATE TRIGGER trg_delete_nulls_t_link_t_parent ON t_parent AFTER DELETE AS BEGIN
    -- "Promotion" to CASCADE if id_child is also NULL
    DELETE FROM t_link WHERE id_child IS NULL AND id_parent IN (SELECT m_id FROM DELETED);
    -- ON DELETE SET NULL (that might not be triggered if the previous statement has deleted all the records)
    UPDATE t_link SET id_parent = NULL WHERE id_parent IN (SELECT m_id FROM DELETED);
END;

-- Same for t_child deletions vs. t_parent
CREATE TRIGGER trg_delete_nulls_t_link_t_child ON t_child AFTER DELETE AS BEGIN
    UPDATE t_link SET id_child = NULL WHERE id_child IN (SELECT m_id FROM DELETED);
    DELETE FROM t_link WHERE id_parent IS NULL AND id_child IN (SELECT m_id FROM DELETED);
END;

Of course, that is probably less efficient than having hand-crafted constraints, but it's good enough (so far) for my use-case and simplifies the SQL generator code a lot.

I guess there are some "gray" behaviors e.g. when a record id_child is deleted (DELETE FROM t_link ...) and then NULLed by the next line (UPDATE t_link SET id_child=NULL), especially with regards to transactions, but it seems to work on my unit tests.