create table with recursive relationship

I am fairly new to SQL and I am currently trying to create
a SQL table (using Microsoft SQL) 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 stored
procedures or something ?You can get away with the first approach. However, you may not use ON
DELETE CASCADE. Rather, you are looking at a trigger that can manage this.

--
Tom

----------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
..
"Robert Ludig" <schwertfischtrombose@.gmx.de> wrote in message
news:1147782885.171670.231170@.i40g2000cwc.googlegr oups.com...
I am fairly new to SQL and I am currently trying to create
a SQL table (using Microsoft SQL) 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 stored
procedures or something ?|||On 16 May 2006 05:34:45 -0700, Robert Ludig wrote:

>I am fairly new to SQL and I am currently trying to create
>a SQL table (using Microsoft SQL) 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:
(snip)

Hi Robert,

I agree with Tom that the first approach is better than the first. But
there are also some radically different ways to store a recursive
relationship or hierarchy. One of the more popular variants is the
nested set model. It's not nearly as intuitive as the model you are
proposing, but it performs far superior in some scenario's.

Google for "Nested Set Model" if you want to know the details.

--
Hugo Kornelis, SQL Server MVP|||>> How is this "standard-problem" usually solved in sql? <<

Get a copy of TREES & HIERARCHIES IN SQL for several ways to model this
kind of problem.

>> Or do I inned to implement the recursive deletion myself using stored
procedures or something ? <<

No need for recursive procedural code if you use the nested sets model.
Younger programmers who learned HTML, XML, etc. find it to be
intuitive. Older programmers who grew up with pointer chains need to
adjust their mind-set.