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…

3 Replies to “MS SQL Server cyclical reference nonsense”

    1. Very useful. I’ve tried fwololing this with the only difference being I’m using the non express versions of VS2010 and SQL2008R2. I can connect to my database fine but when I try the database table drag and drop operation to the asp.net page I get an extremely generic error stating The specified module could not be found. (Exception from HRESULT: 0x8007007E) .I’m assuming you didn’t get this error message but have you any idea what it could be referring to ?ThanksScott http://hgygycyvner.com [url=http://gkxvvnjoef.com]gkxvvnjoef[/url] [link=http://sturefy.com]sturefy[/link]

Leave a Reply