The ids of a table in my sqlite database have been reset

Question

I have an iPhone app and one of my users found a really strange problem with my application. I can't reproduce the problem and I can't figure out why it's happening. Maybe you can?

In Sqlite I have a table with about 1000 rows, each with a unique id. But for some reason the id of that table has restarted, before it was around 1000 but now it's restarted from 80 something. So everytime the user inserts a new row the new assigned id starts around 80 something and I get two duplicates ids that should be unique and yeah you can understand the problem. I have looked at all queries that does anything to that table and none of them could have done this. I always relay on the built in mechanism where the ids are assigned automatically.

Have you seen anything like this?

The schema of the table looks like this:

CREATE TABLE mytable(
  id INTEGER PRIMARY KEY
);

As you can see I don't use AUTOINCREMENT. But from what I understand even if the user deletes a row with id 80, it is ok to give a new inserted row id 80 but not like it works now where the database just keeps incrementing the ids even if I have already have rows with the same id. Shouldn't it work like this:

HIGHEST ROWID IS 1000, ALL IDS FROM 0-1000 ARE TAKEN
USER DELETES ROW WITH ID 80
INSERT A NEW ROW
    THE ID OF THE INSERTED ROW MIGHT NOW BE 80
    SETS THE ID OF THE INSERTED ROW TO 80
INSERT A NEW ROW
    THE ID OF THE INSERTED ROW CAN NOT BE 81 AS THIS IS ALREADY TAKEN
    SETS THE ID OF THE INSERTED ROW TO 1001

Isn't that how it should work?

Answer 1

Did you declare your id column as a(n autoincrementing) primary key?

CREATE TABLE mytable(
  id INTEGER PRIMARY KEY AUTOINCREMENT
);

By adding the autoincrement keyword you ensure that all keys generated will be unique over the lifetime of your table. By omitting it, the keys will still be unique, but it may generate keys that have already been used by other, deleted entries. Note that using autoincrement can cause problems, so read up on it before you add it.

Edit This is a bit of a long-shot, but sqlite only supports one primary key per table. If you have more than one primary key declared, you need to declare all but the one you actually want to use as a primary key as "unique". Hence

CREATE TABLE mytable(
  id INTEGER PRIMARY KEY, 
  otherId INTEGER UNIQUE
);

Answer 2

Hard to say without the code and schema, but my instinct is that this unique ID is not defined as either unique nor primary key, which they should.

How do you make sure (in theory) id's are unique? What is your insert query like?