SQLite conundrum

This site is best viewed in a modern browser with JavaScript enabled.

root

I have a little SQLite database on a website, when I set up the tables using the CREATE directive, the ENTRY_ID was set up with the following.

CREATE TABLE visitors(ENTRY_ID INTEGER PRIMARY KEY AUTOINCREMENT, REQUEST_TIME INTEGER, USER_IP... ... ...

However... the table is not "Auto Incrementing" and the ENTRY_ID fields are empty...

My query is how is it best to move forward with altering the primary key to actually auto increment keeping in mind that the table has nearly 34,000 entries in it.

epricity

That looks about right.
Try "INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL"
If that doesn't work, it's probably related to the way you're inserting. Every time I've ever had a problem like this with SQLite, it's been on the insert side. Make sure you're not inserting a null value into the ENTRY_ID field when you're dropping information into the table.

root

I have this

INSERT INTO visitors(REQUEST_TIME,REMOTE_ADDR, ...

So it should autoincrement when a data set is pushed in to the table.

Question is that now the table exists, any idea on how to make the table autoincrement with the proper int key value

epricity

Does anything happen when you try to pull a field called row_id?
Some versions of SQLite will create an extra column naturally.

My gut feeling is that it's not auto incrementing, because the field can take nulls.
You should be able to alter table, add NOT NULL to the field properties, which should fix your problem.

root

I get "No Results" issued by my script.

SQLite seems to be a fickle beast, handy but required more coaxing than MySQL which I would have used but I needed this widget to be independent of reliance of MySQL hence the use of SQLite.

After reading the SQLite ALTER TABLE directive, I think it would be easier to create a completely new table and copy across the entries one by one with the oldest first.

Steve_Smith

Hi..Data (illustrative) in SQLite3. Have a few million rows, actually.

a, aardvark
a, anvil
a, apple
a, automaton
b, apple
b, peanut
b, persimmon
b, walnut
Key for my data is actually multiple columns but here I've just used the first column.

So, I'd like to find all the keys that are associated with apple, then use that to query all the other values associated with those keys.