不要使用已删除的主键 [英] Don't use deleted primary keys

问题描述

插入 id 1 和 2.删除 id 2.插入新行，它得到 id 2 而不是 3.我该如何更改?我不想重复使用主键.

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())

推荐答案

来自 https://www.sqlite.org/autoinc.html:

如果 AUTOINCREMENT 关键字出现在 INTEGER PRIMARY KEY 之后，则会更改自动 ROWID 分配算法以防止在数据库的生命周期内重复使用 ROWID.换句话说，AUTOINCREMENT 的目的是防止重复使用先前删除的行中的 ROWID.

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.

这篇关于不要使用已删除的主键的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！