2

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,

1
  • The shown code works for me. Are you sure you have data that should be deleted? Mar 2, 2015 at 7:23

1 Answer 1

4

Aftee searching the Ideone FAQ, it seems that it uses Sqlite3 for the SQL language.

Sqlite needs PRAGMA foreign_keys = ON; to enable foreign keys checks. As they say in their docs: SQLite Foreign Key Support :

Assuming the library is compiled with foreign key constraints enabled, it must still be enabled by the application at runtime, using the PRAGMA foreign_keys command. For example:

sqlite> PRAGMA foreign_keys = ON;

Tested at: Ideone

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.