title: SQLite: Autoincrement and insert or ignore will produce unused Autoincrement Keys
body: <p>I'm using a table <code>Mail</code> 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 <code>INSERT OR IGNORE</code> 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 <code>SELECT ...</code> and do an <code>INSERT</code> if needed.</p>
  
  <p>For every <code>INSERT OR IGNORE</code> 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. </p>
  
  <p>I also tried <code>INSERT OR REPLACE</code>, but this will increment the Id of the dataset on every run of the command, so this is not a solution at all.</p>
  
  <p><strong>Is there a way to prevent this increase of auto-increment key on every <code>INSERT OR IGNORE</code>?</strong> </p>
  
  Table Mail Example (replaced with pseudo Addresses)
  
  <pre><code>    mIdMail   mMail
      "1"       ""
      "7"       "mail1@example.com"
      "15"      "mail2@example.com"
      "17"      "mail3@example.com"
      "19"      "mail4@example.com"
      "23"      "mail5@example.com"
      ...
  </code></pre>
  
  Insert Query (Using Java Lib: org.apache.commons.dbutils)
  
  <pre><code>    INSERT OR IGNORE 
      INTO MAIL 
      ( mMail  ) 
      VALUES ( ? );
  </code></pre>
  
  Table Definition
  
  <pre><code>    CREATE TABLE IF NOT EXISTS MAIL (
         mIdMail          INTEGER PRIMARY KEY AUTOINCREMENT, 
         mMail            CHAR(90) UNIQUE 
      ); 
  </code></pre>