PHP小码农

Appearance

UNIQUE 唯一约束 ​

唯一约束（Unique Constraint）用来保证某一列或某几列的组合值在表中不重复。它看起来和主键很像，但有两个关键差别：允许 NULL、可以有多个。也正因为允许 NULL，它的行为常常和直觉相反——NULL 之间互不相等，所以一列可以有任意多行 NULL。本节把这条规则、NULLS NOT DISTINCT 的解法、多列唯一的语义、ON CONFLICT 与并发下的坑，以及「交换两行值」这类经典难题一次讲清。

前置知识

阅读本节前，建议先了解：FOREIGN KEY 外键

基础概念 ​

唯一约束的语义可以概括成一句话：任何两行的受约束列组合值不能相等。但「相等」的定义里有一个例外——NULL。

text
UNIQUE (col)                →  除 NULL 外，col 的值两两不同；NULL 可以有任意多个
UNIQUE NULLS NOT DISTINCT   →  NULL 也参与比较，最多只能有一个 NULL

唯一约束与主键的差异是本节所有讨论的出发点：

特性UNIQUEPRIMARY KEY
是否允许 NULL✅ 允许（默认多个 NULL）❌ 不允许，自动 NOT NULL
一个表能有多少个任意多个最多一个
是否自动建 B-tree 索引✅ 是✅ 是
索引名默认前缀<表名>_<列名>_key<表名>_pkey
能否被外键引用✅ 能✅ 能
能否 DEFERRABLE✅ 能✅ 能
是否支持多列✅ 支持✅ 支持
索引类型必须是 B-tree必须是 B-tree

两者的实现机制完全一致：都在背后创建一个唯一 B-tree 索引，由这个索引来负责唯一性检查。所以很多关于索引的结论（在线创建、USING INDEX 复用、部分索引、CONCURRENTLY）在两者之间通用。

记住这条

UNIQUE 不会自动加 NOT NULL。如果希望某列既唯一又非空，除了主键之外都必须显式写 NOT NULL。这是「唯一约束里出现重复 NULL」类事故的根源。

唯一约束在系统表中由 contype 区分：

contype含义说明
p主键（Primary Key）自动 NOT NULL，唯一索引名为 *_pkey
u唯一（Unique）允许 NULL，可有多个
n非空（Not Null）PostgreSQL 18 起才有独立条目
c检查（Check）—
f外键（Foreign Key）常引用 u 类型约束
x排他（Exclude）见 EXCLUDE 排他约束

语法与用法 ​

text
-- 列级写法
column_name data_type UNIQUE [ NULLS [ NOT ] DISTINCT ] [ index_parameters ]

-- 表级写法（必须用表级才能写多列）
[ CONSTRAINT constraint_name ] UNIQUE [ NULLS [ NOT ] DISTINCT ] ( column_name [, ... ] )
  [ INCLUDE ( column_name [, ... ] ) ]               -- 覆盖列（PostgreSQL 11+）
  [ WITH ( storage_parameter = value [, ... ] ) ]
  [ USING INDEX TABLESPACE tablespace_name ]

-- 已有表上增删
ALTER TABLE table_name ADD [ CONSTRAINT constraint_name ] UNIQUE ( column_name [, ... ] )
ALTER TABLE table_name ADD CONSTRAINT constraint_name UNIQUE USING INDEX index_name
ALTER TABLE table_name DROP CONSTRAINT constraint_name [ CASCADE ]

-- 裸唯一索引（不是约束）
CREATE UNIQUE INDEX index_name ON table_name ( column_name [, ... ] ) [ NULLS [ NOT ] DISTINCT ]

最简可运行示例：

sql
CREATE TABLE users (
  id    bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  email text   NOT NULL UNIQUE,      -- 列级写法
  phone text,                        -- 允许 NULL
  tenant_id bigint NOT NULL,
  code  text,
  CONSTRAINT uq_users_tenant_code UNIQUE (tenant_id, code)   -- 表级 + 命名，复合唯一
);
text
CREATE TABLE

查看生成的约束与索引：

sql
\d users
text
                            Table "public.users"
  Column   |  Type  | Collation | Nullable |           Default
-----------+--------+-----------+----------+------------------------------
 id        | bigint |           | not null | generated always as identity
 email     | text   |           | not null |
 phone     | text   |           |          |
 tenant_id | bigint |           | not null |
 code      | text   |           |          |
Indexes:
    "users_pkey" PRIMARY KEY, btree (id)
    "users_email_key" UNIQUE CONSTRAINT, btree (email)
    "uq_users_tenant_code" UNIQUE CONSTRAINT, btree (tenant_id, code)

注意 NOT NULL 与 UNIQUE 是分开的两件事：code 列只有唯一约束，没有非空约束。

详细说明 ​

UNIQUE 中 NULL 被视为互不相等（最重要） ​

这是唯一约束最容易踩的坑，也是 PostgreSQL（以及 SQL 标准）与多数人直觉最大的分歧点。

