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 ?