On 07/25/2017 08:42 PM, Bubu Bubu wrote:
I'm having trouble with foreign key constraints et cascade clauses and I
don't really know where could be the problem.
I'm working on sqlite version 3.8.11.1 (I can't update it, work
restriction), but I checked and such functionnalities are enabled (correct
me if I'm wrong).
I've renamed everything, hope this will be readable :
PRAGMA foreign_keys = true;
CREATE TABLE T_A (
id_t_a INTEGER PRIMARY KEY,
label TEXT
);
CREATE TABLE T_B (
id_t_b INTEGER PRIMARY KEY,
fk_t_a_id INTEGER NOT NULL,
label TEXT,
FOREIGN KEY(fk_t_a_id) REFERENCES T_A(id_t_a) ON DELETE CASCADE ON UPDATE
CASCADE
);
CREATE TABLE T_C (
id_t_c INTEGER PRIMARY KEY,
fk_t_a_id INTEGER NOT NULL,
fk_t_b_id INTEGER,
FOREIGN KEY(fk_t_a_id) REFERENCES T_A(id_t_a) ON DELETE CASCADE ON UPDATE
CASCADE,
FOREIGN_KEY(fk_t_b_id) REFERENCES T_B(id_t_b) ON DELETE SET NULL ON UPDATE
CASCADE
);
CREATE TABLE T_D (
id_t_d INTEGER PRIMARY KEY,
fk_t_c_id INTEGER NOT NULL,
fk_t_b_id INTEGER,
FOREIGN KEY(fk_t_c_id) REFERENCES T_C(id_t_c) ON DELETE CASCADE ON UPDATE
CASCADE,
FOREIGN KEY(fk_t_b_id) REFERENCES T_B(id_t_b) ON DELETE SET NULL ON UPDATE
CASCADE
);
And then I'm doing this :
INSERT INTO T_A(label) VALUES("A1"); // id_t_a -> 1
INSERT INTO T_B(label, fk_t_a_id) VALUES("B1", 1);
INSERT INTO T_B(label, fk_t_a_id) VALUES("B2", 1);
At this point, tables T_D and T_C are and will remain empty, then I do :
DELETE FROM T_A WHERE id_t_a = 1;
I get this error :
foreign key mismatch - "T_D" referencing "T_C"
This script runs without error in the shell.
The error indicates that there is no index t_c(id_t_c). Which is not the
case in the above - it's an INTEGER PRIMARY KEY. Maybe there is a typo
in the actual db schema that you fixed when transcribing. Or it could be
a bug in some older version of SQLite that has been fixed since.
Try opening the db with the shell tool and running ".lint fkey" - it
will tell you if it thinks there is an index missing. Also check the
entry for table T_C in the sqlite_master table - there might be a typo
in the declaration of column id_t_c.
Dan.
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