标准里 NULL 表示「未知」，而「未知 = 未知」的结果不是真，而是 NULL（未知）。唯一性检查基于「相等」判断，因此两个 NULL 不会被认为是重复值，检查直接放行：

sql
CREATE TABLE demo_null (
  id   bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  code text UNIQUE
);
sql
-- 连续插入 3 行 NULL，全部成功
INSERT INTO demo_null (code) VALUES (NULL);
INSERT INTO demo_null (code) VALUES (NULL);
INSERT INTO demo_null (code) VALUES (NULL);

SELECT id, code FROM demo_null ORDER BY id;
text
 id | code
----+------
  1 |
  2 |
  3 |
(3 rows)

而插入重复的非空值会被拒绝：

sql
-- 错误！duplicate key value violates unique constraint "demo_null_code_key"
INSERT INTO demo_null (code) VALUES ('A');
INSERT INTO demo_null (code) VALUES ('A');
text
ERROR:  duplicate key value violates unique constraint "demo_null_code_key"
DETAIL:  Key (code)=(A) already exists.

这意味着一个 UNIQUE 列不能用来表达「这一列最多有一条软删除记录」或「每租户最多一条默认配置」这类需求，只要标志列用了 NULL：

sql
-- 直觉上想表达「每个用户最多一条默认地址」
CREATE TABLE addresses (
  id        bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  user_id   bigint NOT NULL,
  is_default boolean,          -- 默认地址为 true，非默认为 NULL
  UNIQUE (user_id, is_default)
);
sql
-- 危险！三行都成功：is_default 都是 NULL，互不相等
INSERT INTO addresses (user_id, is_default) VALUES (1, NULL);
INSERT INTO addresses (user_id, is_default) VALUES (1, NULL);
INSERT INTO addresses (user_id, is_default) VALUES (1, NULL);

-- 只有 true 那一行会被真正约束
INSERT INTO addresses (user_id, is_default) VALUES (1, true);
-- 错误！第二行 true 才会冲突
INSERT INTO addresses (user_id, is_default) VALUES (1, true);

正确做法：把标志列改成 NOT NULL DEFAULT false，让它永远不出现 NULL；或者用 PostgreSQL 15+ 的 NULLS NOT DISTINCT 让 NULL 也参与比较。

UNIQUE NULLS NOT DISTINCT（PostgreSQL 15+） ​

如果你的业务语义是「NULL 也算重复」，从 PostgreSQL 15 开始可以直接声明：

sql
-- PostgreSQL 15+
CREATE TABLE demo_null_nd (
  id   bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  code text UNIQUE NULLS NOT DISTINCT
);
sql
INSERT INTO demo_null_nd (code) VALUES (NULL);   -- 成功
INSERT INTO demo_null_nd (code) VALUES (NULL);   -- 错误！
text
ERROR:  duplicate key value violates unique constraint "demo_null_nd_code_key"
DETAIL:  Key (code)=() already exists.

多列同样适用，此时「任意列相等即冲突」的判定由整个行值决定：

sql
-- PostgreSQL 15+
CREATE TABLE demo_multi_nd (
  tenant_id bigint,
  code      text,
  UNIQUE NULLS NOT DISTINCT (tenant_id, code)
);

INSERT INTO demo_multi_nd VALUES (1, NULL);   -- 成功
INSERT INTO demo_multi_nd VALUES (1, NULL);   -- 错误！(1, NULL) 视为相等
INSERT INTO demo_multi_nd VALUES (NULL, NULL);-- 成功
INSERT INTO demo_multi_nd VALUES (NULL, NULL);-- 错误！(NULL, NULL) 视为相等

注意 NULLS NOT DISTINCT 的判定是整行的组合，不是逐列：(1, NULL) 与 (2, NULL) 不相等，不会冲突。

裸唯一索引也支持这个选项：

sql
-- PostgreSQL 15+
CREATE UNIQUE INDEX uq_demo_null_nd ON demo_null_nd (code) NULLS NOT DISTINCT;

多列唯一 UNIQUE (a, b) 的语义 ​

多列唯一约束约束的是组合值，而不是「每一列各自唯一」。这是极高频的误解：

sql
CREATE TABLE user_roles (
  user_id bigint NOT NULL,
  role_id bigint NOT NULL,
  UNIQUE (user_id, role_id)
);
sql
-- 正确：组合不同，允许
INSERT INTO user_roles VALUES (1, 10);
INSERT INTO user_roles VALUES (1, 20);
INSERT INTO user_roles VALUES (2, 10);

-- 错误！duplicate key value violates unique constraint "user_roles_user_id_role_id_key"
-- (1, 10) 这个组合已存在
INSERT INTO user_roles VALUES (1, 10);

常见误解是以为 UNIQUE (tenant_id, code) 等于「tenant_id 唯一 且 code 唯一」：

sql
-- 错误！这两行会冲突，说明 (tenant_id, code) 是组合唯一而非各自唯一
INSERT INTO user_roles VALUES (1, 10);
INSERT INTO user_roles VALUES (10, 1);   -- 组合不同，实际不会冲突

