Click here to Skip to main content
12,546,935 members (66,802 online)
Click here to Skip to main content
Add your own
alternative version

Tagged as


1 bookmarked

MS SQL Server cyclical reference nonsense

, 29 Jan 2013 CPOL
Rate this:
Please Sign up or sign in to vote.
MS SQL Server cyclical reference issue.

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
---------  --------

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
---------    --------    ---------

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…


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


About the Author

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.

You may also be interested in...


Comments and Discussions

GeneralMy vote of 1 Pin
Hugo de Vreugd29-Jan-13 21:05
memberHugo de Vreugd29-Jan-13 21:05 
Just tested (SQL Server 2012) and it works like a charm.
GeneralRe: My vote of 1 Pin
VentsyV31-Jan-13 17:51
memberVentsyV31-Jan-13 17:51 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    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 | Terms of Use | Mobile
Web02 | 2.8.161018.1 | Last Updated 29 Jan 2013
Article Copyright 2013 by VentsyV
Everything else Copyright © CodeProject, 1999-2016
Layout: fixed | fluid