This is just a request-for-enhancement bug report, I've went to the
trouble or reproducing this problem in a simple test case and while
I probably wont be able to immediately benefit from an upstream fix
for this, I hope that this bug report will be perceived as helpful
and entered into your upstream bug tracking system (which seems to
not be public at this time).
This week I ran into a bug in my ~700 line complex schema which was
very hard to find.
After trying many things, including stepping through the
locateFKeyIndex() function issuing the not-so-informative
message "foreign key mismatch", I finally found that the
error was coming from another table with an incorrectly defined
foreign key.
In this case, the FK was defined to refer to a non-unique column
in the parent table.
Suggestions to fix error reporting:
o When foreign keys are enabled at CREATE TABLE time, it would
be very helpful at this point to issue an error if a foreign
key is declared which refers to a non-unique column (or compound
FK referring to a non-unique set of keys in the parent table).
o Alternatively, at least the error message issued from
locateFKeyIndex() could be improved.
I could almost contribute a patch for this but it would require
I spend a hand full of hours understanding the Table & FKey
structures in SQLite, I would suggest something to the effect of:
sprintf (message,
"Foreign key for column %s on table %s refers to"
"non-unique column %s in parent table %s",
column, table, parent_column, parent_table);
And something a little more involved to construct a proper message
for a similar error when it occurs on a compound FK.
Either of these options, preferably both, would greatly improve
usability of SQLite by saving people time debugging the schemas
they create, and I'm sure that the effort required to enhance the
error messages would cost very little effort to those who are
already committers to the SQLite codebase.
I've attached here a simplified test case from my schema, the bug in
the schema occurs on the 'event_participant_activity' table, however
the problem occurs when trying to delete rows from the
'event_participant' table (so it was difficult without any reliable
error reporting to really find where the problem was coming from).
Running the attached script should illustrate the issue, it also
includes a fixed version of the schema (which defines a compound
foreign key instead and fixes the problem so it refers to the correct
and unique data).
This is the first time I've reported any bug on SQLite and again,
I hope that you maintainers perceive this as a helpful thing, I am
not trying to lay blame here, I would consider this an enhancement
class bug in any project that I maintain and would happily record
it to our bug trackers, so I hope you see this the way it was intended.
Best Regards,
-Tristan
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
