SQLite: ON DELETE CASCADE is not working right

Question

I tried very often and looked also on the other forum posts, but they didn't help me. This is my problem: I create Tables like this:

CREATE TABLE IF NOT EXISTS Parent
    (Id INTEGER PRIMARY KEY,
     Name STRING);
CREATE TABLE IF NOT EXISTS Child
    (Id INTEGER,
     Name STRING,
     ParentId INTEGER,
     PRIMARY KEY (Id, ParentId)
     FOREIGN KEY (ParentId) REFERENCES Parent (ParentId) ON DELETE CASCADE);

After that I insert this:

INSERT INTO Parent (Id, Name) VALUES (1, 'Michael');
INSERT INTO Child (Id, Name, ParentId) VALUES (1, 'Paul', 1);

Alright! We have now in each table a row with a parent and a child. Now I want to delete the parent:

DELETE FROM Parent WHERE Id = 1;

What is happening? The row in the table 'Parent' is not there anymore, but the row in the table 'Child' is still there, even if I said: 'ON DELETE CASCADE'.

Answer 1

Foreign key constraints are not active by default. You must enable them with the following command, once per database session :

PRAGMA foreign_keys = ON;

From Official Doc :

2. Enabling 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;

Foreign key constraints are disabled by default (for backwards compatibility), so must be enabled separately for each database connection.