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.

Is there a way around this?

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)
;

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.