I would agree that no warning is needed for for columns that don't state
any affinity, or for a non-affinity FK that refers to some PK with
affinity.
I tend to agree with OP that an explicitly text foreign key referring to
an explicitly int primary key is probably worth a warning (perhaps from
the hypothetical "lint mode" that surfaces now and then), since it's
pretty likely that a user who took the trouble to specify affinities for
both PK and FK probably made a mistake if the types are different. Sure,
some record might override affinity and store 'abc' as its "int" primary
key, but even if your app relies on that behavior, an "int" foreign key
would be harmless for the same reason.
Off topic, I'd love a way to request strong typing for a column (so that
attempts to store 'abc' into an int column would fail). You can emulate
it with a pair of before/update triggers (select raise(...) where
typeof(intcol)!='integer'), but that's clunky. Oh well... in retrospect,
most of the times I've been bitten by type mismatches were probably
either due to this bug or (more likely) due to my not specifying any
affinity at all and then being surprised when 1 != '1'.
Ryan
On 06/03/2013 7:23 AM, Stephen Chrzanowski wrote:
SQLite doesn't care what kind of data type you are using, so, no, it
shouldn't throw an error. The logic of this database engine is that you
will always be comparing apples to apples, regardless if one happens to be
orange.
On Wed, Mar 6, 2013 at 4:50 AM, Tom Matrix <ratomat...@gmail.com> wrote:
Richard Hipp <drh@...> writes:
On Sun, Mar 3, 2013 at 2:48 PM, Tom Matrix <ratomatrix@...> wrote:
I’ve encountered a problem, which is hardly reproducable on arbitrary
databases, therefore I attached one.
A simple, reproducible test case for (what we think is) your problem can
be
seen in this ticket:
www.sqlite.org/src/tktview/fc7bd6358f59b
This bug has been latent in SQLite for almost four years and you are the
first to hit it. Probably this is because not many applications contain
A=B in the WHERE clause where A is a text expression and B is an integer
expression. You can probably work around the problem by changing your
schema so that entries.measurementid is an integer rather than text.
This
does not excuse SQLite: It is still getting the wrong answer and needs
to
be fixed. We are working on a fix now. But a simple change to your
schema
will work around the problem and get you going even before that fix is
available.
Very good example, thank you! It really solved my problem. Nevertheless, I
think
I don't have to mention that entries.measurementid must have been an
integer,
and this bug must be fixed in our schema, too.
My only remaining concern is, however: Should not SQLite give an error (or
at
least a warning) in cases where a foreign key constraint refers to a
different
data type?
Thanks again,
Tamás
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users