Quoting Ryan Johnson <ryan.john...@cs.utoronto.ca>:
On 08/11/2012 8:04 AM, 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
[...]
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.
So true... :-(
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.
I think that this would be the best solution.
But inferring the FK's type from the referenced PK would cause applications
which rely on the FK's type affinity being 'none' to be broken, no?
With a change of syntax on the other hand, the break would at least be clearly
visible:
Existing databases would continue to behave as is.
But statements in the form of 'CREATE TABLE ( ... [column-name]
REFERENCES... )'
which used to be accepted, would now be rejected.
Screaming users could then be informed that a proper datatype must be chosen
for [column-name] -- 'BLOB' if the user actually relies on the foreign key
to have type affinity 'none'.
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
Hehe! You caught me... :-D
(I actually tripped over this myself as I tried to feed my example to Oracle!)
[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)
Thanks! I forget to turn on the check too... I guess I'm somewhat spoiled
by "non-lite" DBMSes. :-)
Maybe the 'foreign-keys' pragma could also turn on the FK-type inheritance.
This seems reasonable and might mitigate the badly-designed-software-yelling
somewhat.
best regards,
Christian
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
