At 12:35 PM 27/02/2005 +0100, you wrote:

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

ON DELETE SET NULL (or any referential action, indeed) applies to the row
"in focus". The rule that is applies is "If my parent gets deleted, set my
parent ID to null".

Another rule that can be applied is ON DELETE CASCADE. This rule says "If
my parent gets deleted, then delete me."

The third action (the default) is ON DELETE NO ACTION. This means that the
referential rule gets applied without moderation and an RI exception will
be thrown if there is a request to delete the parent.

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

For deleting all of the rows, another (less arduous) way is simply to drop
the FK constraint; commit; then proceed to delete the rows.

./heLen