Quoting Simon Davies <[email protected]>:
CREATE TABLE main ( id INTEGER PRIMARY KEY );
CREATE TABLE tab1 ( id INTEGER REFERENCES main, str VARCHAR(10) );
CREATE TABLE tab2 ( id REFERENCES main, str VARCHAR(10) );
INSERT INTO tab1 VALUES ( 42, 'foo' );
INSERT INTO tab2 VALUES ( 42, 'foo' );
The following two queries return different results:
SELECT * FROM tab1 WHERE id = '42'; -- returns 1 row
SELECT * FROM tab2 WHERE id = '42'; -- returns no rows
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
Hmm... I see what you mean:
Point 3 under 2.1 states that "if no type is specified then the column
has affinity NONE."
However, I find a foreign-key-clause counting as "no type specified"
is at least a bit irritating. After all the type could be inferred
from the reference. :-/
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. :-)
Anyway, thanks for your help, Simon!
kind regards,
Christian
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
