create table with recursive relationship

SQL Apprentice Question
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 ?



Celko Answers

>> 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.