The random ramblings of a techie

MS SQL Server cyclical reference nonsense

On SQL Server 2008

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…

Facebook Twitter Digg Reddit Linkedin Email

, ,

3 Responses to “MS SQL Server cyclical reference nonsense”

  • Robinson says:

    Very true! Makes a chagne to see someone spell it out like that. :)

  • Adelphia says:

    Wowza, problem slvoed like it never happened.

    • Nica says:

      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

Your email address will not be published. Required fields are marked *

Please type the characters of this captcha image in the input box

Please type the characters of this captcha image in the input box

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>