At 11:11 AM 27/02/2005 +0000, you wrote:

>I have a question about deleting all the rows from a table. The table
>represents a hierarchy of categories so that the end result is a tree
>of categories.
>
>When I try to delete all the rows from the table using "delete from
>category" I get a foreign key viloation. I don't believe this should
>happen as the "delete from" should delete all the data in the table
>and should not care about foreign keys within the same table.
>
>I know that MSSQL allows it, and I think Oracle and DB2 also allow it.

..provided you define the appropriate delete behaviour.

>Is there a way around this?

See below.

>Thanks,
>Bryan
>
>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;
;

>INSERT INTO CATEGORY VALUES (1, NULL, 'Category 1');
>INSERT INTO CATEGORY VALUES (2, 1, 'Category 1');
>DELETE FROM CATEGORY;
>
>The "delete from" results in a foreign key violation.

Yup, that's how referential integrity is intended to work.

./hb