如果确实需要「每一列各自唯一」，必须写两个独立的唯一约束：

sql
-- 各自唯一：需要两条
CONSTRAINT uq_a UNIQUE (tenant_id),
CONSTRAINT uq_b UNIQUE (code)

同样地，复合唯一约束里含有可空列时，NULL 会打破组合判等：

sql
CREATE TABLE shipment_items (
  shipment_id bigint,
  line_no     integer,
  UNIQUE (shipment_id, line_no)
);

-- 以下两行都能插入：line_no 为 NULL，组合不相等
INSERT INTO shipment_items VALUES (1, NULL);
INSERT INTO shipment_items VALUES (1, NULL);

UNIQUE 自动建索引（\d 证明）与 USING INDEX 复用 ​

建完唯一约束后立刻查表结构和系统表：

sql
CREATE TABLE products (
  sku  text UNIQUE,
  name text NOT NULL
);
sql
\d products
text
                          Table "public.products"
 Column | Type | Collation | Nullable | Default
--------+------+-----------+----------+---------
 sku    | text |           |          |
 name   | text |           | not null |
Indexes:
    "products_sku_key" UNIQUE CONSTRAINT, btree (sku)

从 pg_index 侧确认这个索引与约束的绑定关系：

sql
SELECT i.indexrelid::regclass AS index_name,
       i.indisunique,
       i.indisprimary,
       c.conname,
       c.contype
FROM pg_index i
LEFT JOIN pg_constraint c ON c.conindid = i.indexrelid
WHERE i.indrelid = 'products'::regclass;
text
    index_name     | indisunique | indisprimary |     conname      | contype
-------------------+-------------+--------------+------------------+---------
 products_sku_key  | t           | f            | products_sku_key | u
(1 row)

反过来，如果已经存在一个合适的唯一索引，可以直接把它「收编」为约束，避免重建索引：

sql
-- 先建索引（可以直接在业务低峰期 CONCURRENTLY 建）
CREATE UNIQUE INDEX uq_orders_order_no ON orders (order_no);

-- 再用它作为约束的实现，不重新扫描、不重建索引
ALTER TABLE orders ADD CONSTRAINT uq_orders_order_no UNIQUE USING INDEX uq_orders_order_no;

USING INDEX 有严格前置条件，不满足会报错：

条件说明
索引必须已存在且 indisvalid = trueCONCURRENTLY 建失败的索引不可用
索引必须是唯一索引indisunique = true
索引不能是部分索引带 WHERE 的部分唯一索引无法升级为约束
索引不能是表达式索引表达式无法用约束语法表达
必须是 B-tree 且使用默认排序带 DESC / NULLS FIRST / 非默认 opclass 的索引不行
索引列集合必须与约束列一致列顺序也要一致
指定了约束名时索引会被重命名未指定约束名则约束沿用索引名
sql
-- 错误！ERROR: index "uq_active_email" is a partial index，不能作为约束实现
CREATE UNIQUE INDEX uq_active_email ON users (email) WHERE deleted_at IS NULL;
ALTER TABLE users ADD CONSTRAINT uq_active_email UNIQUE USING INDEX uq_active_email;

CREATE UNIQUE INDEX vs ADD CONSTRAINT UNIQUE ​

两者都依赖一个唯一 B-tree 索引，差别在「约束」这一层额外的能力。很多人以为二者等价，直到需要用外键引用或延迟检查时才发现不行。

能力ADD CONSTRAINT UNIQUE / UNIQUECREATE UNIQUE INDEX（裸索引）
唯一性检查✅✅
被外键引用（REFERENCES 目标）✅✅ 也可以¹
能被 DEFERRABLE 延迟✅❌ 不能
出现在 pg_constraint 中✅ contype = 'u'❌ 不出现
出现在 information_schema.table_constraints✅❌
支持部分索引（WHERE）❌✅
支持表达式索引❌✅
支持 NULLS NOT DISTINCT✅✅
支持 INCLUDE 覆盖列✅✅
删除方式DROP CONSTRAINTDROP INDEX

¹ 引用目标是裸唯一索引时，它必须是完整（非部分、非表达式）唯一索引；且这种外键引用在依赖管理上不如约束清晰，推荐用约束。

sql
-- 裸唯一索引：语法上可以作为外键目标
CREATE UNIQUE INDEX uq_tmp_code ON tmp_code (code);
CREATE TABLE ref_tmp (code text REFERENCES tmp_code (code));

关键差别体现在延迟检查上：

sql
-- 错误！裸唯一索引无法声明 DEFERRABLE，语法上也没地方写
CREATE UNIQUE INDEX uq_demo_defer ON demo_defer (code) DEFERRABLE;  -- 语法错误
sql
-- 正确：约束可以延迟
ALTER TABLE demo_defer ADD CONSTRAINT uq_demo_defer UNIQUE (code)
  DEFERRABLE INITIALLY IMMEDIATE;

