One thing that’s always bothered me about mssql is its attitude toward table circular references, or “cyclic references” if you’re an Oracle bod.

For the uninitiated a circular table reference is a term given to a set of tables which reference each other, either directly or through one or more joining tables using a foreign key constraint.

While SQL Server will quite happily let you create a network of such references, it quickly throws its bytes out of the pram as soon as you try and mandate that these references should be anything other than ON DELETE NO ACTION ON UPDATE NO ACTION with something similar to the following error message:

Introducing FOREIGN KEY constraint 'FK_xxx_xxx' on table 'tblxxx' may cause cycles or multiple cascade paths.Specify ON DELETE NOACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.

I mean what’s the point of defining your data schema correctly, with the perfect foreign key constraints in place if, with half of them set to NO ACTION, many rows cannot be deleted because they’re always being referenced by another row?

While Wikipedia will tell you that “Most relational databases such as Oracle and SQL Server do not allow circular referencing because there is always a problem when deleting a row from a table having dependencies to another row from another table (foreign key) which refers to the row being deleted.”

Now while this may sound like a good reason on the surface, it’s not. When deleting a row, there’s nothing stopping any DBMS, whilst walking the network of cascade constraints from simply ignoring the source row if it encounters it. That row is going to be deleted anyway –no special action is needed when/if it encounters it again.

Not all DBMS’s suffer from this issue; PostgresSQL for example supports circular cascade foreign key constraints simply as part of its implementation of ANSI SQL. This has me believing that, as the problem is obviously solvable, Microsoft will support multiple cascade paths in a later version of SQL Server. Until then, however, there is a work-around:

Define your foreign keys as normal and set as many as you need to CASCADE updates and deletes. Then, when you receive your first error message about multiple cascade paths, leave that reference as NO ACTION and simply create a trigger on the primary table which will delete the foreign table row when the primary row is deleted.

A bit nasty, but if you’re a CASCADE-junkie like myself – just about acceptable.