SQLite UNIQUE 约束：单列、联合唯一与 NULL

UNIQUE 的意思是“不允许重复”

UNIQUE 约束告诉 SQLite：某一列（或一组列）的值在各行之间不能重复。它用来表达“两个用户不能共用一个邮箱”或者“一个商品编码最多出现一次”这类规则。

CREATE TABLE users (
    id    INTEGER PRIMARY KEY,
    email TEXT UNIQUE,
    name  TEXT
);

INSERT INTO users (email, name) VALUES ('ada@example.com', 'Ada');
INSERT INTO users (email, name) VALUES ('boris@example.com', 'Boris');
INSERT INTO users (email, name) VALUES ('ada@example.com', 'Ada Two');

SELECT * FROM users;

第三条插入失败，报错 UNIQUE constraint failed: users.email。SQLite 在每次写入时都会检查约束，拒绝任何会产生重复值的操作。前两行保存成功，第三行根本没有写进去。

在幕后，UNIQUE 是用唯一索引实现的，也就是 SQLite 用来快速查找的同一种数据结构，所以检查的开销很小，而且这一列会自动建立索引。

列级写法和表级写法

UNIQUE 有两种写法：直接写在列的后面，或者作为一个单独的子句写在表定义的末尾：

-- Column-level: simple, one column.
CREATE TABLE products (
    sku   TEXT UNIQUE,
    name  TEXT
);

-- Table-level: same effect, different placement.
CREATE TABLE products2 (
    sku   TEXT,
    name  TEXT,
    UNIQUE(sku)
);

对单列来说，两种写法完全等价，选读起来更顺的那种即可。一旦你需要跨多列的唯一性，表级写法就必不可少了。

联合 UNIQUE：多列组合唯一

有时单独一列并不唯一，但它们的组合应该唯一。一个用户可以报名多门课程，一门课程也可以有很多用户，但同一对 (user_id, course_id) 不应该出现两次：

CREATE TABLE enrollments (
    id        INTEGER PRIMARY KEY,
    user_id   INTEGER NOT NULL,
    course_id INTEGER NOT NULL,
    UNIQUE(user_id, course_id)
);

INSERT INTO enrollments (user_id, course_id) VALUES (1, 100);
INSERT INTO enrollments (user_id, course_id) VALUES (1, 101);  -- fine, different course
INSERT INTO enrollments (user_id, course_id) VALUES (2, 100);  -- fine, different user
INSERT INTO enrollments (user_id, course_id) VALUES (1, 100);  -- fails: pair already exists

SELECT * FROM enrollments;

约束针对的是这一对值，而不是其中任何一列。用户 1 可以报名很多门课程，课程 100 可以有很多用户，但每种组合只能出现一次。

这是多对多关系中关联表的标准写法。

UNIQUE 和 PRIMARY KEY

两者听起来很像，也确实有关联，但并不是一回事：

• 一张表最多只有一个 PRIMARY KEY，但可以有多个 UNIQUE 约束。
• PRIMARY KEY 是行的身份标识，外键指向的就是它，rowid 是它的别名。
• UNIQUE 只表示“这个值（或组合）不会重复”。
• 在普通表中，UNIQUE 列可以存放 NULL 值，PRIMARY KEY 不行（有一个历史遗留的例外，这里略过）。

一种常见的结构：

CREATE TABLE accounts (
    id       INTEGER PRIMARY KEY,   -- internal identity, never changes
    email    TEXT NOT NULL UNIQUE,  -- user-facing identifier, must be unique
    username TEXT NOT NULL UNIQUE   -- another unique handle
);

id 是数据库其他部分引用的对象。email 和 username 之所以唯一，是因为应用需要，而不是因为它们是身份标识。用户修改邮箱时，id 保持不变，这正是把它们分开的意义。

NULL 的特殊行为

几乎每个人第一次都会在这里栽跟头。SQLite 中的 UNIQUE 列可以接受任意多个 NULL 值：

CREATE TABLE invites (
    id    INTEGER PRIMARY KEY,
    email TEXT UNIQUE
);

INSERT INTO invites (email) VALUES (NULL);
INSERT INTO invites (email) VALUES (NULL);
INSERT INTO invites (email) VALUES (NULL);
INSERT INTO invites (email) VALUES ('ada@example.com');
INSERT INTO invites (email) VALUES ('ada@example.com');  -- this one fails

SELECT * FROM invites;

三个 NULL，没问题。两个 'ada@example.com'，就冲突了。

原因是：SQL 把 NULL 当作“未知”，两个未知值不被视为相等，所以唯一性检查无法判定它们重复。如果最多只允许一个 NULL，最简洁的办法是 NOT NULL UNIQUE。如果 NULL 是合法的，但要求在另一列的每种取值下只能有一个，就用部分索引（在后面的索引章节中介绍）。