同理，想在 information_schema 里统一巡检所有约束，就必须用约束形式；只建裸索引的库在这类巡检里会「隐身」。

NULLS NOT DISTINCT 与 NULLS FIRST / LAST 的语法位置差异 ​

这两个 NULLS 选项名字相似，但位置和含义完全不同，写在同一个 CREATE INDEX 里特别容易混淆：

选项位置作用
NULLS [NOT] DISTINCT紧跟列列表之后（索引级）决定 NULL 是否参与唯一性判等
NULLS FIRST / NULLS LAST跟在某一列之后（列级）决定排序时 NULL 排在最前还是最后
sql
-- NULLS FIRST/LAST：列级，跟在列名后面，影响排序顺序
CREATE INDEX idx_orders_created ON orders (created_at DESC NULLS FIRST, id);

-- NULLS NOT DISTINCT：索引级，写在所有列之后
CREATE UNIQUE INDEX uq_orders_code ON orders (code) NULLS NOT DISTINCT;
sql
-- 错误！NULLS NOT DISTINCT 是索引级选项，不能写在列名后面
CREATE UNIQUE INDEX uq_bad ON orders (code NULLS NOT DISTINCT);
text
ERROR:  syntax error at or near "NOT"
LINE 1: CREATE UNIQUE INDEX uq_bad ON orders (code NULLS NOT DISTINCT);

两者可以同时出现在一条语句里，只要位置正确：

sql
-- PostgreSQL 15+
CREATE UNIQUE INDEX uq_orders_tenant_code
  ON orders (tenant_id, code DESC NULLS LAST) NULLS NOT DISTINCT;

B-tree 索引默认把 NULL 当作最大值，即 NULLS LAST（升序）／NULLS FIRST（降序）。详见 索引排序与 NULLS FIRST / LAST。

唯一约束与 ON CONFLICT 的配合 ​

INSERT ... ON CONFLICT（业界常称 upsert）要求能找到一个唯一索引或唯一约束作为冲突判定依据，否则直接报错：

sql
CREATE TABLE counters (
  name  text PRIMARY KEY,
  value bigint NOT NULL
);

-- 正确：name 有主键，ON CONFLICT (name) 可用
INSERT INTO counters (name, value) VALUES ('hits', 1)
ON CONFLICT (name) DO UPDATE SET value = counters.value + 1;
sql
-- 错误！there is no unique or exclusion constraint matching the ON CONFLICT specification
CREATE TABLE logs (
  id      bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  message text,
  created timestamptz
);

INSERT INTO logs (message) VALUES ('x')
ON CONFLICT (message) DO NOTHING;
text
ERROR:  there is no unique or exclusion constraint matching the ON CONFLICT specification

哪怕表上有 message 的普通索引也不行，必须是唯一索引或约束。补上唯一约束后即可：

sql
ALTER TABLE logs ADD CONSTRAINT uq_logs_message UNIQUE (message);

INSERT INTO logs (message) VALUES ('x') ON CONFLICT (message) DO NOTHING;

几个实用细节：

sql
-- 指定约束名而不是列：适合多列或表达式索引
INSERT INTO logs (message) VALUES ('x')
ON CONFLICT ON CONSTRAINT uq_logs_message DO NOTHING;

-- 部分唯一索引也能用于 ON CONFLICT，但推断条件必须匹配
CREATE UNIQUE INDEX uq_logs_msg_active ON logs (message) WHERE deleted_at IS NULL;
INSERT INTO logs (message, created) VALUES ('x', now())
ON CONFLICT (message) WHERE deleted_at IS NULL DO NOTHING;

-- 冲突时返回实际发生的事
INSERT INTO counters (name, value) VALUES ('hits', 1)
ON CONFLICT (name) DO UPDATE SET value = counters.value + 1
RETURNING name, value, (xmax <> 0) AS was_update;

ON CONFLICT ... DO UPDATE 不能对同一个目标行在一句话内更新两次，否则报 ON CONFLICT DO UPDATE command cannot affect row a second time：

sql
-- 错误！同一批 VALUES 里出现重复键，DO UPDATE 会在同一条命令内二次命中
INSERT INTO counters (name, value) VALUES ('hits', 1), ('hits', 2)
ON CONFLICT (name) DO UPDATE SET value = counters.value + 1;

解法是插入前先去重（DISTINCT ON / 聚合），或用 DO NOTHING 自行处理。

唯一约束在并发下的行为 ​

这是本节第二个高频坑。唯一索引的冲突检查不会等待对方的未提交状态，也不受 READ COMMITTED 隔离级别保护——两个并发事务可以都通过检查，然后其中一个在提交前失败。

典型时序（READ COMMITTED，两个会话）：

text
-- 会话 A                              -- 会话 B
BEGIN;                                  BEGIN;
INSERT INTO users (email)
  VALUES ('a@x.com');                   -- 未提交
                                        INSERT INTO users (email)
                                          VALUES ('a@x.com');   -- 阻塞！等待 A
                                        -- A 提交后，B 收到：
                                        -- ERROR: duplicate key value
                                        --        violates unique constraint
