Multiple Cascade Paths Error in SQL Server

Working on DrySQL has given me an opportunity to learn about the subtle differences between many DBMSs.

As my project is designed to run on a number of DBMSs, I crafted a reasonably complex test DB schema that I create on each DBMS in order to run my unit tests.

Though my test schema can be created without error on MySQL, PostgreSQL, and DB2, when I attempted to create it on SQL Server Express 2005 recently, it errored on the attempted creation of a FOREIGN KEY constraint with cascaded deletes and updates:

Server: Msg 1785, Level 16, State 1, Line 1 Introducing FOREIGN KEY constraint 'PAYMT_APP_FK2' on table 'PAYMENT_APPLICATION' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints. Server: Msg 1750, Level 16, State 1, Line 1 Could not create constraint. See previous errors.

I'll explain the error using the following table layout.


Note that all the FOREIGN KEYs were created with cascaded delete and update options.

The constraint definition that produced the error above was in my CREATE TABLE statement for the PAYMENT_APPLICATION table.

[PAYMENT_APPLICATION.BILL_ID...]FOREIGN KEY REFERENCES MONTHLY_BILL.ID CASCADE DELETE CASCADE UPDATE

According to Microsoft the above error occurs because "a table cannot appear more than one time in a list of all the cascading referential actions that are started by either a DELETE or an UPDATE statement". So, my constraint definition produces an error because the cascading delete path for INVOICE would like this if the constraint were defined:

My PAYMENT_APPLICATION table will appear twice on INVOICE's cascading delete/update path:

1. PAYMENT_APPLICATION has an FK reference to INVOICE with cascading delete/update options


2. PAYMENT_APPLICATION has an FK reference to MONTHLY_BILL with cascading delete/update options, and MONTHLY_BILL has an FK reference to INVOICE with cascading delete/update options
   

As I mentioned earlier, SQL Server stands alone (to my knowledge) as the only DBMS that considers such constraints to be illegal. A simple solution to my problem would be to remove the cascade options from my PAYMENT_APPLICATION-->INVOICE Foreign Key constraint. The deletion/update of a referenced INVOICE or MONTHLY_BILL will still trigger a cascade onto the related PAYMENT_APPLICATION record, which is ultimately the behaviour that I want.

I can still create a DB schema that meets my needs in SQL Server, but it's annoying that I need to define my constraints differently than I do with other databases (although fairly typical of Microsoft software). I can't help wondering if this is a feature or a limitation.

If anyone else has an opinion about this, I'll be interested to hear it.
...And if anyone else experiences this error and struggles to figure out why, hopefully you find this explanantion helpful.

Labels: database

posted by Bryan Evans @ 2:12 PM Comments: 19