python - 为什么 SQLite 会插入重复的复合主键？

Question

代码:

import sqlite3
c = sqlite3.Connection(':memory:')
c.execute('CREATE TABLE foo(a INTEGER, b VARCHAR(8), PRIMARY KEY(a, b))')
c.execute('INSERT INTO foo(a) VALUES (1)')
c.execute('INSERT INTO foo(a) VALUES (1)')
print(c.execute('SELECT * FROM foo').fetchall())

输出:

[(1, None), (1, None)]

为什么 SQLite 插入具有重复主键的行？我该如何解决这个问题？

Answer 1

SQL PK (PRIMARY KEY) 表示 UNIQUE NOT NULL。您不应该期望 PK 的值可以为 NULL，更不用说只有一个了。您应该将 PK 列声明为 NOT NULL，并且不要将 NULL 放入其中。

SQL As Understood By SQLite :

Each row in a table with a primary key must have a unique combination of values in its primary key columns. For the purposes of determining the uniqueness of primary key values, NULL values are considered distinct from all other values, including other NULLs. If an INSERT or UPDATE statement attempts to modify the table content so that two or more rows have identical primary key values, that is a constraint violation.

According to the SQL standard, PRIMARY KEY should always imply NOT NULL. Unfortunately, due to a bug in some early versions, this is not the case in SQLite. Unless the column is an INTEGER PRIMARY KEY or the table is a WITHOUT ROWID table or the column is declared NOT NULL, SQLite allows NULL values in a PRIMARY KEY column. SQLite could be fixed to conform to the standard, but doing so might break legacy applications. Hence, it has been decided to merely document the fact that SQLite allowing NULLs in most PRIMARY KEY columns.

由于 PK 中的 NULL 是针对 SQL 的，因此当在 PK 中使用 NULL 约束和操作表时，SQLite 选择做什么似乎没有实际意义。但它使用通常的 SQL 解释，即 NULL 不等于 NULL 用于 UNIQUE。这就像您声明一个列集 UNIQUE NULL。因此，作为约束，SQLite PK 是 UNIQUE 的同义词，而不是 UNIQUE NOT NULL。

A UNIQUE constraint is similar to a PRIMARY KEY constraint, except that a single table may have any number of UNIQUE constraints. For each UNIQUE constraint on the table, each row must contain a unique combination of values in the columns identified by the UNIQUE constraint. For the purposes of UNIQUE constraints, NULL values are considered distinct from all other values, including other NULLs.