Mario Frasca wrote:
I'm trying to use default values and autoincrementing primary keys.
[EMAIL PROTECTED]:~$ sqlite3 /data/mariof/test.db_scia.db
SQLite version 3.3.4
Enter ".help" for instructions
sqlite> create table test(f int auto_increment primary key, v int default 0);
The above is not correct syntax for what you want.
sqlite> insert into test (v) values (1);
sqlite> insert into test (v) values (2);
sqlite> insert into test (v) values (1);
sqlite> insert into test (v) values (NULL);
sqlite> select * from test;
|1
|2
|1
|
First, recreating your example:
$ sqlite3
SQLite version 3.3.6
Enter ".help" for instructions
sqlite> create table test(f int auto_increment primary key, v int
default 0);
sqlite> insert into test (v) values (1);
sqlite> insert into test (v) values (2);
sqlite> insert into test (v) values (NULL);
sqlite> select * from test;
|1
|2
|
--------------------------------------------------
Next, showing that f is really not what you want:
sqlite> select oid,f,v from test;
1||1
2||2
3||
sqlite> drop table test;
--------------------------------------------------------------
Next, correcting int to integer (no change.. yet)
sqlite> create table test(f integer auto_increment primary key, v int
default 0)
;
sqlite> insert into test (v) values (1);
sqlite> insert into test (v) values (2);
sqlite> insert into test (v) values (NULL);
sqlite> select * from test;
|1
|2
|
sqlite> drop table test;
------------------------------------------------------
Now, correcting the order:
sqlite> create table test(f integer primary key auto_increment, v int
default 0);
SQL error: near "auto_increment": syntax error
----------------------------------------------------
Now correct the spelling of autoincrement:
sqlite> create table test(f int autoincrement primary key, v int default 0);
SQL error: near "autoincrement": syntax error
-----------------------------------------------------
Now correct the order again:
sqlite> create table test(f int primary key autoincrement, v int default 0);
SQL error: AUTOINCREMENT is only allowed on an INTEGER PRIMARY KEY
----------------------------------------------------------
Finally, get it right:
sqlite> create table test(f integer primary key autoincrement, v int
default 0);
sqlite> insert into test (v) values (1);
sqlite> insert into test (v) values (2);
sqlite> insert into test (v) values (NULL);
sqlite> select * from test;
1|1
2|2
3|
Not sure why the last row is not 3|0.
HTH,
Gerry
