Click here to Skip to main content
Click here to Skip to main content

MS SQL Server cyclical reference nonsense

By , 29 Jan 2013
Rate this:
Please Sign up or sign in to vote.

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 – a 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 does a shabby job at it themselves. Classic Microsoft…

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

About the Author

VentsyV
Software Developer
United States United States
I've been developing software for about 5 years now, specializing mostly in industrial and scientific programming. I'm experienced in C/C++ and C# both on Windows and Linux. I'm also fairly experienced in SQL as well.

Comments and Discussions

 
GeneralMy vote of 1 PinmemberHugo de Vreugd29-Jan-13 21:05 
GeneralRe: My vote of 1 PinmemberVentsyV31-Jan-13 17:51 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web01 | 2.8.140415.2 | Last Updated 29 Jan 2013
Article Copyright 2013 by VentsyV
Everything else Copyright © CodeProject, 1999-2014
Terms of Use
Layout: fixed | fluid