COMMIT;

关键点：B 不是「一开始就报错」，而是阻塞到 A 提交后被拒绝。在批量并发写入（如接口并发注册、消费者重复消费）场景下，这表现为「偶发报错」而非稳定失败，非常难排查。

正确做法有三种：

sql
-- 方案 1（推荐）：用 ON CONFLICT 把冲突变成正常分支
INSERT INTO users (email, name) VALUES ('a@x.com', 'alice')
ON CONFLICT (email) DO NOTHING;
sql
-- 方案 2：捕获唯一冲突并重试（应用层实现，注意只重试 23505）
-- PostgreSQL 唯一约束冲突的错误码：23505 unique_violation
sql
-- 方案 3：显式加咨询锁串行化同一键的操作
SELECT pg_advisory_xact_lock(hashtext('user:a@x.com'));
INSERT INTO users (email, name) VALUES ('a@x.com', 'alice');

需要特别提醒的是：把隔离级别提到 REPEATABLE READ 或 SERIALIZABLE 不能把唯一冲突变成序列化失败从而自动重试，唯一索引检查在这两个级别下依然会抛出 23505（而不是 40001）。所以拦截唯一冲突的兜底逻辑必须处理 23505。

另外，DEFERRABLE 唯一约束在并发下的行为更弱：

sql
-- 延迟到提交时才检查，冲突更晚暴露，且错误码仍是 23505
ALTER TABLE users ADD CONSTRAINT uq_users_email UNIQUE (email)
  DEFERRABLE INITIALLY DEFERRED;
text
ERROR:  duplicate key value violates unique constraint "uq_users_email"

部分唯一索引与唯一约束的区别 ​

「只对一部分行做唯一」是很常见的需求，比如「未删除的用户邮箱唯一」「只有激活状态的许可证唯一」。这只能用部分唯一索引实现，唯一约束做不到：

sql
-- 部分唯一索引：只约束 deleted_at IS NULL 的行
CREATE UNIQUE INDEX uq_users_email_active
  ON users (email)
  WHERE deleted_at IS NULL;
sql
-- 然后这两行可以共存：一行已软删除，一行活跃
INSERT INTO users (email, deleted_at) VALUES ('a@x.com', now());   -- 成功
INSERT INTO users (email, deleted_at) VALUES ('a@x.com', NULL);    -- 成功
INSERT INTO users (email, deleted_at) VALUES ('a@x.com', NULL);    -- 错误！
text
ERROR:  duplicate key value violates unique constraint "uq_users_email_active"

唯一约束无法表达这个条件，因为约束语法里没有 WHERE：

sql
-- 错误！约束语法不支持 WHERE
ALTER TABLE users ADD CONSTRAINT uq_users_email UNIQUE (email) WHERE deleted_at IS NULL;

代价是部分唯一索引不能被外键引用，也不能 DEFERRABLE；如果这个列同时要作为外键目标，就只能在「完整性」与「条件唯一」之间取舍，通常做法是另建一个专门的、带 NOT NULL 的完整唯一列（例如 active_email，软删除时置 NULL）。

在线加唯一约束：CREATE UNIQUE INDEX CONCURRENTLY + USING INDEX ​

直接在大表上 ALTER TABLE ... ADD CONSTRAINT ... UNIQUE 会：持 ACCESS EXCLUSIVE 锁，并全表扫描建索引——期间该表的读写全部阻塞，线上不可接受。

标准手法是拆成三步，先并发建索引（不阻塞写），再收编为约束（此时只做一次极短的元数据加锁）：

sql
-- 第 1 步：并发建唯一索引，不阻塞读写（不可在事务块内执行）
CREATE UNIQUE INDEX CONCURRENTLY uq_orders_order_no ON orders (order_no);
sql
-- 第 2 步：如果 CONCURRENTLY 失败会留下 invalid 索引，先排查再重建
SELECT indexrelid::regclass AS index_name, indisvalid, indisready
FROM pg_index
WHERE indexrelid = 'uq_orders_order_no'::regclass;
text
        index_name        | indisvalid | indisready
--------------------------+------------+------------
 uq_orders_order_no       | t          | t
sql
-- 若 indisvalid = f，必须删掉重建（invalid 索引不参与任何查询）
DROP INDEX CONCURRENTLY uq_orders_order_no;
sql
-- 第 3 步：收编为约束，只做元数据操作，不重新扫描数据
ALTER TABLE orders ADD CONSTRAINT uq_orders_order_no UNIQUE USING INDEX uq_orders_order_no;

三点注意：

  • CREATE INDEX CONCURRENTLY 不能在显式事务块中运行，也不能与其它语句包在同一个 BEGIN 里。
  • 并发建索引期间若已存在重复值，第二步收编时会失败，报 could not create unique index ... contains duplicated values，需要先清洗数据。
  • 收编时若显式给了约束名，PostgreSQL 会把索引重命名为该约束名；未给约束名则约束沿用索引名。因此建议索引名与约束名保持一致，避免命名混乱。

若表还不存在重复数据，也可以先用 NOT VALID 的思路吗？不行——ADD CONSTRAINT ... UNIQUE 不支持 NOT VALID，唯一约束没有「先跳过历史数据」的模式：

sql
-- 错误！唯一约束不支持 NOT VALID
ALTER TABLE orders ADD CONSTRAINT uq_orders_order_no UNIQUE (order_no) NOT VALID;
text
ERROR:  UNIQUE constraints cannot be marked NOT VALID

所以大表加唯一约束只有「并发建索引 + USING INDEX」这一条在线路径。

用 DEFERRABLE 交换两行值 ​

「交换两行唯一列的值」是唯一约束的经典难题。直接写一条 UPDATE，中间状态必然冲突：

sql
CREATE TABLE seats (
  id   bigint PRIMARY KEY,
  code text UNIQUE
);

INSERT INTO seats VALUES (1, 'A'), (2, 'B');
sql
-- 错误！交换 A 与 B：中间态会出现两行 'A'（或两行 'B'），立即冲突
UPDATE seats
SET code = CASE id WHEN 1 THEN 'B' WHEN 2 THEN 'A' END
WHERE id IN (1, 2);
text
ERROR:  duplicate key value violates unique constraint "seats_code_key"
DETAIL:  Key (code)=(B) already exists.

原因是默认的唯一约束是立即检查的：语句执行过程中每一行更新后都会被验证。解法是把约束声明为可延迟，然后在事务里显式延迟检查，让检查发生在事务提交前、所有行都已更新完之后：

sql
-- 声明为可延迟，但默认仍然立即检查
ALTER TABLE seats DROP CONSTRAINT seats_code_key;
ALTER TABLE seats ADD CONSTRAINT uq_seats_code UNIQUE (code)
  DEFERRABLE INITIALLY IMMEDIATE;
sql
-- 事务内把该约束的检查延迟到提交时
BEGIN;
SET CONSTRAINTS uq_seats_code DEFERRED;

UPDATE seats
SET code = CASE id WHEN 1 THEN 'B' WHEN 2 THEN 'A' END
WHERE id IN (1, 2);

COMMIT;   -- 此刻才检查，两行已更新完毕，不再冲突

验证结果：

sql
SELECT * FROM seats ORDER BY id;
text
 id | code
----+------
  1 | B
  2 | A
(2 rows)

几种声明方式的对照：

声明默认检查时机事务内是否需要 SET CONSTRAINTS
UNIQUE (code)立即（每行更新后）无法延迟，交换必然失败
UNIQUE (code) DEFERRABLE INITIALLY IMMEDIATE立即需要，SET CONSTRAINTS ... DEFERRED
UNIQUE (code) DEFERRABLE INITIALLY DEFERRED事务提交时不需要（但整个事务期间都不检查）

实践建议用 INITIALLY IMMEDIATE：平时保持严格检查，只在确实需要交换值时临时延迟，避免「约束在一个长事务里长时间失效」带来的风险。

实战示例 ​

下面用一个「软删除 + 租户内唯一编号」的表，把本节的主要知识点串成完整流程。

第 1 步：建表，区分三种唯一需求

sql
CREATE TABLE tickets (
  id         bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  tenant_id  bigint NOT NULL,
  ticket_no  text   NOT NULL,                 -- 租户内唯一，非空
  email      text,                            -- 可为空的联系方式
  status     text   NOT NULL DEFAULT 'open',
  deleted_at timestamptz,
  created_at timestamptz NOT NULL DEFAULT now(),

  -- 组合唯一：租户内编号不重复
  CONSTRAINT uq_tickets_tenant_no UNIQUE (tenant_id, ticket_no) DEFERRABLE INITIALLY IMMEDIATE
);

-- 软删除后才允许复用编号：部分唯一索引
CREATE UNIQUE INDEX uq_tickets_active_no
  ON tickets (tenant_id, ticket_no)
  WHERE deleted_at IS NULL;

注意上面两个索引针对不同语义：uq_tickets_tenant_no 是「永久唯一」（含已删除行），uq_tickets_active_no 是「活跃行唯一」。二者并存时，冲突判定以更严格者为准。

第 2 步：验证 NULL 行为

sql
-- 多行 NULL 可以共存（NULL 互不相等）
INSERT INTO tickets (tenant_id, ticket_no, email) VALUES
  (1, 'T-1', NULL),
  (1, 'T-2', NULL),
  (1, 'T-3', NULL);

SELECT count(*) FROM tickets WHERE email IS NULL;
text
 count
-------
     3
(1 row)

第 3 步：验证组合唯一

sql
-- 正确：同编号不同租户
INSERT INTO tickets (tenant_id, ticket_no) VALUES (2, 'T-1');

-- 错误！(1, 'T-1') 组合已存在
INSERT INTO tickets (tenant_id, ticket_no) VALUES (1, 'T-1');
text
ERROR:  duplicate key value violates unique constraint "uq_tickets_tenant_no"
DETAIL:  Key (tenant_id, ticket_no)=(1, T-1) already exists.

