7

I have a database of several tables in which the first field is defined as:

'ID' INTEGER NOT NULL UNIQUE ... PRIMARY_KEY('ID')

I do not use the AUTOINCREMENT keyword for any of these tables yet my db contains an empty sqlite_sequence table which I cannot delete. Inserting records into these tables with a NULL value for the ID field generates default incremental values for the ID fields but does not populate or update the sqlite_sequence table. Is there any way I can reset the last inserted rowid value to 0 for these tables?

MartinL
  • 71
  • 1
  • 2
  • 4
    When you define an integer column as PRIMARY_KEY it is also AUTOINCREMENT. Read more here: https://www.sqlite.org/autoinc.html – forpas Aug 06 '19 at 11:41
  • 1
    `UNIQUE` on a primary key column is redundant and pointless, btw. – Shawn Aug 06 '19 at 11:51
  • Did you have a table with an `AUTOINCREMENT` integer PK at some point and then drop it? – Shawn Aug 06 '19 at 11:53
  • @Shawn, regarding UNIQUE, point taken. Regarding previously existing but now dropped table, I can't recall but if no longer "active" and empty why can't I delete it? – MartinL Aug 06 '19 at 12:52
  • 3
    It's an internal table (as are all that start with `sqlite_` and explicitly cannot be dropped once the system creates it. https://www.sqlite.org/fileformat2.html#the_sqlite_sequence_table – Shawn Aug 06 '19 at 12:59
  • Perhaps I should rephrase my original question: How do I reset the last inserted rowid to 0 for a table in a db where sqite_sequence is absent or empty and not used? – MartinL Aug 06 '19 at 13:27
  • 1
    i believe a simple `UPDATE` command could do it but resetting/altering a sequence is most of the time a bad idea.. – Raymond Nijland Aug 06 '19 at 14:05
  • In a rowid table without `AUTOINCREMENT`, a new rowid is calculated based on the current maximum rowid present in the table. See the link that forpas provided for a complete description of the algorithm. tl;dr: reset it by deleting all rows from the table. – Shawn Aug 06 '19 at 15:45

0 Answers0