Multiple cascade paths

Hello,

I started using SQL Server 2005 and encountered a bug/missing feature.

The cascade behavior on updates and deletions is made in such a way 2 different fields in a given table cannot be accessed through 2 different cascade paths. This limitation is "explained", in fact, in the documentation where the engine enforces the reachable-on-update/delete tables to fit in a tree. A directed acyclic graph would have been appreciated. Or, if you are bound to using a tree, why not replacing table nodes by field ones.

That limitation raises implementation-dependent concerns up to the design phase. Whereas MS Access 2003 allows such behavior... couldn't you collaborate with the Access development team so you did not have to redo the job?

Finally, browsing other threads, I came across another similar limitation which is as restrictive as the previous one, according to me. It's impossible to self-reference a table... and Access supports this feature too.
I thought you were ready after 5 years, but I see very few enhancements in compliance with the SQL standard, not to say any. Besides that, SQL Server 2005 remains a nice tool to use.

Yours,
Intenion

The limitation is still there in June 2007 CTP of SQL Server 2008!!!!

Seems like a bad joke since Oracle and DB2 and probably all other databases has supported this for year (even decades?).

Makes all these nice features like "Delete Cascade" and "Delete Set Null" worthless in most real world relation databases.

Really hope they will fix this to the final SQL Server 2008...

/Andreas

|||

(The cascade behavior on updates and deletions is made in such a way 2 different fields in a given table cannot be accessed through 2 different cascade paths. This limitation is "explained", in fact, in the documentation where the engine enforces the reachable-on-update/delete tables to fit in a tree. A directed acyclic graph would have been appreciated. Or, if you are bound to using a tree, why not replacing table nodes by field ones.

That limitation raises implementation-dependent concerns up to the design phase. Whereas MS Access 2003 allows such behavior... couldn't you collaborate with the Access development team so you did not have to redo the job?)

It is not a bug but the correct implementation of ANSI SQL DRI(declarative referential integrity) rules which says if a references b b must exist. What that means primary key a becomes foreign key b you can use a to delete b Cascade delete, you can also use a to update b cascade update and a can set b to default SET Default and null SET NULL.

What is implemented in Access is not relational, I have not used DB2 for a while now but Oracle DRI is not different from SQL Server. Microsoft did not say you cannot have more than one but for that you need a trigger, a trigger can be used to delete and update as many as you want.

BTW that feature came to us from one of the most fabulous Algebras of the 20th century all twenty six pages of it.