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"
My bet is on the combination of the ON DELETE clauses, but I'm not sure
where it's wrong.
As i said, T_D and T_C are empty anyway at this point so I don't even
understand why they are mentionned in the error message.
If you guys have questions go ahead, I hope this is clearly expressed.
Regards
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
