Self-Reference cascading deletes

[cesar.guinovart@gmail.com]

I have the following table

CREATE TABLE [tbl_Items]
(
[item_id] int IDENTITY(1,1) CONSTRAINT PK_tbl_Items__i tem_id
PRIMARY KEY,
[parent_id] int DEFAULT(NULL) CONSTRAINT
FK_tbl_Items__i tem_id__parent_ id REFERENCES [tbl_Items]( [item_id] ) ON
DELETE NO ACTION ON UPDATE NO ACTION
)


My Intention was to create a table that when I delete a record, all
records that have on the [parent_id] field the deleted record
[item_id].

I am trying to avoid having to use triggers or create a stored
procedure that firsts delete the children (recursively) and then
deletes the parent.

Is there any way to do this by changing my table definition here?

[Erland Sommarskog]

Re: Self-Reference cascading deletes

(cesar.guinovar t@gmail.com) writes:

I have the following table
>
CREATE TABLE [tbl_Items]
(
[item_id] int IDENTITY(1,1) CONSTRAINT PK_tbl_Items__i tem_id
PRIMARY KEY,
[parent_id] int DEFAULT(NULL) CONSTRAINT
FK_tbl_Items__i tem_id__parent_ id REFERENCES [tbl_Items]( [item_id] ) ON
DELETE NO ACTION ON UPDATE NO ACTION
)
>
>
My Intention was to create a table that when I delete a record, all
records that have on the [parent_id] field the deleted record
[item_id].
>
I am trying to avoid having to use triggers or create a stored
procedure that firsts delete the children (recursively) and then
deletes the parent.
>
Is there any way to do this by changing my table definition here?

While you can set up cascading foreign keys in some cases, this is not
one of them.

To use a an after trigger, you would have to remove the constraint.

But you could have an INSTEAD OF trigger. If you are using SQL 2000
you would have to first gather all children in a temp table, and
then delete all childre you found. In SQL 2005 you would use a
recursive CTE instead.

As you did not include which version of SQL Server you are using,
and the solutions are completely different, I don't include an example.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at

http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx