I believe a check constraint with an appropriate typeof comparison has
been suggested for this usage.
On 3/6/2013 6:29 AM, Ryan Johnson wrote:
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
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
