Foreign key mismatch with ON DELETE clauses

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
1 message Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Foreign key mismatch with ON DELETE clauses

Bubu Bubu
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Loading...