1

I use 'INSERT OR IGNORE INTO' in my main table which works fine but leaves empty primary key IDs when records are ignored. This leads to big jumps in my primary key numbering because I re-scan part of the same input data every time. Can I avoid this and re-set the ID to the last used value before next call?

thanks

2 Answers 2

0

Apparently, leaving out the autoincrement from the key definition does the job. The key still autoincrements itself but not when IGNORE is triggered. I found this solution here: https://stackoverflow.com/questions/47120708/sqlite-autoincrement-and-insert-or-ignore-will-produce-unused-autoincrement-key

0

Not really an answre to the question, but an "are you sure you need to?":

This leads to big jumps in my primary key numbering

Unless you are in danger of running out of values then you probably shouldn't care. If you might push past 2,000,000,000 then use a larger integer type than 32-bit for the key value. As sqlite isn't a strongly typed database you don't even need to declare this.

As a general rule if your primary key is a surrogate key (such as an auto-incrementing value or UUID) rather than being (or being derived from) real properties of the entities being modelled, the actually value should not matter other than it is unique, so gaps between values should not matter either.

There are a number of other reasons why there will be gaps in autoincrement values, so the solution currently accepted does not stop this from happening (it just addresses one of the reasons why it will, so will reduce the occurrence but not remove it).

Caring about such values is a common antipattern which you will find more detailed discussions regarding in a number of places (the popular, and recommended IMO, book "SQL Antipatterns" has a section covering it under the name "Pseudokey Neat-Freak").

2
  • thanks for this comment, not sure I will buy the book though ;-)
    – splaisan
    Oct 1, 2020 at 15:20
  • @splaisan - fair enough, as said you'll find the same discussions elsewhere though perhaps with a different name given to the pattern. Oct 1, 2020 at 21:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.