Hi Dan,
Yeah, I took a closer look to my script today (fresh mind), and indeed
there was a typo. I've been on it so closely yesterday that I couldn't see
this huge error.
I'm sorry, to all the mailing list, for such an useless post...
Anyway, thanks again Dan, have a nice day guys
2017-07-26 13:04 GMT+02:00 Dan Kennedy <danielk1...@gmail.com>:
> 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
>
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
