为什么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())

输出：

^{pr2}$

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

Answer 1

SQL PK（主键）表示UNIQUE NOT NULL。你不应该期望PK的值中有一个NULL，更不用说只有一个了。您应该声明PK列不为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.

Answer 2

SQLite和许多其他SQL数据库一样，出于唯一性的目的，将两个NULL视为不同的值（部分原因是在SQL中，NULL == NULL为false）。在

我不相信有办法改变这种行为。作为一种解决方法，您可以使用列b中的空字符串作为“no value”。在