mardi 3 mars 2015

ON DELETE CASCADE not deleting entry for foreign key


I am running the below query in ideone



CREATE TABLE AUTHOR (
AUTHOR_ID NUMBER(5) PRIMARY KEY,
AUTHOR_NAME VARCHAR2(18)
);

INSERT INTO AUTHOR (AUTHOR_ID, AUTHOR_NAME) VALUES (1234, 'MC GRAW HILL');
INSERT INTO AUTHOR (AUTHOR_ID, AUTHOR_NAME) VALUES (1235, 'J K ROLLING');
INSERT INTO AUTHOR (AUTHOR_ID, AUTHOR_NAME) VALUES (1236, 'WILLAM');
INSERT INTO AUTHOR (AUTHOR_ID, AUTHOR_NAME) VALUES (1237, 'SHAKESPEAR');

---------------------------------
CREATE TABLE BOOK
(
BOOK_ID NUMBER(5) PRIMARY KEY,
BOOK_NAME VARCHAR2(15),
AUTHOR_ID NUMBER(5),
CONSTRAINT author_fk
FOREIGN KEY (AUTHOR_ID) REFERENCES AUTHOR(AUTHOR_ID)
ON DELETE CASCADE
);

INSERT INTO BOOK (BOOK_ID, BOOK_NAME, AUTHOR_ID)
VALUES (10001, 'SOFTWARE DEVELOPEMENT', 1234);
INSERT INTO BOOK (BOOK_ID, BOOK_NAME, AUTHOR_ID)
VALUES (10002, 'HARRY POTTER', 1235);
INSERT INTO BOOK (BOOK_ID, BOOK_NAME, AUTHOR_ID)
VALUES (10003, 'TO BE OR NOT TO BE', 1237);
INSERT INTO BOOK (BOOK_ID, BOOK_NAME, AUTHOR_ID)
VALUES (10004, 'PROJECT MANAGEMENT', 1234);


After creating these entries, when I try to delete the entries in AUTHOR Table by using following command:



DELETE FROM AUTHOR WHERE AUTHOR_ID = 1234;


Select * from book ;

10001|SOFTWARE DEVELOPEMENT|1234
10002|HARRY POTTER |1235
10003|TO BE OR NOT TO BE |1237
10004|PROJECT MANAGEMENT |1234


and



Select * from author ;

1235|J K ROLLING
1236|WILLAM
1237|SHAKESPEAR


the entry from parent table is deleted but their is no delete cascade effect in child table (BOOK table). Can someone help me what I am doing wrong.


Thanks,





Aucun commentaire:

Enregistrer un commentaire