SQLite: Autoincrement and insert or ignore will produce unused Autoincrement Keys

user_5330578 0
Viewed 2383

I'm using a table Mail with auto-increment Id and Mail Address. The table is used in 4 other tables and it is mainly used to save storage (String is only saved once and not 4 times). I'm using INSERT OR IGNORE to just blindly add the mail addresses to the table and if it exists ignore the update. This approach is MUCH faster than checking the existence with SELECT ... and do an INSERT if needed.

For every INSERT OR IGNORE the auto-increment, no matter if ignored or done the auto-increment Id is incremented. I one run I have approx. 500k data sets to proceed. So after every run the the last auto-increment key is incremented by 500k. I know there are 2^63-1 possible keys, so a long time to use them all up.

I also tried INSERT OR REPLACE, but this will increment the Id of the dataset on every run of the command, so this is not a solution at all.

Is there a way to prevent this increase of auto-increment key on every INSERT OR IGNORE?

Table Mail Example (replaced with pseudo Addresses) 
    mIdMail   mMail
    "1"       ""
    "7"       "mail1@example.com"
    "15"      "mail2@example.com"
    "17"      "mail3@example.com"
    "19"      "mail4@example.com"
    "23"      "mail5@example.com"
    ...
Insert Query (Using Java Lib: org.apache.commons.dbutils) 
    INSERT OR IGNORE 
    INTO MAIL 
    ( mMail  ) 
    VALUES ( ? );
Table Definition 
    CREATE TABLE IF NOT EXISTS MAIL (
       mIdMail          INTEGER PRIMARY KEY AUTOINCREMENT, 
       mMail            CHAR(90) UNIQUE 
    );
sqlite sql
2 Answers
user_116540

Auto-increment keys behave the way they do specifically because the database guarantees their behavior -- regardless of concurrent transactions and transaction failures.

Auto-increment keys have two guarantees:

  • They are increasing, so later inserts have larger values than earlier ones.
  • They are guaranteed to be unique.

The mechanism for allocating the keys does not guarantee no gaps. Why not? Because no-gaps would incur a lot more overhead on the database. Basically, each transaction on the table would need to be completely serialized (that is completed and committed) before the next one can take place. Generally, that is a really bad idea from a performance perspective.

Unfortunately, SQLite doesn't have the simplest solution, which is simply to call row_number() on the auto-incremented keys. You could try to implement a gapless auto-increment using triggers, significantly slowing down your application.

My real suggestion is simply to live with the gaps. Accept them. Surrender. That is how the built-in method works, and for good reason. Now design the rest of the database/application keeping this in mind.

user_11440350
Related