Am 12.07.2006 um 11:16 schrieb RohitPatel9999:
INT and INTEGER behaves differently (for PRIMARY KEY) !!!
(SQLite 3.3.4)
create table t1 ( id INTEGER PRIMARY KEY );
create table t2 ( id INT PRIMARY KEY );
insert into t1 values(NULL);
insert into t1 values(NULL);
insert into t2 values(NULL);
insert into t2 values(NULL);
/* insert into t1 values('a'); fails */
insert into t2 values('a');
select rowid, id, (id IS NULL) from t1;
select rowid, id, (id IS NULL) from t2;
My observations:
if null is inserted for id column in table t1, it contains 1, 2
if null is inserted for id column in table t2, it contains null values
if text is inserted for id column in table t1, it fails
if text is inserted for id column in table t2, it allows to insert
text
Is it advisable not to use INT but use INTEGER for PRIMARY KEY ??
According to <http://www.sqlite.org/version3.html> (section "64-bit
ROWIDs"):
If the table defines a column with the type "INTEGER PRIMARY KEY"
then that column becomes an alias for the rowid.
Apparently, the column becomes the rowID if and only if the type
matches the above exactly, in which case it will get the additional
constraint of only allowing numeric values and the column will have
an auto-incrementing default value.
For "INT PRIMARY KEY" a separate (invisible) rowID column is added
and the id column remains without additional constraints, i.e. you
can add data of any type as with any other column...
This is still arguably an inconsistency though, but the above might
explain the difference...
HTH,
</jum>
