Helen Borrie wrote:

>>CREATE TABLE CATEGORY
>>(
>> CATEGORY_ID INTEGER NOT NULL,
>> PARENT_ID INTEGER,
>> NAME VARCHAR(100),
>> CONSTRAINT CAT_PK PRIMARY KEY (CATEGORY_ID)
>>);
>
> ALTER TABLE CATEGORY ADD CONSTRAINT PARENT_CAT
> FOREIGN KEY (PARENT_ID)
> REFERENCES CATEGORY(CATEGORY_ID)
> ON DELETE SET NULL;
> ;

But doesn't this also allow single-row deletes to remove an item that
has children? What if this is undesirable? How do you make it work only
if the parent's children are also deleted in the same statement?

The best I can think of myself is to construct a query that deletes all
items that don't have children and then repeat that query until all
items are deleted. But it doesn't seem like a very neat solution.

Kjell
--
--------------------------------------
Kjell Rilbe
Adressmarknaden AM AB
E-post: kjell.rilbe@...
Telefon: 08-761 06 55
Mobil: 0733-44 24 64