Oracle PLSQL Cascade Delete doesn't work?

Question

I have some tables with foreign keys which should be deleted. I Put the "on delete cascade" everywhere I needed it, but when I try to Drop the Table i get following error:

*Cause:    An attempt was made to drop a table with unique or
           primary keys referenced by foreign keys in another table.

This is the Table I want to drop:

DROP TABLE Author;
CREATE TABLE Author (
    id NUMBER(4) NOT NULL,
    first_name VARCHAR2(30) NOT NULL,
    last_name VARCHAR2(30) NOT NULL,
    date_of_birth DATE NOT NULL,
    date_of_death DATE NULL,
    CONSTRAINT Author_PK PRIMARY KEY (id)
);

And this is the Table that is in relation to the Author table:

CREATE TABLE Book (
    id NUMBER(4) NOT NULL,
    author NUMBER(4) NULL,
    title VARCHAR2(30) NOT NULL,
    ISBN VARCHAR2(13) NOT NULL,
    book_language VARCHAR2(2) NOT NULL,
    book_genre VARCHAR2(20) NOT NULL,
    CONSTRAINT Book_PK PRIMARY KEY (id),
    CONSTRAINT Book_Author FOREIGN KEY (author) REFERENCES Author(id) ON DELETE cascade
);

`drop table author cascade constraints` – Apr 24 '20 at 10:15 — , Apr 24 '20 at 10:15
@a_horse_with_no_name Thanks, now it works! – Samuel Apr 24 '20 at 16:55 — Samuel, Apr 24 '20 at 16:55

score 0 · Answer 1 · answered Apr 24 '20 at 10:01

0

DROP is a DDL. It has nothing to do with DELETE (DML) (affected by the way you created the foreign key constraint).

Drop child table first; then drop its parent.

answered Apr 24 '20 at 10:01

Littlefoot

131,892
15
35
57

score 0 · Answer 2 · answered Apr 24 '20 at 16:57

0

I resolved it now with help from @a_horse_with_no_name If you have the same issue, just write

drop table table_name cascade constraints;

answered Apr 24 '20 at 16:57

Samuel

13
3

Oracle PLSQL Cascade Delete doesn't work?

2 Answers2