On 08/11/2012 8:04 AM, stahl...@dbs.uni-hannover.de wrote:
Quoting Ryan Johnson <ryan.john...@cs.utoronto.ca>:
On 07/11/2012 7:58 PM, Simon Davies wrote:
On 7 November 2012 20:36, <stahl...@dbs.uni-hannover.de> wrote:
[...]
I think this is the documented behaviour:
http://www.sqlite.org/datatype3.html
tab1.id has integer affinity, and '42' is coerced to integer
tab2.id has none affinity, and '42' is not coerced
[...]
Also, ironically, the documentation claims that "The dynamic type
system of
SQLite is backwards compatible with the more common static type
systems of
other database engines...".
I just checked the example with the Oracle and PostgreSQL instances
I have
at hand here:
Oracle does what I think is correct and returns a row in both cases.
PostgreSQL does not allow the "id REFERENCES" construction at all and
requires a data type even for foreign keys.
So in this case SQLite is incompatible with two major DBMSes. :-)
Which from what you have said, are also incompatible with each other!
Man with sharp stick has point.
Okay, but at least Oracle and PostgreSQL don't claim to do their
auto-coerce-voodoo because of compatibility with other database
engines. :-]
To be fair, though, I have been bitten numerous times by exactly this
same scenario, where foreign key joins fail because the key types
somehow end up differing and don't coerce automatically. Very easy to
forget, or to accidentally let a string slip in where an int was
intended (say, by loading from csv). Also hard to diagnose.
Interesting... so I'm not the only one bitten by this.
Not sure the best way to "fix" the problem [1], but it might be the
single most surprising aspect of using sqlite3 in my experience.
[1] especially since there's probably a customer out there somewhere
whose app actually depends on foreign key join columns having
different types and not matching '24' with 24.
There are actually users *relying* on this incoherent behaviour?
Granted, I don't have insight in the full consequences of this problem,
but I find that hard to believe: This would clearly be bad database
design.
Agree, but badly-designed software has an annoying habit of showing up
in production and then yelling loudly when you fix the bug it depends on.
If this is actually a concern, then I think the best way of fixing
would be
to do what PostgreSQL does: Make '[column-name] REFERENCES...' a
syntax error
and explicitly require a datatype in this case.
I actually liked your idea of making the FK field inherit the type of
the PK field it references.
Meanwhile, you might want to tell sqlite to enforce those foreign key
constraints you so carefully specified:
http://www.sqlite.org/foreignkeys.html#fk_enable [1].
Then, the only way to insert '24' into the FK table and have it mismatch
the 24 in your PK table is if your PK table has '24' in it as well. But
that would clearly be bad database design :P
Ryan
[1] I always forget that check's off by default... somehow I doubt it
would catch problems with previously inserted keys, but you could fix
them in one fell swoop with: update $fktable set $fk=cast($fk as int)
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