第 4 步：用 ON CONFLICT 做幂等写入

sql
-- 重复提交同一工单不报错，返回既有 id
INSERT INTO tickets (tenant_id, ticket_no) VALUES (1, 'T-1')
ON CONFLICT ON CONSTRAINT uq_tickets_tenant_no
DO UPDATE SET status = tickets.status
RETURNING id, ticket_no, status;
text
 id | ticket_no | status
----+-----------+--------
  1 | T-1       | open
(1 row)

第 5 步：批量交换两个租户的编号（延迟检查）

sql
BEGIN;
SET CONSTRAINTS uq_tickets_tenant_no DEFERRED;

UPDATE tickets
SET tenant_id = CASE tenant_id WHEN 1 THEN 2 WHEN 2 THEN 1 END
WHERE ticket_no = 'T-1';

COMMIT;
text
COMMIT

第 6 步：确认约束与索引现状

sql
SELECT c.conname,
       c.contype,
       c.condeferrable,
       c.condeferred,
       pg_get_constraintdef(c.oid) AS def
FROM pg_constraint c
WHERE c.conrelid = 'tickets'::regclass
ORDER BY c.contype, c.conname;
text
         conname          | contype | condeferrable | condeferred |                       def
--------------------------+---------+---------------+-------------+-------------------------------------------------
 tickets_pkey             | p       | f             | f           | PRIMARY KEY (id)
 uq_tickets_tenant_no     | u       | t             | f           | UNIQUE (tenant_id, ticket_no) DEFERRABLE
(2 rows)

部分唯一索引不在 pg_constraint 中，需要在 pg_index 侧查：

sql
SELECT indexrelid::regclass AS index_name, indisunique, indisvalid
FROM pg_index
WHERE indrelid = 'tickets'::regclass AND indisunique
ORDER BY index_name;
text
        index_name        | indisunique | indisvalid
--------------------------+-------------+------------
 tickets_pkey             | t           | t
 uq_tickets_active_no     | t           | t
 uq_tickets_tenant_no     | t           | t
(3 rows)

注意事项 ​

  • UNIQUE 不保证非空。 「唯一且非空」必须显式加 NOT NULL，否则 NULL 会绕过检查。
sql
-- 危险！email 可以有任意多行 NULL
email text UNIQUE

-- 正确
email text NOT NULL UNIQUE
  • NULL 之间互不相等，所以 UNIQUE 列可以有任意多行 NULL。需要「最多一个 NULL」时用 NULLS NOT DISTINCT。
sql
-- PostgreSQL 15+
code text UNIQUE NULLS NOT DISTINCT
  • 多列唯一是组合唯一，不是各自唯一。 需要各自唯一就写多条约束。
sql
UNIQUE (a, b)          -- a 可以重复，b 也可以重复，但 (a,b) 组合不能重复
UNIQUE (a), UNIQUE (b) -- 才是各自唯一
  • 唯一约束冲突的错误码是 23505，并发场景下必须处理它，不要只处理序列化失败 40001。
sql
-- 应用层判断：SQLSTATE = 23505 → unique_violation
  • CREATE UNIQUE INDEX CONCURRENTLY 不能在事务块里执行，失败会留下 indisvalid = f 的无效索引，必须清理并重建。
sql
SELECT indexrelid::regclass FROM pg_index WHERE NOT indisvalid;
  • 部分唯一索引不能作为外键引用目标，也不能 DEFERRABLE。
sql
-- 错误！there is no unique constraint matching given keys for referenced table
CREATE UNIQUE INDEX uq_active_code ON tickets (tenant_id, ticket_no) WHERE deleted_at IS NULL;
CREATE TABLE refs (tenant_id bigint, ticket_no text,
  FOREIGN KEY (tenant_id, ticket_no) REFERENCES tickets (tenant_id, ticket_no));
  • 唯一约束不支持 NOT VALID。 大表上加唯一约束只能走「并发建索引 + USING INDEX」。
sql
-- 错误！UNIQUE constraints cannot be marked NOT VALID
ALTER TABLE big_table ADD CONSTRAINT uq_big UNIQUE (col) NOT VALID;
  • ADD CONSTRAINT ... UNIQUE 会持有 ACCESS EXCLUSIVE 锁并全表建索引，直接在大表上执行会阻塞业务。
sql
-- 不要在大表上直接执行
ALTER TABLE orders ADD CONSTRAINT uq_orders_no UNIQUE (order_no);
  • USING INDEX 复用的索引必须与约束列完全一致，列顺序不同、含表达式、或带 WHERE 都会失败。
sql
-- 索引列顺序与约束不一致时无法收编
CREATE UNIQUE INDEX uq_wrong_order ON t (b, a);
ALTER TABLE t ADD CONSTRAINT uq_t UNIQUE USING INDEX uq_wrong_order;  -- 列不匹配会报错
  • NULLS NOT DISTINCT 是索引级选项，NULLS FIRST/LAST 是列级选项，位置写错会直接语法报错。
