I thought actually specifying a ROWID would be harmless..... So I tried
your idea, same results:
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)
...> );
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|3
sqlite>
Notice I still have elements 1,2 and 3 in the end where I want to have
elements 1, 2 and 4.
Clay Dowling wrote:
ROWID is a reserved word. Each row has one and you don't need to specify
it. In fact you probably shouldn't, since it seems to be causing you
problems.
If you want the column to be explicitly declared in your table, call it
something else like id. If you don't want to do that just use the keyword
ROWID in your queries, without declaring it as a column, and everything
wil be fine.
Clay
Dixon Hutchinson said:
I am having a problem with default behavior of ROWID not
auto-incrementing.
I use the following to create my table:
CREATE TABLE abc
{
c TEXT,
p INTEGER,
t TEXT,
ROWID INTEGER PRIMARY KEY,
UNIQUE(p,c)
);
When I insert into the table using just c, p and t, I see that ROWID is
assigned auto-incrementing values, as I would expect. If I delete an
entry (and in my test it was the last entry made), and then reinsert the
same c and p with a different t, I notice that ROWID gets the value of
the row I just deleted. I was hoping to get the behavior specified at
http://www.sqlite.org/autoinc.html. where the AUTOINCREMENT keyword is
not used. I have other elements in the table, but I left them out for
this illustration. I want to be able to do relatively quick lookups of
data by either p, c, or ROWID. But I need ROWID to auto increment until
the largest 64-bit integer value is used and then follow the random
search algorithm described in the previously mention web page. I
suspect my problem is that specifying "UNIQUE(p,c)" has obviated the
table's need for a unique ROWID.
I'm looking for a recommendation on how to specify the table such that
ROWID gets the desired behavior and I can still do quick lookups by
ROWID, p, or c.
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
