0

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,

  • The shown code works for me. Are you sure you have data that should be deleted? – Allan S. Hansen Mar 2 '15 at 7:23
  • 1
    Which RDBMS are you using? – Philᵀᴹ Mar 2 '15 at 9:03
  • VARCHAR2 suggests Oracle. @sdn_worls, provide the sripts/statements that show the issue (Inserts, Deletes and then Select with the output) . – ypercubeᵀᴹ Mar 2 '15 at 12:44
  • If you're using Oracle and SQL Developer, did you make sure to commit; and refresh? – Connor Mar 3 '15 at 2:44
  • Ideone pages indicate that they use Falcon for the SQL language. Falcon is an MySQL storage engine which supported Foreign Keys but its development was abandoned. As far as I can understand from this code run, it does not seem to support foreign keys at all. – ypercubeᵀᴹ Mar 3 '15 at 10:42
1

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, privacy policy and cookie policy

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