sql
-- 正确
CREATE UNIQUE INDEX i ON t (a DESC NULLS LAST) NULLS NOT DISTINCT;
  • ON CONFLICT 必须能唯一推断出索引或约束，否则报 there is no unique or exclusion constraint matching the ON CONFLICT specification。
sql
-- 表上只有普通索引时，这句话会失败
ON CONFLICT (message) DO NOTHING
  • ON CONFLICT DO UPDATE 不能在同一条命令内二次命中同一行。
sql
-- 错误！VALUES 里带重复键
INSERT INTO counters VALUES ('hits', 1), ('hits', 2)
ON CONFLICT (name) DO UPDATE SET value = counters.value + 1;
  • 唯一约束的 NULL 行为在多列组合时是「整行判定」，不要误以为逐列独立判断。
sql
-- (1, NULL) 与 (2, NULL) 不冲突
  • DROP CONSTRAINT 默认连带删除背后的索引，若外部代码依赖索引名会一起失效。
sql
ALTER TABLE seats DROP CONSTRAINT uq_seats_code;   -- 索引 uq_seats_code 同时消失

最佳实践 ​

  • 唯一 + 非空，永远一起写。 「唯一」的业务含义几乎总是包含「必须有值」。
sql
email text NOT NULL UNIQUE
  • 需要「最多一个 NULL」时，把列改成 NOT NULL DEFAULT，比依赖 NULLS NOT DISTINCT 更可移植，也避免 NULL 语义蔓延。
sql
is_default boolean NOT NULL DEFAULT false,
CONSTRAINT uq_addr_default UNIQUE (user_id, is_default) DEFERRABLE INITIALLY IMMEDIATE
  • 明确区分「约束」与「裸索引」的用途：需要被外键引用、需要 DEFERRABLE、需要出现在 information_schema 巡检里 → 用约束；需要 WHERE 部分条件、需要表达式 → 用裸索引，并接受它不能被引用。
sql
-- 需要延迟检查的场景必须用约束
ALTER TABLE seats ADD CONSTRAINT uq_seats_code UNIQUE (code) DEFERRABLE INITIALLY IMMEDIATE;
  • 约束与索引显式命名（uq_<表>_<列>），别依赖自动命名。 自动名在多列时冗长，在后续维护与迁移脚本里很难引用。
sql
CONSTRAINT uq_users_tenant_email UNIQUE (tenant_id, email)
  • 大表加唯一约束走三步：CREATE UNIQUE INDEX CONCURRENTLY → 校验 indisvalid → ADD CONSTRAINT ... USING INDEX。
sql
CREATE UNIQUE INDEX CONCURRENTLY uq_orders_order_no ON orders (order_no);
-- 确认 indisvalid = t
ALTER TABLE orders ADD CONSTRAINT uq_orders_order_no UNIQUE USING INDEX uq_orders_order_no;
  • 幂等写入统一用 ON CONFLICT，不要靠捕获异常再重试。 前者把冲突变成正常分支，后者在并发下更容易放大压力。
sql
INSERT INTO users (email, name) VALUES ($1, $2)
ON CONFLICT (email) DO UPDATE SET name = EXCLUDED.name;
  • 拦截唯一冲突时明确捕 23505，并设置重试上限与退避，避免在真正重复的数据上无限重试。
sql
-- 伪代码
-- ON ERROR 23505 → 重试最多 3 次，指数退避；仍失败则返回业务冲突
  • 交换/轮转唯一值用 DEFERRABLE INITIALLY IMMEDIATE + SET CONSTRAINTS ... DEFERRED，把延迟范围限制在单个事务内，别声明成 INITIALLY DEFERRED。
sql
BEGIN;
SET CONSTRAINTS uq_seats_code DEFERRED;
UPDATE seats SET code = CASE id WHEN 1 THEN 'B' WHEN 2 THEN 'A' END WHERE id IN (1, 2);
COMMIT;
  • 软删除场景用部分唯一索引，并把「活跃唯一列」与「历史唯一列」的关系设计清楚（是否允许复用编号、是否保留永久唯一）。
sql
CREATE UNIQUE INDEX uq_users_email_active ON users (email) WHERE deleted_at IS NULL;
  • 定期巡检「该唯一却可能有重复」的列，并检查 indisvalid，避免并发建索引失败留下的无效索引被误认为生效。
sql
SELECT c.conname, c.conrelid::regclass AS tbl, c.contype, c.convalidated
FROM pg_constraint c
WHERE c.contype IN ('u', 'p')
ORDER BY c.conrelid::regclass::text, c.conname;
  • 唯一约束会参与 B-tree 插入维护，高写入表的唯一列越少越好。 每多一个唯一索引，每次写入就多一次索引维护与潜在冲突检查；业务上不必唯一的列不要顺手加 UNIQUE。

下一节

继续学习：NOT NULL / CHECK / DEFAULT

参考链接 ​