I think this has been answered already in that it is not a bug, but I
would like to mention why it works the way it works.
Imagine you have some tables, like a list of clients (table: client) and
a list of contacts which are foreign-keyed (or simply used in
conjunction) with the client parent table.
If you add 3 clients, let's assume they get IDs 1, 2 and 3 and then you
create for each of them 2 contacts which link to their parent via the
key so that contacts 1 and 2 has Company 1 as their parent, Contacts 3 &
4 has parent company 2, and so on.
Later you decide to delete Company 2. This leaves Contacts 3 and 4
"hanging", which may or may not be fine with you (and if the foreign key
was set up with cascading deletes then they would have been deleted too,
but let's imagine they are not). Now if you add the next company,
company 4, and it magically gets the same ID that was used before (ID 2)
then those hanging contacts will now again seem to be linked and they
will seem to be linked to company 4 which now has ID 2. Which is of
course complete unwanted.
You can extrapolate this to larger scale DBs where entire history tables
or comments or any amount of things can be linked to IDs which will
point to the wrong entries if IDs are re-used. The industry standard to
avoid any and all of this, is to NEVER use an ID twice.
In SQLite, there might be a way to achieve what you want/expected (more
or less).
If a table is created with an Integer primary key but without the
"AUTOINCREMENT" directive, it will still auto-increment the new keys if
you insert rows with NULL for the ID (because the DB helps out since the
primary key cannot be duplicate or null, if you try to insert null for
the PK, it assumes you mean "the next ID"). When you delete rows *from
the END of the list* it might use those deleted IDs again (i.e. if you
had ID 1, 2 and 3 and you deleted 2 and 3, it might use 2 and 3 again in
a next session, but if you deleted 2 from 1,2,3 it will still make the
next 4 so that you have 1,3,4 now - it won't assign an ID less than the
current last ID in the DB).
If you use the AUTOINCREMENT directive, SQLite promises to never use an
ID twice, so every next ID will be one higher than any ID that was
already used before, deleted or not (i.e. if you had IDs 1,2 and 3 and
deleted the last two, the next added one will be 4 regardless).
You can circumvent this behaviour by UPDATEing the sqlite3_sequence
table (which keeps a record of the last-assigned incremented ID for
every table) with the new lower ID to use (or whatever you like the
next ID to be). I'm not sure now if it keeps the last ID used, or the
next ID to use, but that's a matter of public record or a 1-minute test
to figure out.
Lastly, a comment I've made possibly more than once on this list: There
is no imperative to trust the SQL engine with ID assignments. You are
free to (and I prefer to) assign IDs yourself. You can add ID n to the
list, you do not need in any way to be at the mercy of the "next ID"
decided by the SQL engine. AUTOINCREMENT is merely a convenience, not a
rule, requirement or standard (in conformance terms).
Hope that makes sense :)
Ryan
On 2017/09/01 3:20 AM, Noah Simon wrote:
Hello,
While using the sqlite3 SQL shell from macOS Sierra Terminal, a bug (I think)
occurred.
Version number:
sqlite> .version
SQLite 3.13.0 2016-05-18 10:57:30 fc49f556e48970561d7ab6a2f24fdd7d9eb81ff2
What happened was I created a table with an autoincremented primary key. After
creating a row of values, I deleted that row. I then created two more rows, but
their IDs were 2 and 3, not 1 and 2. The expected behavior was that when I
removed the most recent row in a table, the autoincrement would go back. Is
this a misunderstanding on my part, or should this be fixed? Let me know if you
need reproducing steps.
Thank you,
Noah Simon
_______________________________________________
sqlite-users mailing list
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
