MS SQL Server cyclical reference nonsense

Most relational database software allows a change in one table to trigger changes in other tables based on a foreign key relation. For example let’s say we have two tables: Users and Orders that have a foreign relation defined on the user id field.

Users      Orders
---------  --------
USERID     ORDERID
USERNAME   USERID

SQL Server allows you to define cascading deletes so that when you delete an entry from the users table, all related entries in the orders table are also going to be deleted.

There is a legitimate concern that the cascading deletes could end up creating a circular dependency – delete from table one will cause a delete in another, which would then cause a  delete in first table.

SQL Server tries to detect those and will prevent you from creating a cascading delete if it thinks that it will produce a cycle.

The problem is that it detects a cycle, even when none exists.

Let’s expand the example by adding one more table.

Users        Orders      Products
---------    --------    ---------
USERID       ORDERID     PRODUCTID
USERNAME     USERID      PRODUCT_NAME
             PRODUCTID

It makes sense that when either a product or an user is deleted all the associate rows in the Orders table are to be deleted as well.

This is where SQL Server fails and detects a cycle, even though no cycle exists. Not only that, but there is no way to override the check of somehow work around it.  It seems SQL Server’s development team has taken a shortcut and decided that there should be no more than one cascading delete into a table.

So Microsoft has done it again – they think you are an idiot who can’t be trusted to do the right thing in the first place but then do shabby job at it themselves. Classic Microsoft…