SQL Server Multiple Cascade Paths (Error 1785): Which Foreign Key Loses ON DELETE CASCADE, and What Replaces It
Disclosure: I build Schemity , a desktop ERD tool - this post is from our blog and uses it for the examples. TL;DR: SQL Server refuses a foreign key when its CASCADE , SET NULL or SET DEFAULT would let one delete or update reach the same table by two paths, and it decides that from the declared actions when the key is created, not from the rows. One key has to become NO ACTION , which makes some…
SQL Server Error 1785 occurs when a foreign key constraint with CASCADE would create multiple paths to the same table, causing a cycle. This prevents the creation of the foreign key constraint. To resolve this issue, one of the foreign keys must be set to NO ACTION, meaning that the DELETE operation will fail with error 547 instead of cascading.
The other foreign key should be replaced with a stored procedure or an INSTEAD OF DELETE trigger on the parent table. This allows the deletion to be handled properly, and the cascade action to proceed as intended. In the given example, the comments.user_id foreign key is set to NO ACTION, while the post.user_id foreign key is replaced with a stored procedure to handle the deletion.
Written by urgent.news from Dev.to's reporting — not their text. Machine-written — may contain errors; check the original before relying on it.
