SQL: create a table with recursive relationship

[bonk]

I am fairly new to SQL and ADO.NET and I am currently trying to create
a SQL table (using Microsoft SQL and ADO.NET 1.1) that has a recursive
relationship, let me try to explain:

I have a piece of Data let's call it "Item" wich may again contain one
more "Items". Now how would I design a set of SQL Tables that are
capable of storing this information?

I tried the following two approaches:

1.) create a Table "Item" with Column "ItemID" as primary key, some
colums for the Data an Item can store and a Column "ParentItemID". I
set a foreign key for ParentItemID wich links to the primarykey
"ItemID" of the same table.

2.) create separate Table "Item_ParentItem" that stores
ItemID-ParentItemID-pairs. Each column has a foreign key linked to
primary key of the "Item" Column "ItemID".

In both approaches when I try to delete an Item I get an Exception
saying that the DELETE command could not be executed because it
violates a COLUMN REFERENCE constraint. The goal behind these FK_PK
relations is is that when an Item gets deleted, all childItems should
automatically be deleted recursively.

How is this "standard-problem" usually solved in sql? Or do I inned to
implement the recursive deletion myself using C#/ADO.NET or stored
procedures ?

 

[tomb]

Personally, I prefer method #1. To query I just use a table alias for
the children. However, I never tried to delete the parent records and
have it cascade into the children. You might want to look at a trigger
for that.

Tom

 

[bonk]

I googled for quite some time and then finally I found this:
http://www.sqlmag.com/Article/ArticleID/23123/sql_server_23123.html

So I created a trigger like this:

CREATE TRIGGER CascadeDeleteItem ON ItemTable
FOR DELETE
AS
IF @@rowcount = 0 RETURN
DELETE FROM T
FROM ItemTable AS T JOIN deleted AS D
ON T.ParentItemID = D.ItemID

It _seems_ like it is working. Allthough am do not (yet) understand the
sematics of this trigger completly. Why does the DELETE statement of
this trigger have two "FROM" statements ?

 

[Garth Wells]

I'm not saying this is the most efficient approach, but it seems
to work. You would probably get a more efficient answer in
the microsoft.public.sqlserver.programming group.

--
SET NOCOUNT ON

CREATE TABLE Recursive
(
ID int PRIMARY KEY,
F1 varchar(10) NOT NULL,
ParentID int FOREIGN KEY REFERENCES Recursive(ID)
)
go
INSERT Recursive VALUES (1,'stuff',NULL)
INSERT Recursive VALUES (2,'more stuff',1)
INSERT Recursive VALUES (3,'more stuff',1)
INSERT Recursive VALUES (4,'more stuff',2)
INSERT Recursive VALUES (5,'more stuff',NULL)
INSERT Recursive VALUES (6,'more stuff',3)
go

CREATE PROCEDURE pr_Recursive_Delete
@ID int,
@OrigID int = NULL
AS
SET NOCOUNT ON

DECLARE @OrigID2 int,
@MinID int

IF @OrigID IS NULL
SET @OrigID2 = @ID
ELSE
SET @OrigID2 = @OrigID

IF EXISTS(SELECT * FROM Recursive WHERE ParentID = @ID)
BEGIN
SELECT @MinID = MAX(ID) FROM Recursive WHERE ParentID = @ID
EXEC pr_Recursive_Delete @MinID, @OrigID2
END
ELSE
BEGIN
DELETE Recursive WHERE ID = @ID
IF EXISTS(SELECT * FROM Recursive WHERE ID = @OrigID2)
BEGIN
EXEC pr_Recursive_Delete @OrigID2
END
END
go


EXEC pr_Recursive_Delete 1

SELECT * FROM Recursive

-- Results --

ID F1 ParentID
----------- ---------- -----------
5 more stuff NULL

 

[Garth Wells]

Pretty slick...unfortunately I would have never thought of it.

FROM THE BOL...
Specifies an additional FROM clause. This Transact-SQL extension to DELETE
allows
you to specify data from <table_sources> and delete corresponding rows from the
table
in the first FROM clause.

This extension, specifying a join, can be used instead of a subquery in the
WHERE clause
to identify rows to be removed.