cascade delete ...

Hi!
I have a question about implementing cascade deletes in sql server 2000
database.
I have two tables:
Object (ObjectId, ObjectName, ObjectType, etc.)
Object_Association (ObjectParentId, ObjectId)
Both fields in the association table are foreign key related to the
ObjectId field in the first table. I would like the cascade delete on
both these relations.
So, if the tables had the following records:
1, horse, ..
2, cat, ..
3, cattle, ..
4, swine, ..
and
2, 1
3, 2
4, 2
and I delete the horse record in the object table, it should trigger
deleting the first record in the second table, that should trigger
delete of the cat record, ... and finally all the above records should
be deleted.
I get the following error when I try to set the cascade delete flags on
the foreign key relations in sql server 2000:
Introducing FOREIGN KEY constraint 'Object_Object_Association_FK2' on
table 'Object_Association' may cause cycles or multiple cascade paths.
Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other
FOREIGN KEY constraints.
How do I achieve the complete cascade delete?
Thanks!kazoo wrote:
> Hi!
> I have a question about implementing cascade deletes in sql server
> 2000 database.
> I have two tables:
> Object (ObjectId, ObjectName, ObjectType, etc.)
> Object_Association (ObjectParentId, ObjectId)
> Both fields in the association table are foreign key related to the
> ObjectId field in the first table. I would like the cascade delete on
> both these relations.
> So, if the tables had the following records:
> 1, horse, ..
> 2, cat, ..
> 3, cattle, ..
> 4, swine, ..
> and
> 2, 1
> 3, 2
> 4, 2
> and I delete the horse record in the object table, it should trigger
> deleting the first record in the second table, that should trigger
> delete of the cat record, ... and finally all the above records should
> be deleted.
> I get the following error when I try to set the cascade delete flags
> on the foreign key relations in sql server 2000:
> Introducing FOREIGN KEY constraint 'Object_Object_Association_FK2' on
> table 'Object_Association' may cause cycles or multiple cascade paths.
> Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other
> FOREIGN KEY constraints.
> How do I achieve the complete cascade delete?
> Thanks!
That's a good question. I'm guessing SQL Server cannot handle this type
of delete cascade functionality, probably because of the the
self-referencing table relationship - it won't know which delete cascade
to perform first. You should manage the delete from a stored procedure
instead. From BOL:
"The series of cascading referential actions triggered by a single
DELETE or UPDATE must form a tree containing no circular references. No
table can appear more than once in the list of all cascading referential
actions that result from the DELETE or UPDATE. The tree of cascading
referential actions must not have more than one path to any given table.
Any branch of the tree is terminated when it encounters a table for
which NO ACTION has been specified or is the default."
David Gugick - SQL Server MVP
Quest Software