But "AUTOINCREMENT" has slightly different behavior that what I desire.
The difference is what happens when the ROWID reaches the "largest
possible integer". If AUTOINCREMENT is specified, then the next insert
after "largest possible integer" is reach will fail, regardless of the
availability of empty rows that resulted from deletes. The behavior I
desire is that after "largest possible integer" is reach, "/the database
engine starts picking candidate ROWIDs at random until it finds one that
is not previously used/".
Mario Frasca wrote:
Dixon Hutchinson wrote:
H:\b>sqlite3.exe t.dat
SQLite version 3.3.7
Enter ".help" for instructions
sqlite> CREATE TABLE abc
...> (
...> c TEXT,
...> p INTEGER,
...> t TEXT,
...> masked INTEGER PRIMARY KEY,
...> UNIQUE(p,c)
...> );
[...]
Notice I still have elements 1,2 and 3 in the end where I want to
have elements 1, 2 and 4.
which is the reason why sqlite has autoincrement...
[EMAIL PROTECTED]:~$ /usr/bin/sqlite3
SQLite version 3.3.4
Enter ".help" for instructions
sqlite> CREATE TABLE abc ( c TEXT,p INTEGER, t TEXT, masked INTEGER
PRIMARY KEY AUTOINCREMENT, UNIQUE(p,c));
sqlite> INSERT INTO abc(c,p,t) VALUES('t1', 24, 't2');
sqlite> INSERT INTO abc(c,p,t) VALUES('t3', 25, 't4');
sqlite> INSERT INTO abc(c,p,t) VALUES('t5', 26, 't6');
sqlite> SELECT * FROM abc;
t1|24|t2|1
t3|25|t4|2
t5|26|t6|3
sqlite> DELETE FROM abc WHERE ROWID='3';
sqlite> INSERT INTO abc(c,p,t) VALUES('t5', 26, 't8');
sqlite> SELECT * FROM abc;
t1|24|t2|1
t3|25|t4|2
t5|26|t8|4
sqlite>
works also if you write "rowid" instead of "masked"
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------