|
Posted by Robert Ludig on 05/16/06 15:34
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 ?
Navigation:
[Reply to this message]
|