SQLite UNIQUE 遇到 NULL：用部分唯一索引限制每个用户只有一个默认项
地址簿里每个用户可以保存多条地址，但最多只能有一个默认地址。有人会增加一个可空标记，再建立联合唯一索引，期待数据库自动理解“默认只能有一个”。问题在于，唯一约束比较的是被索引的值，它不会替应用推断哪类记录需要竞争同一个名额。
SQLite 的唯一索引把各个 NULL 视为不同值，因此同一用户的多条空标记记录可以同时存在。这有合理用途，却容易与“没有填写也算相同”的业务想法冲突。与其依赖某个特殊空值作为暗号，不如把需要受约束的行明确写进索引条件。
AI概念配图，非真实界面：以抽象物件说明本文主题，不代表运行结果。
先复现空值，再表达默认项规则
下面是只在内存数据库中运行的完整 Python 3 示例。第一张表展示两个 NULL 都能写入。第二张表使用非空用户编号和二值默认标记，再建立只覆盖默认行的唯一索引；普通行不进入该索引，因此同一用户可以保留多个普通地址。
索引键只有 user_id，条件是 is_default 等于一。这样，每位用户的默认地址竞争同一个键，而不同用户之间互不冲突。NOT NULL 与 CHECK 则把默认标记限制为明确的零或一，防止第三种状态悄悄绕开条件。
import sqlite3
con = sqlite3.connect(":memory:", isolation_level=None)
con.execute("CREATE TABLE nullable(user_id INTEGER, marker INTEGER, UNIQUE(user_id, marker))")
con.executemany("INSERT INTO nullable VALUES (?, ?)", [(1, None), (1, None)])
assert con.execute("SELECT COUNT(*) FROM nullable").fetchone()[0] == 2
con.executescript("""
CREATE TABLE address(
id INTEGER PRIMARY KEY,
user_id INTEGER NOT NULL,
label TEXT NOT NULL,
is_default INTEGER NOT NULL CHECK(is_default IN (0, 1))
);
CREATE UNIQUE INDEX one_default ON address(user_id) WHERE is_default = 1;
""")
con.executemany("INSERT INTO address VALUES (?, ?, ?, ?)", [
(1, 10, "home", 1), (2, 10, "office", 0),
(3, 10, "other", 0), (4, 20, "home", 1),
])
def conflict(sql, args):
try:
con.execute(sql, args)
except sqlite3.IntegrityError:
return
raise AssertionError("constraint did not fire")
conflict("INSERT INTO address VALUES (?, ?, ?, ?)", (5, 10, "new", 1))
conflict("UPDATE address SET is_default = 1 WHERE id = ?", (2,))
conflict("INSERT INTO address VALUES (?, ?, ?, ?)", (6, 30, "bad", None))
assert con.execute("SELECT is_default FROM address WHERE id = 2").fetchone() == (0,)
rows = con.execute("SELECT user_id, COUNT(*) FROM address WHERE is_default = 1 GROUP BY user_id ORDER BY user_id").fetchall()
assert rows == [(10, 1), (20, 1)]
print("nullable rows:", 2)
print("default counts:", rows)
con.close()新增与更新都要让数据库验收
程序先写入两名用户的记录，再确认同一用户新增默认项会失败；把已有普通地址改为默认，同样会触发约束。断言还检查更新失败后原标记没有改变，说明应用不能仅凭“准备更新成功”的内存状态来向界面汇报结果。
不要把唯一索引替换成“先查询没有默认项，再插入”。两个并发请求可能都查询到空缺，随后一起写入。数据库约束才是最后一道一致性判定，应用应捕获冲突，把它映射成可理解的业务错误，或重新读取当前默认项。
唯一索引约束的是“至多一个”，不是“必须有一个”。将所有地址设为普通状态仍然合法。如果业务要求始终存在默认项，需要进一步设计创建、删除与切换流程；不能因为插入冲突已处理，就认为整个默认地址生命周期已经完整。
切换与迁移需要另外安排
切换默认地址时，通常在同一事务里先清除旧标记，再设置新标记。若先设置新默认项，会立即撞上旧记录的唯一键。事务失败应整体回滚，避免留下没有默认项的中间状态；本文只测试约束行为，没有代替完整切换接口。
给已有表添加索引前，先按用户统计默认行，找出重复并决定如何修复。数据库不会自动挑选最新或最可信的一条；直接创建索引会因为现有冲突失败。修复规则应由业务确定，再在受控迁移中执行并验收。
部分索引还会受到版本与查询写法的影响：运行环境至少需要支持该功能的 SQLite 版本。这里的正确性来自写入约束，不以查询优化器是否使用索引为前提。性能问题应另看查询计划，不能凭名称里有“索引”就推断所有读取都会变快。