处理冲突：ON CONFLICT

默认情况下，违反 UNIQUE 约束会中止语句。但有时你想要不同的行为：替换已有的行、忽略新的行，或者更新特定的列。SQLite 提供了两种方式。

第一种是用 ON CONFLICT 直接写在约束里：

CREATE TABLE settings (
    key   TEXT UNIQUE ON CONFLICT REPLACE,
    value TEXT
);

INSERT INTO settings (key, value) VALUES ('theme', 'light');
INSERT INTO settings (key, value) VALUES ('theme', 'dark');   -- replaces the first
INSERT INTO settings (key, value) VALUES ('lang', 'en');

SELECT * FROM settings;

第二次插入 theme 时，已有的那一行会被删除，由新行取而代之。其他选项有 IGNORE（静默跳过）、ABORT（默认）、FAIL 和 ROLLBACK。

第二种是在每条语句上使用 upsert 语法，通常更灵活，因为它可以更新特定的列：

CREATE TABLE counters (
    name  TEXT UNIQUE,
    count INTEGER NOT NULL DEFAULT 0
);

INSERT INTO counters (name, count) VALUES ('hits', 1);
INSERT INTO counters (name, count) VALUES ('hits', 1)
    ON CONFLICT(name) DO UPDATE SET count = count + 1;
INSERT INTO counters (name, count) VALUES ('hits', 1)
    ON CONFLICT(name) DO UPDATE SET count = count + 1;

SELECT * FROM counters;

第一条插入创建了这一行。后面两条触发了 UNIQUE 约束，转入 DO UPDATE 分支，让 count 加一。这就是 INSERT ... ON CONFLICT 的 upsert 写法，后面有专门的一页介绍它。

UNIQUE 约束和 UNIQUE 索引

CREATE UNIQUE INDEX 和 UNIQUE 约束做的是同一件事。实际上，UNIQUE 约束就是在幕后创建一个唯一索引，两者几乎是同一种机制，只是换了个身份出现。

CREATE TABLE users (
    id    INTEGER PRIMARY KEY,
    email TEXT NOT NULL
);

CREATE UNIQUE INDEX idx_users_email ON users(email);

INSERT INTO users (email) VALUES ('ada@example.com');
INSERT INTO users (email) VALUES ('ada@example.com');  -- fails

什么时候选哪个：

• 约束：当唯一性是表定义的一部分时。它就写在列的旁边，一目了然。
• 唯一索引：当你需要部分索引（WHERE 子句）、需要指定名字，或者想在不重建表的情况下给已有的表加上唯一性时。SQLite 的 ALTER TABLE 不能添加约束，但随时都可以添加索引。

两者在写入时的行为完全相同。选择哪个，主要看你想把这条规则放在表结构的什么位置。

给已有的表添加 UNIQUE

SQLite 的 ALTER TABLE 有意做得很有限，没有 ALTER TABLE ... ADD CONSTRAINT。两种实用的方案：

-- Option 1: add a unique index. Works on existing tables, same enforcement.
CREATE TABLE customers (
    id    INTEGER PRIMARY KEY,
    email TEXT
);

INSERT INTO customers (email) VALUES ('ada@example.com');
INSERT INTO customers (email) VALUES ('boris@example.com');

CREATE UNIQUE INDEX idx_customers_email ON customers(email);

INSERT INTO customers (email) VALUES ('ada@example.com');  -- now fails

方案 2 适用于你确实想把 UNIQUE 子句写进表定义的情况，也就是重建表的那套流程：创建一张带约束的新表，把数据复制过去，删除旧表，再重命名。下一页会介绍。

提醒一句：如果你要给一个已经存在重复值的列加上唯一性，CREATE UNIQUE INDEX 会失败。先清理掉重复的行，再添加索引。

UNIQUE 失败时：读懂错误信息

错误信息会准确告诉你是哪个约束出了问题：

Error: UNIQUE constraint failed: users.email
Error: UNIQUE constraint failed: enrollments.user_id, enrollments.course_id

第一种是 users.email 上的单列约束。第二种是联合约束，两列都被列出来，是因为这个组合已经存在。看到这类错误时：

1. 找出哪一行已经有了冲突的值（SELECT ... WHERE email = '...'）。
2. 决定是更新那一行、跳过这次插入，还是换一个值。
3. 如果重复是预期之内的，并且你想合并它们，改用 INSERT ... ON CONFLICT DO UPDATE。

这个错误之所以这么显眼，是因为大多数时候你确实需要知道：悄无声息的重复数据比一次失败的写入更糟糕。

下一步：删除和修改表

UNIQUE 约束无法用简单的 ALTER TABLE 加到已有的表上。正是这个限制，让 SQLite 在修改表结构时有一套特别的流程，也就是重建表。这是下一页的主题，同时还会介绍如何干净地删除表。