SQLite复合主键出现重复值：该现象的含义是什么？

问题场景 #

创建表并执行SQL：

CREATE TABLE t2
(
    id INTEGER, 
    a INTEGER, 
    b TEXT, 
    PRIMARY KEY(id, a)
);

INSERT INTO t2 (a, b) VALUES (17, 'seven');
INSERT INTO t2 (a, b) VALUES (17, 'seven');

SELECT rowid, * 
FROM t2;

查询结果：

(1, None, 17, 'seven')
(2, None, 17, 'seven')

现象本质 #

这个现象的核心是SQLite对NULL的比较逻辑特殊：

• 在SQLite中，NULL与任何值（包括另一个NULL）的相等比较结果都是NULL（而非TRUE）。
• 主键约束的重复判定逻辑是检查是否存在完全相等的主键值组合。因为两次插入的id都是NULL，而NULL之间不被判定为相等，所以(NULL,17)不会被视为重复的复合主键，因此SQLite允许这两条记录插入。

补充说明 #

如果需要避免这种情况，需要显式给id字段添加NOT NULL约束，修改表定义为：

CREATE TABLE t2
(
    id INTEGER NOT NULL, 
    a INTEGER, 
    b TEXT, 
    PRIMARY KEY(id, a)
);

此时如果插入时不指定id的值，SQLite会抛出NOT NULL constraint failed: t2.id的错误，阻止插入不符合主键约束的记录。

内容的提问来源于stack exchange，提问作者Alexey Starinsky