Insert id 1 and 2. Remove id 2. Insert new row, it gets id 2 instead of 3. How do I change this? I don't want to reuse primary keys.
import sqlite3, os
os.remove('mydatabase.db')
conn = sqlite3.connect("mydatabase.db") # or use :memory: to put it in RAM
cursor = conn.cursor()
# create a table
cursor.execute("CREATE TABLE if not exists albums (id INTEGER PRIMARY KEY NOT NULL, title text, artist text, release_date text, publisher text, media_type text)")
cursor.execute("CREATE TABLE if not exists anti (id INTEGER, title text, artist text, release_date text, publisher text, media_type text)")
conn.commit()
cursor.execute("INSERT INTO albums VALUES (null, 'Glow', 'Andy Hunter', '7/24/2012', 'Xplore Records', 'MP3')")
cursor.execute("INSERT INTO albums VALUES (null, 'second', 'artist', '7/24/2012', 'Xplore Records', 'MP3')")
conn.commit()
cursor.execute("SELECT * FROM albums")
print(cursor.fetchall())
cursor.execute("INSERT INTO anti SELECT * FROM albums WHERE title='second'")
cursor.execute("DELETE FROM albums WHERE title='second'")
cursor.execute("INSERT INTO albums VALUES (null, 'third', 'artist', '7/24/2012', 'Xplore Records', 'MP3')")
conn.commit()
cursor.execute("SELECT * FROM albums")
print(cursor.fetchall())
cursor.execute("SELECT * FROM anti")
print(cursor.fetchall())
From https://www.sqlite.org/autoinc.html:
If the AUTOINCREMENT keyword appears after INTEGER PRIMARY KEY, that changes the automatic ROWID assignment algorithm to prevent the reuse of ROWIDs over the lifetime of the database. In other words, the purpose of AUTOINCREMENT is to prevent the reuse of ROWIDs from previously deleted rows.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With