Quoting Igor Tandetnik <itandet...@mvps.org>:
stahl...@dbs.uni-hannover.de wrote:
Consider these two tables:
CREATE TABLE tab1 (x INTEGER PRIMARY KEY);
CREATE TABLE tab2 (x PRIMARY KEY REFERENCES tab1);
Assuming they contain the same rows, I expect any query against 'tab1' to
return the same rows as against 'tab2'.
Why would you expect that? You keep saying this, but I don't
understand the basis of your expectations - unless it's just the
good old wishful thinking.
I base my expectations on two things.
First (and probably more importantly):
Column 'x' of table 'tab2' is defined as a foreign key referencing the primary
key 'x' of table 'tab1'.
I expect that a foreign key column is in no way different than the primary key
column it points to - unless I explicitly specify it to be different.
(This has nothing to do with SQLite, but with the definition of functional
dependencies in relational databases: A foreign key *is* a primary key from
a foreign table.)
So this means the schema definitions of 'tab1' and 'tab2' must effectively be
the same and thus my assumption above should hold.
As SQLite let me leave out the column type, I thought "Great! SQLite is smart
enough to infer the column type for FKs! Just like Oracle!".
Which leads to my second reason: The Oracle DBMS does what I expect.
So yes, I admit that this *is* wishful thinking on my part.
I do think, however, that my wishes are reasonable.
(I don't know what the SQL Standard has to say about this situation
I'm pretty sure the second CREATE TABLE statement is syntactically
invalid, per the standard. The column type is mandatory, if I recall
correctly.
Ah! I think that explains the situation somewhat:
SQLite and Oracle expand the standard in the same way by allowing to leave
out the column type for FKs.
Oracle infers the column type from the PK, but SQLite always uses the default
column type BLOB (or 'NONE'..?).
(PostgreSQL rejects the definition for 'tab2' as mentioned earlier.)
SQLite's behavior makes sense, because *every* column type may be left out.
However, I think that in the case of FK-definitions (like the one in 'tab2')
assigning the default type is not the right thing to do.
but I do think that most users would share my expectation.)
I'm not sure what you base this belief on, either. I don't seem to
see your argument enjoying widespread support on this thread.
Yes, you are obviously right there.
I discussed the problem earlier with colleagues (mostly Oracle users), but
obviously this list is a different world. :-)
However with SQLite there are queries which yield incoherent results:
Define "incoherent". As far as I can tell, you use this term to mean
"results you personally dislike". The results SQLite produces are in
agreement - in other words, in coherence - with the product
documentation.
I just meant 'incoherent' wrt. "The same query returns different results for
the same data." as per my example.
You seem to hold this truth to be self-evident, but I honestly don't
understand why. Could you explain your reasoning to me?
I hope the explanations above make my point at least understandable?
Also I don't really care *how* this is fixed
As best I can tell, so far there's no agreement that it's broken, so
discussing how to fix it seems a bit premature.
Agreed there's no agreement. :-)
As I said earlier: I'm fine now that I know that I should explicitly specify
the column type (even better if that's standard compliant!).
I still think that SQLite could be made better by inferring FK column types.
But I won't pursue this matter any further if there is no agreement on this.
Thank you for your answer!
Kind regards,
Christian
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
