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]
-----------------------------------------------------------------------------