SQLite : On cascade delete doesn't works

278 views Asked by At

I want to use foreign key with SQLite (version > 3.8).

I create 3 bases :

PRAGMA foreign_keys = ON;
CREATE TABLE `child1` (`child1_id` INTEGER PRIMARY KEY AUTOINCREMENT, `address` VARCHAR);

CREATE TABLE `child2` (`child2_id` INTEGER PRIMARY KEY AUTOINCREMENT, `country` VARCHAR);

CREATE TABLE `parent` (`parent_id` INTEGER PRIMARY KEY AUTOINCREMENT, 
    `name` VARCHAR, 
    `child1_fk` integer references child1 (child1_id) on delete cascade, 
    `child2_fk` integer references child2 (child2_id) on delete cascade);

Then inject some data

PRAGMA foreign_keys = ON;
INSERT INTO child1(address) VALUES('Street 1');
INSERT INTO child1(address) VALUES('Street 2');
INSERT INTO child2(country) VALUES('France');
INSERT INTO child2(country) VALUES('England');
INSERT INTO parent(name, child1_fk, child2_fk) VALUES('Smith', 1, 1);
INSERT INTO parent(name, child1_fk, child2_fk) VALUES('Dupond', 2, 2);

Then, I want to delete a parent and I expect these childs are deleted:

PRAGMA foreign_keys = ON;
DELETE FROM parent WHERE parent_id=1;

But child1 and child 2 with id 1 isn't deleted... do you know why ?

0

There are 0 answers