I've spent most of a day trying to figure out what was causing a "foreign key mismatch" error in a truncate-optimized DELETE query. I searched the list and didn't find anything similar to what I've found, nor does there appear to be a bug slip about this either. I'm using version 3.7.4.
First the details. There are three tables: T1, T2, T3. Table T3 has a foreign key reference to the primary key of parent table T2, and T2 has a foreign key reference to the primary key of T1. All foreign key references are set to ON DELETE CASCADE. Now when I delete data from T2, I expect that delete to cascade into T3, but it was causing a "foreign key mismatch" error. Odd, as I think anyone would expect the delete to work properly and cascade into T3. However, if I remove the foreign key reference of T2 to T1, meaning that T2 is now no longer a child table of T1, the delete successfully cascades into T3 without error and the data is deleted as expected from both tables. So it appears that the only way a DELETE statement will cascade to child tables (T3) without throwing a "foreign key mismatch" error is if the parent table from which the data is being initially deleted (T2) is not a child table itself. This seems contrary to how that is expected to work, in my opinion, and I don't believe I've seen this kind of limitation in other database engines with which I've worked, nor does it appear to be documented anywhere. I already have a workaround in mind to get around this limitation. I do believe this to be a bug, however. Kenneth _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
