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 relatedt_parent
record is deleted (ON DELETE CASCADE
), andt_link
records related to deletedt_child
deleted as well - all
t_link
records deleted when their relatedt_parent
record is deleted (ON DELETE CASCADE
) t_link.id_child
set toNULL
when their relatedt_child
record is deleted or deleted as well, if it makes things easier (ON DELETE SET NULL
orON 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.