Eurika! I get it!
# sqlite3
SQLite version 3.5.9
Enter ".help" for instructions
sqlite> CREATE TABLE t(i INTEGER PRIMARY KEY, t TEXT);
sqlite> INSERT INTO t(i,t) VALUES ("a", "b");
SQL error: datatype mismatch
sqlite> CREATE TABLE t2(i INTEGER, t TEXT);
sqlite> INSERT INTO t2(i,t) VALUES ("a", "b");
INTEGER PRIMARY KEY is the only type which is checked as a datatype!
Thankyou, thankyou, thankyou!!!!
I'll rethink and come back tomorrow. I'm still sure it's possible to
very simple make BIGINT work as ROWID is 64bit anyways.
In regards to libdbi, i think we are saying if there is one field with
the pk flag set and its INTEGER then that is PRIMARY KEY (ROWID) and
autoincrement.
sqlite> PRAGMA table_info(test);
0|id|INTEGER|0||1
1|int|INTEGER|0||0
2|bigint|BIGINT|0||0
Yes, id is PRIMARY KEY (ROWID)
sqlite> PRAGMA table_info(test);
0|id|INTEGER|0||1
1|int|INTEGER|0||1
2|bigint|BIGINT|0||0
No, id is not PRIMARY KEY (ROWID)
This does mean an inefficient loop though, would be nice if it could
provide something like "is_rowid" column. Although being able to
declare BIGINT PRIMARY KEY would fix all issues.
regards,
Nathan
Quoting "D. Richard Hipp" <d...@hwaci.com>:
>
> On Dec 15, 2008, at 11:23 AM, Thomas Briggs wrote:
>
>> I think Nathan's point is that the integer you get when declaring a
>> column INTEGER PRIMARY KEY can hold a 64-bit value anyway, so why
>> couldn't it simply be declared differently and behave the same?
>
> INTEGER PRIMARY KEY is the exception to the rules for SQLite
> datatypes. Any other column can hold any datatype, regardless of the
> declared datatype. But an INTEGER PRIMARY KEY is different. INTEGER
> PRIMARY KEY is an alias for the underlying rowid. It can only hold an
> integer.
>
> If we allowed any integer-typed column that was a PRIMARY KEY to
> behave this way, then you would suddenly have many expections to the
> rule, rather than just one. And there would be no way to declare a
> column that had integer affinity that was the primary key.
>
> The INTEGER PRIMARY KEY exception was first introduced in SQLite
> version 2.2.0, about 7 years ago (2001-12-20).
>
>
> D. Richard Hipp
> d...@hwaci.com
>
>
>
> _______________________________________________
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
----------------------------------------------------------------
This message was sent using IMP, the Internet Messaging Program.
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
