PHP小码农

Appearance

约束命名与延迟检查 ​

约束名（Constraint Name）是 DDL 与运维脚本里被引用得最频繁、却最容易被忽略的标识符：报错信息里带着它、ALTER TABLE ... DROP CONSTRAINT 需要它、巡检 SQL 靠它定位问题。而「延迟检查」（Deferrable Constraint）决定了一条约束到底在语句结束时校验，还是拖到事务提交时才校验。本节把命名规则、pg_constraint 元数据、RENAME CONSTRAINT 的连带效应，以及延迟检查的完整语义一次讲透。

前置知识

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

基础概念 ​

约束名是「表内唯一」的标识符，不是全局唯一 ​

很多人下意识以为约束名在全库唯一，事实并非如此。官方在 pg_constraint 的列说明里写得很直白：

text
conname name
    约束名（not necessarily unique!）

约束名的唯一性范围是单个表内（同表内两个约束不能重名），不同表之间可以有同名约束。这一点直接决定了 SET CONSTRAINTS 与 ALTER TABLE ... DROP CONSTRAINT 的行为差异：

语句名字的作用域同名约束的影响
SET CONSTRAINTS c DEFERRED搜索路径中所有表的同名约束全部一起生效
ALTER TABLE t ALTER CONSTRAINT c ...表 t 内只影响 t 上的那一个
ALTER TABLE t DROP CONSTRAINT c表 t 内只影响 t 上的那一个

也就是说，SET CONSTRAINTS 是唯一一个按名字跨表匹配的约束语句。

不给名字，PostgreSQL 会自动生成 ​

创建约束时若省略 CONSTRAINT 名字，PostgreSQL 按固定的模板自动命名。这套模板与约束类型、列数强相关：

约束类型默认名模板实例
CHECK（列级，单列）<表>_<列>_checkaccounts_balance_check
CHECK（表级或多列）<表>_checkorders_check
NOT NULL（PostgreSQL 18+）<表>_<列>_not_nullusers_email_not_null
UNIQUE<表>_<列>_keyusers_email_key
PRIMARY KEY<表>_pkeyusers_pkey
FOREIGN KEY<表>_<列>_fkeyorders_user_id_fkey
EXCLUDE<表>_<列>_exclbookings_ex_room_id_during_excl

自动名可读性尚可，但不可预测：一旦表名、列名或 PostgreSQL 版本变化，生成的名字就可能与你的迁移脚本对不上。

「延迟检查」解决的是顺序问题，不是性能问题 ​

延迟约束（Deferrable Constraint）把校验时机从「语句／行处理时」推迟到「事务提交时」。它要解决的是中间态必然违反约束的场景：

text
问题：循环引用
  employees.manager_id → employees.id
  员工 10 的上司是 11，员工 11 的上司是 10
  无论先插哪一行，另一行都还不存在 → 立即检查必然失败

解法：把检查推到 COMMIT 时
  事务结束时两行都在 → 检查通过

它不减少任何校验工作量，只是改变了校验的时间点。

延迟检查只对四类约束有效 ​

这是最容易被误解的一点。官方原文：

text
Currently, only UNIQUE, PRIMARY KEY, REFERENCES (foreign key), and EXCLUDE
constraints are affected by this setting. NOT NULL and CHECK constraints are
always checked immediately when a row is inserted or modified (not at the end
of the statement). Uniqueness and exclusion constraints that have not been
declared DEFERRABLE are also checked immediately.
约束类型能否 DEFERRABLE说明
PRIMARY KEY✅ 能底层是唯一索引
UNIQUE✅ 能底层是唯一索引
FOREIGN KEY✅ 能可配 ON DELETE 动作
EXCLUDE✅ 能底层是排他索引
CHECK❌ 不能永远是行级立即检查
NOT NULL❌ 不能永远是行级立即检查

给 CHECK 写 DEFERRABLE 会直接报语法错误，连创建都过不去。

语法与用法 ​

text
-- 1. 显式命名（列级约束：写在列定义之后）
column_name data_type [ CONSTRAINT constraint_name ] { NOT NULL | UNIQUE | ... }

-- 2. 显式命名（表级约束）
[ CONSTRAINT constraint_name ] { CHECK (...) | UNIQUE (...) | PRIMARY KEY (...) | FOREIGN KEY (...) | EXCLUDE ... }

-- 3. 延迟声明（追加在约束定义之后）
[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]

-- 4. 事务内临时切换（只影响当前事务）
SET CONSTRAINTS { ALL | name [, ...] } { DEFERRED | IMMEDIATE }

-- 5. 事后重命名（会连带重命名底层索引）
ALTER TABLE [ IF EXISTS ] [ ONLY ] name [ * ] RENAME CONSTRAINT c1 TO c2;

-- 6. 事后修改延迟属性
ALTER TABLE name ALTER CONSTRAINT c
  [ DEFERRABLE | NOT DEFERRABLE ]
  [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
  [ ENFORCED | NOT ENFORCED ];

-- 7. 把一个已有唯一索引「升格」为约束
[ CONSTRAINT constraint_name ] { UNIQUE | PRIMARY KEY } USING INDEX index_name
  [ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]

最简可运行示例：

sql
CREATE TABLE accounts (
  id      bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  balance numeric NOT NULL CHECK (balance >= 0),          -- 自动名：accounts_balance_check
  email   text    NOT NULL,                                -- 自动名：accounts_email_not_null
  CONSTRAINT uq_accounts_email UNIQUE (email),              -- 显式命名
  CONSTRAINT ck_accounts_balance_upper CHECK (balance < 1000000)
);
text
CREATE TABLE

查看生成的约束名：

sql
\d accounts
text
                           Table "public.accounts"
 Column  |  Type   | Collation | Nullable |           Default
---------+---------+-----------+----------+------------------------------
 id      | bigint  |           | not null | generated always as identity
 balance | numeric |           | not null |
 email   | text    |           | not null |
Indexes:
    "accounts_pkey" PRIMARY KEY, btree (id)
    "uq_accounts_email" UNIQUE CONSTRAINT, btree (email)
Check constraints:
    "accounts_balance_check" CHECK (balance >= 0::numeric)
    "ck_accounts_balance_upper" CHECK (balance < 1000000::numeric)
Not-null constraints:
    "accounts_email_not_null" NOT NULL "email"
    "accounts_balance_not_null" NOT NULL "balance"

输出把「自动名」与「显式名」并列呈现——这正是本节要教你在写脚本时避开的那种不确定性。

详细说明 ​

默认命名规则：五类约束的自动名从哪来 ​

自动名由 ChooseConstraintName() 生成，规则是「表名 + 关键列名 + 类型后缀」。理解它有两个现实意义：读懂报错信息、写出可预测的 DROP 语句。

逐个类型验证：

sql
CREATE TABLE naming_demo (
  id       bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,   -- naming_demo_pkey
  tenant   bigint NOT NULL,
  code     text   NOT NULL,
  amount   numeric NOT NULL CHECK (amount > 0),                    -- naming_demo_amount_check
  UNIQUE (tenant, code),                                           -- naming_demo_tenant_code_key
  CHECK (amount < 999999)                                          -- naming_demo_check（表级）
);
sql
SELECT conname, contype, pg_get_constraintdef(oid) AS def
FROM pg_constraint
WHERE conrelid = 'naming_demo'::regclass
ORDER BY contype, conname;
text
      conname       | contype |                        def
--------------------+---------+----------------------------------------------------
 naming_demo_amount | c       | CHECK (amount < 999999::numeric)
 naming_demo_amount | c       | CHECK (amount > 0::numeric)
 naming_demo_pkey   | p       | PRIMARY KEY (id)
 naming_demo_tenant | u       | UNIQUE (tenant, code)
(4 rows)

（上面为便于对齐，把名字截断了；真实名字分别是 naming_demo_check、naming_demo_amount_check。）

注意三个细节：

text
1. 列级单列 CHECK  →  <表>_<列>_check      naming_demo_amount_check
2. 表级 / 多列 CHECK →  <表>_check          naming_demo_check
3. 多列 UNIQUE      →  <表>_<列1>_<列2>_key  naming_demo_tenant_code_key

命名冲突会自动编号。 如果模板生成的候选名在该表内已被占用，PostgreSQL 追加 1、2……直到不冲突：

sql
CREATE TABLE clash_demo (
  a int,
  b int,
  CONSTRAINT clash_demo_amount_check CHECK (a > 0)   -- 先占用了这个名字
);
sql
-- 再加一个 CHECK，默认名与上面冲突 → 自动变成 clash_demo_amount_check1
ALTER TABLE clash_demo ADD CHECK (b > 0);
sql
SELECT conname FROM pg_constraint
WHERE conrelid = 'clash_demo'::regclass AND contype = 'c' ORDER BY conname;
text
           conname
-----------------------------
 clash_demo_amount_check
 clash_demo_amount_check1
(2 rows)

这就是「同一个库里出现 xxx_check1」的成因。它不会报错，但会让脚本里的硬编码名字失效。

显式命名：让迁移脚本与巡检 SQL 可引用 ​

自动名的问题不在「不好看」，而在你不该依赖一个你无法完全预测的名字。官方在 ddl-constraints 里给出的推荐写法就是显式命名：

sql
CREATE TABLE products (
  product_no integer,
  name       text,
  price      numeric CHECK (price > 0)                                  -- 默认名
);

CREATE TABLE products_v2 (
  product_no integer,
  name       text,
  price      numeric CONSTRAINT positive_price CHECK (price > 0),        -- 显式名
  discounted_price numeric,
  CONSTRAINT valid_discount CHECK (price > discounted_price)
);

落地时推荐一套可读的前缀约定，把「类型」和「语义」都编进名字：

sql
CREATE TABLE orders (
  id         bigint GENERATED ALWAYS AS IDENTITY,
  order_no   text   NOT NULL,
  user_id    bigint NOT NULL,
  total      numeric NOT NULL,
  status     text   NOT NULL,
  created_at timestamptz NOT NULL DEFAULT now(),

  CONSTRAINT pk_orders          PRIMARY KEY (id),
  CONSTRAINT uq_orders_order_no UNIQUE (order_no),
  CONSTRAINT ck_orders_total    CHECK (total >= 0),
  CONSTRAINT ck_orders_status   CHECK (status IN ('pending', 'paid', 'shipped', 'cancelled'))
);
text
CREATE TABLE
前缀约束类型示例
pk_主键pk_orders
uq_唯一uq_orders_order_no
fk_外键fk_orders_user
ck_检查ck_orders_total
ex_排他ex_bookings_room_period

有了稳定名字，迁移脚本才能安全地做幂等变更：

sql
-- 幂等：先删后加，名字固定才可重复执行
ALTER TABLE orders DROP CONSTRAINT IF EXISTS ck_orders_total;
ALTER TABLE orders ADD  CONSTRAINT ck_orders_total CHECK (total >= 0);
text
ALTER TABLE
ALTER TABLE

名字里不要塞引号与特殊字符

约束名遵循标识符规则。想用大写或关键字就必须加双引号，而带引号的名字在任何后续引用里都得一路带引号，属于给自己挖坑。统一小写 + 下划线是最省事的选择。

用 pg_constraint 查约束元数据 ​

约束的权威来源是系统目录 pg_constraint。巡检、审计、生成 DDL 全都靠它。关键列：

列含义
conname约束名（表内唯一，非全库唯一）
contype类型：c 检查 / f 外键 / n 非空 / p 主键 / u 唯一 / t 约束触发器 / x 排他
condeferrable是否声明为可延迟
condeferred是否默认延迟（即 INITIALLY DEFERRED）
conenforced是否强制（PostgreSQL 18 起）
convalidated是否已校验（与 NOT VALID 对应）
conrelid约束所属表
conindid支撑该约束的索引（唯一／主键／排他）
conparentid分区表的父约束 OID
coninhcount继承计数（非零时不能删除或重命名）
confrelid外键引用的父表
conkey / confkey本地列号 / 引用列号数组
conbin检查表达式（内部形式，建议改用 pg_get_constraintdef()）

一条把「约束全貌」打印出来的巡检 SQL：

sql
SELECT c.conname,
       c.contype,
       c.condeferrable,
       c.condeferred,
       c.convalidated,
       pg_get_constraintdef(c.oid) AS definition
FROM pg_constraint c
WHERE c.conrelid = 'orders'::regclass
ORDER BY c.contype, c.conname;
text
     conname      | contype | condeferrable | condeferred | convalidated |              definition
------------------+---------+---------------+-------------+--------------+--------------------------------------
 ck_orders_status | c       | f             | f           | t            | CHECK (status = ANY (ARRAY[...]))
 ck_orders_total  | c       | f             | f           | t            | CHECK (total >= 0::numeric)
 pk_orders        | p       | f             | f           | t            | PRIMARY KEY (id)
 uq_orders_order  | u       | f             | f           | t            | UNIQUE (order_no)
(4 rows)

不要直接读 conbin。 官方在 pg_constraint 文档里明确提示：

text
conbin pg_node_tree
    如果是一个检查约束，这里是其表达式的内部形式。
    建议使用 pg_get_constraintdef() 提取检查约束的定义。

pg_get_constraintdef() 的输出可直接拼回 DDL：

sql
SELECT format('ALTER TABLE %s ADD CONSTRAINT %I %s;',
              conrelid::regclass, conname, pg_get_constraintdef(oid)) AS ddl
FROM pg_constraint
WHERE conrelid = 'orders'::regclass AND contype = 'c';
text
                                            ddl
---------------------------------------------------------------------------------------------
 ALTER TABLE orders ADD CONSTRAINT "ck_orders_status" CHECK (status = ANY (ARRAY['pending'::text, 'paid'::text, 'shipped'::text, 'cancelled'::text]));
 ALTER TABLE orders ADD CONSTRAINT "ck_orders_total" CHECK (total >= 0::numeric);
(2 rows)

顺带一条一致性校验

pg_class.relchecks 必须等于该表 contype = 'c' 的条目数。二者不一致通常意味着目录损坏，是需要立刻排查的信号。

RENAME CONSTRAINT：连带重命名底层索引 ​

重命名约束的语法很简洁：

text
ALTER TABLE [ IF EXISTS ] [ ONLY ] name [ * ]
    RENAME CONSTRAINT constraint_name TO new_constraint_name;

关键行为在官方文档里有一句原文：

text
When renaming a constraint that has an underlying index, the index is renamed
as well. There is no effect on the stored data.

也就是说：主键、唯一、排他约束重命名时，它们背后的索引会一起改名，而数据文件一个字节都不动。外键与检查约束没有支撑索引，改名只动 pg_constraint。

sql
CREATE TABLE users (
  id    bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  email text NOT NULL,
  CONSTRAINT uq_users_email_temp UNIQUE (email)
);

先确认约束名与索引名的对应关系：

sql
SELECT c.conname AS constraint_name, i.relname AS index_name
FROM pg_constraint c
JOIN pg_class i ON i.oid = c.conindid
WHERE c.conrelid = 'users'::regclass AND c.contype = 'u';
text
   constraint_name    |    index_name
----------------------+-------------------
 uq_users_email_temp  | uq_users_email_temp
(1 row)

执行重命名：

sql
ALTER TABLE users RENAME CONSTRAINT uq_users_email_temp TO uq_users_email;
text
ALTER TABLE

再查一次：

sql
SELECT c.conname AS constraint_name, i.relname AS index_name
FROM pg_constraint c
JOIN pg_class i ON i.oid = c.conindid
WHERE c.conrelid = 'users'::regclass AND c.contype = 'u';
text
 constraint_name |   index_name
-----------------+-----------------
 uq_users_email  | uq_users_email
(1 row)

索引用的是同名索引——约束名与索引名被一起改了。这也是为什么你不该把 uq_... 改名成某个与已有索引撞车的名字。

继承来的约束不能删也不能改名

pg_constraint.coninhcount 非零表示该约束继承自父表。官方原文：

text
A constraint with a nonzero number of ancestors cannot be dropped nor renamed.

要在子表上单独改名，必须先在父表层面处理，或让该约束不再被继承。

DEFERRABLE 的三种声明与生效范围 ​

延迟属性由两个开关组合而成，共三种有效状态：

声明默认行为能否用 SET CONSTRAINTS 改目标
NOT DEFERRABLE立即检查❌ 不能严格即时的完整性
DEFERRABLE INITIALLY IMMEDIATE立即检查✅ 可切成 DEFERRED默认严格，按需放宽
DEFERRABLE INITIALLY DEFERRED延迟到提交✅ 可切成 IMMEDIATE默认宽松

两个布尔字段在 pg_constraint 里的映射：

text
condeferrable = true          ←→  写了 DEFERRABLE
condeferred   = true          ←→  写了 INITIALLY DEFERRED

NOT DEFERRABLE                    → condeferrable=f, condeferred=f
DEFERRABLE INITIALLY IMMEDIATE    → condeferrable=t, condeferred=f
DEFERRABLE INITIALLY DEFERRED     → condeferrable=t, condeferred=t

NOT DEFERRABLE 的约束对 SET CONSTRAINTS 完全免疫——不能改成延迟：

sql
CREATE TABLE strict_demo (
  id  bigint PRIMARY KEY,                       -- 默认 NOT DEFERRABLE
  ref bigint
);

BEGIN;
SET CONSTRAINTS ALL DEFERRED;                   -- 对它无效
text
BEGIN
SET CONSTRAINTS

事务内查 pg_constraint 会看到它的 condeferrable 仍是 f，检查依然在语句结束时立刻发生。

事后修改用一个语句即可，但要理解代价：

sql
ALTER TABLE orders ALTER CONSTRAINT fk_orders_user
  DEFERRABLE INITIALLY IMMEDIATE;
text
ALTER TABLE

ALTER CONSTRAINT 修改延迟属性本身是纯元数据操作，不重写表。但 DEFERRABLE INITIALLY DEFERRED 会让此后每个涉及该约束的事务都多付出「事务末集中校验」的开销——这是一个需要显式评估的性能取舍。

哪些约束类型可以被延迟 ​

DEFERRABLE 不是「想加就加」的开关，它只对底层是索引的约束有意义。把四类可延迟约束逐一验证：

sql
CREATE TABLE parent_demo (id bigint PRIMARY KEY);

CREATE TABLE defer_demo (
  id      bigint,
  ref_id  bigint,
  room    int,
  during  int4range,

  -- 主键：可延迟
  CONSTRAINT pk_defer_demo PRIMARY KEY (id)
    DEFERRABLE INITIALLY IMMEDIATE,

  -- 外键：可延迟
  CONSTRAINT fk_defer_parent FOREIGN KEY (ref_id) REFERENCES parent_demo (id)
    DEFERRABLE INITIALLY DEFERRED,

  -- 排他：可延迟（需 btree_gist 支持等值操作符）
  CONSTRAINT ex_defer_room EXCLUDE USING gist (room WITH =, during WITH &&)
    DEFERRABLE INITIALLY IMMEDIATE
);
text
CREATE TABLE

给 CHECK 或 NOT NULL 加 DEFERRABLE 会直接语法报错：

sql
-- 错误！CHECK 约束不支持 DEFERRABLE
CREATE TABLE bad_check (
  amount numeric CONSTRAINT ck_amount CHECK (amount > 0) DEFERRABLE
);
text
ERROR:  syntax error at or near "DEFERRABLE"
LINE 2:   amount numeric CONSTRAINT ck_amount CHECK (amount > 0) DEFERRABLE

约束触发器也受 SET CONSTRAINTS 控制。 由 CREATE CONSTRAINT TRIGGER 建立的触发器，其触发时机同样可以声明为 DEFERRABLE INITIALLY DEFERRED，并与约束共享同一套延迟机制：

sql
CREATE CONSTRAINT TRIGGER trg_audit
  AFTER INSERT ON orders
  DEFERRABLE INITIALLY DEFERRED
  FOR EACH ROW EXECUTE FUNCTION log_order_change();
text
CREATE TRIGGER

延迟不改变「冲突时如何报错」

把约束改成可延迟，只是把报错从语句时挪到提交时。官方文档同时提醒：ON DELETE RESTRICT 这类立即动作不受延迟影响——因为它由外键动作触发，而不是由检查时机决定。

SET CONSTRAINTS 的追溯语义 ​

SET CONSTRAINTS 有两个少有人知的语义细节，都是官方明文规定的。

细节一：从 DEFERRED 切回 IMMEDIATE 时是追溯的。

text
When SET CONSTRAINTS changes the mode of a constraint from DEFERRED to
IMMEDIATE, the new mode takes effect retroactively: any outstanding data
modifications that would have been checked at the end of the transaction are
instead checked during the execution of the SET CONSTRAINTS command. If any
such constraint is violated, the SET CONSTRAINTS fails (and does not change
the constraint mode).

原文最后半句尤其重要：校验失败时，SET CONSTRAINTS 本身报错，并且不会改变约束模式。 这意味着可以把它当成一个「提前验收」的探针：

sql
BEGIN;
SET CONSTRAINTS fk_orders_user DEFERRED;

INSERT INTO orders (id, order_no, user_id, total, status)
VALUES (9001, 'ORD-9001', 999, 100, 'pending');   -- user 999 不存在，暂不校验

-- 想在任何写操作前确认「当前事务状态是否已一致」：
SET CONSTRAINTS fk_orders_user IMMEDIATE;          -- 立刻校验，会在这里失败
text
ERROR:  insert or update on table "orders" violates foreign key constraint "fk_orders_user"
DETAIL:  Key (user_id)=(999) is not present in table "users".

失败后整个事务已经不可用，必须 ROLLBACK。把这一步当作「提交前的强制体检」是合法用法。

细节二：事务块之外执行只发一条警告。

text
SET CONSTRAINTS ... outside a transaction block emits a warning and otherwise
has no effect.
sql
-- 注意：不在 BEGIN 里，因此没有任何效果
SET CONSTRAINTS ALL DEFERRED;
text
WARNING:  SET CONSTRAINTS can only be used in transaction blocks
SET CONSTRAINTS

细节三：名字匹配是跨表、按搜索路径的。 SET CONSTRAINTS fk_x DEFERRED 会命中当前搜索路径里所有名为 fk_x 的约束；名字未限定 schema 时，一旦在某个 schema 里匹配到就不再往后找。想精确控制，必须配合前面提到的「显式命名 + 全局唯一前缀」策略。

sql
-- 推荐：ALL + 显式名字，语义最明确
BEGIN;
SET CONSTRAINTS ALL DEFERRED;
-- ... 批量操作 ...
COMMIT;
text
BEGIN
SET CONSTRAINTS
COMMIT

经典场景：延迟唯一约束交换两行值 ​

「把两行的某个唯一列互换」是延迟约束最漂亮的用法。UNIQUE 默认立即检查时，任何一次 UPDATE 都会撞上约束：

sql
CREATE TABLE slots (
  id     int PRIMARY KEY,
  label  text NOT NULL,
  CONSTRAINT uq_slots_label UNIQUE (label)         -- 默认 NOT DEFERRABLE
);
sql
INSERT INTO slots (id, label) VALUES (1, 'A'), (2, 'B');
sql
-- 错误！想交换 A / B，第一步就把 label 改成已存在的 'B'
UPDATE slots SET label = 'B' WHERE id = 1;
text
ERROR:  duplicate key value violates unique constraint "uq_slots_label"
DETAIL:  Key (label)=(B) already exists.

把约束声明为可延迟（或事后 ALTER CONSTRAINT），就能在一个事务里完成交换：

sql
ALTER TABLE slots ALTER CONSTRAINT uq_slots_label
  DEFERRABLE INITIALLY IMMEDIATE;
sql
BEGIN;
SET CONSTRAINTS uq_slots_label DEFERRED;

UPDATE slots SET label = 'B' WHERE id = 1;   -- 中间态：两行都是 'B'
UPDATE slots SET label = 'A' WHERE id = 2;   -- 事务末：回到合法状态

COMMIT;                                       -- 此刻才校验，通过
text
BEGIN
SET CONSTRAINTS
UPDATE 1
UPDATE 1
COMMIT
sql
SELECT id, label FROM slots ORDER BY id;
text
 id | label
----+-------
  1 | B
  2 | A
(2 rows)

为什么不在中间态用临时值绕开

常见「土办法」是先把 label 改成 'A_tmp' 再改回，靠两次 UPDATE 避开冲突。它同样可行，但会污染数据、多写一次 WAL、并且在对 label 有 CHECK 或外部依赖时失效。延迟唯一约束是语义上正确的做法，中间态只在事务内可见，外界永远看不到。

USING INDEX 与 PostgreSQL 18 的 NOT ENFORCED ​

USING INDEX 让你把一个已经存在的唯一索引直接升格为约束，而不是先建约束再等它去建索引。这在「先并发建索引、再加约束」的在线变更流程里是标准做法：

text
[ CONSTRAINT constraint_name ]
{ UNIQUE | PRIMARY KEY } USING INDEX index_name
[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
sql
-- 第一步：并发建唯一索引，避免长时间锁表
CREATE UNIQUE INDEX CONCURRENTLY uq_products_sku_idx ON products (sku);
text
CREATE INDEX
sql
-- 第二步：用这个索引直接生成约束（不重建索引）
ALTER TABLE products
  ADD CONSTRAINT uq_products_sku UNIQUE USING INDEX uq_products_sku_idx;
text
ALTER TABLE
sql
SELECT conname, condeferrable, conindid::regclass AS backing_index
FROM pg_constraint
WHERE conrelid = 'products'::regclass AND contype = 'u';
text
    conname     | condeferrable | backing_index
----------------+---------------+------------------------
 uq_products_sku| f             | uq_products_sku_idx
(1 row)

用这条路径时，延迟属性可以在 USING INDEX 子句上顺带声明，不必事后 ALTER CONSTRAINT。

sql
ALTER TABLE products
  ADD CONSTRAINT uq_products_sku UNIQUE USING INDEX uq_products_sku_idx
  DEFERRABLE INITIALLY IMMEDIATE;
text
ALTER TABLE

PostgreSQL 18 的 NOT ENFORCED。 PostgreSQL 18 起，CHECK 与外键约束可以声明为「不强制」，仅作为元数据登记给外部工具或文档使用：

sql
CREATE TABLE ledger (
  id       bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  debit    numeric NOT NULL,
  credit   numeric NOT NULL,
  -- 只登记语义，不真正强制（PostgreSQL 18+）
  CONSTRAINT ck_ledger_balanced CHECK (debit = credit) NOT ENFORCED
);
text
CREATE TABLE

这类约束在 pg_constraint.conenforced 里记为 false：

sql
SELECT conname, contype, conenforced, convalidated
FROM pg_constraint
WHERE conrelid = 'ledger'::regclass AND contype = 'c';
text
        conname         | contype | conenforced | convalidated
------------------------+---------+-------------+--------------
 ck_ledger_balanced     | c       | f           | t
(1 row)

也可以事后切换：

sql
ALTER TABLE ledger ALTER CONSTRAINT ck_ledger_balanced ENFORCED;
text
ALTER TABLE

不要把 NOT ENFORCED 当成「临时关掉约束」

它不是 DISABLE TRIGGER 的替代品：NOT ENFORCED 是持久化的声明（会被 pg_dump 带出去），语义是「这条规则不由数据库执行」。想在导入期临时放宽，请用 DEFERRABLE + SET CONSTRAINTS，或 ALTER TABLE ... DISABLE TRIGGER。

实战示例 ​

场景一：给存量表补齐规范化约束名 ​

已有系统里堆满了 orders_check1、orders_amount_check2 这类自动名。写一段脚本生成重命名语句，人工审核后再执行：

sql
SELECT format('ALTER TABLE %s RENAME CONSTRAINT %I TO ck_%s_%s;',
              c.conrelid::regclass,
              c.conname,
              c.conrelid::regclass,
              a.attname) AS rename_stmt
FROM pg_constraint c
JOIN unnest(c.conkey) WITH ORDINALITY AS k(attnum, ord) ON true
JOIN pg_attribute a ON a.attrelid = c.conrelid AND a.attnum = k.attnum
WHERE c.conrelid = 'orders'::regclass
  AND c.contype = 'c'
  AND c.conname NOT LIKE 'ck_%'
  AND k.ord = 1
ORDER BY c.conname;
text
                                  rename_stmt
----------------------------------------------------------------------------------
 ALTER TABLE orders RENAME CONSTRAINT orders_amount_check TO ck_orders_amount;
 ALTER TABLE orders RENAME CONSTRAINT orders_check1 TO ck_orders_status;
(2 rows)

场景二：批量导入时临时放宽外键 ​

导入期间父子表的插入顺序无法保证，用「只在本事务内延迟」的方式解决，导入结束后自动恢复严格模式：

sql
BEGIN;

-- 只对本事务生效；事务结束自动恢复 INITIALLY IMMEDIATE
SET CONSTRAINTS fk_orders_user DEFERRED;

COPY orders (id, order_no, user_id, total, status)
  FROM '/tmp/orders.csv' WITH (FORMAT csv, HEADER true);

COPY users (id, email)
  FROM '/tmp/users.csv' WITH (FORMAT csv, HEADER true);

COMMIT;   -- 此刻统一校验外键，全部通过才落库
text
BEGIN
SET CONSTRAINTS
COPY 50000
COPY 1200
COMMIT

场景三：审计「谁是延迟约束」 ​

上线前检查一遍，避免有人无意中把关键唯一约束声明成 INITIALLY DEFERRED：

sql
SELECT c.conrelid::regclass AS table_name,
       c.conname,
       c.contype,
       c.condeferrable,
       c.condeferred,
       c.conenforced
FROM pg_constraint c
JOIN pg_namespace n ON n.oid = c.connamespace
WHERE n.nspname NOT IN ('pg_catalog', 'information_schema')
  AND (c.condeferrable OR NOT c.conenforced)
ORDER BY c.conrelid::regclass::text, c.conname;
text
 table_name |    conname     | contype | condeferrable | condeferred | conenforced
------------+----------------+---------+---------------+-------------+-------------
 defer_demo | fk_defer_parent| f       | t             | t           | t
 defer_demo | pk_defer_demo  | p       | t             | f           | t
 ledger     | ck_ledger_...  | c       | f             | f           | f
(3 rows)

场景四：用 SET CONSTRAINTS 做事务内早期自检 ​

在长事务里，与其等到 COMMIT 才发现约束冲突、丢失全部工作，不如在中途主动校验一次：

sql
BEGIN;

SET CONSTRAINTS ALL DEFERRED;

INSERT INTO orders (id, order_no, user_id, total, status)
VALUES (9100, 'ORD-9100', 1, 200, 'pending');

-- 中途自检：若外键悬空，会在这里立刻报错，而不是拖到 COMMIT
SET CONSTRAINTS ALL IMMEDIATE;

-- 通过后继续做后续工作
INSERT INTO order_items (order_id, sku, qty) VALUES (9100, 'SKU-1', 2);

COMMIT;
text
BEGIN
SET CONSTRAINTS
INSERT 0 1
SET CONSTRAINTS
INSERT 0 1
COMMIT

注意事项 ​

  • 错误！给 CHECK 或 NOT NULL 写 DEFERRABLE。 这两类约束永远是行级立即检查，写上去直接 syntax error。
  • 错误！依赖自动生成的约束名写迁移脚本。 xxx_check1、xxx_check2 这类编号名会随建表顺序漂移，脚本在另一个环境执行时可能引用到不存在的名字。请始终显式命名。
  • 不推荐直接用 pg_constraint.conbin 解析检查表达式。官方明确建议改用 pg_get_constraintdef()。
  • 错误！在 SET CONSTRAINTS 报错后继续用同一个事务。 报错触发后事务已进入 aborted 状态，后续语句全部被拒，只能 ROLLBACK。
  • 不推荐在事务块外执行 SET CONSTRAINTS。它只发一条 WARNING，随后什么都不做，容易让你误以为已经生效。
  • 错误！对 coninhcount 非零的继承约束执行 DROP 或 RENAME。 官方规定这类约束「不能删除也不能重命名」，必须先处理父表。
  • 不推荐把 DEFERRABLE INITIALLY DEFERRED 当默认选项。它让所有事务都承担事务末集中校验的成本，只应在确有必要时逐条声明。
  • 错误！重命名唯一／主键约束后仍按旧索引名查询。 底层索引会跟着改名，任何写死索引名的脚本都要同步更新。
  • 错误！把 NOT ENFORCED 当成「临时禁用约束」。 它是持久化声明，不是开关，语义完全不同。

最佳实践 ​

  • 统一采用前缀命名约定：pk_ / uq_ / fk_ / ck_ / ex_。让类型与语义都体现在名字里。

    sql
    CONSTRAINT ck_orders_total CHECK (total >= 0)

  • 把约束名当作公开 API 管理。 它是迁移脚本、监控、告警共同的依赖点，改名视为破坏性变更，走评审流程。

  • 默认用 DEFERRABLE INITIALLY IMMEDIATE 而不是 INITIALLY DEFERRED。 保持默认严格，只在确实需要的事务里临时放宽。

    sql
    ALTER TABLE orders ALTER CONSTRAINT fk_orders_user DEFERRABLE INITIALLY IMMEDIATE;

  • 在线给大表加唯一约束，走「CREATE UNIQUE INDEX CONCURRENTLY → ADD ... USING INDEX」两步走。 避免长事务锁表。

    sql
    CREATE UNIQUE INDEX CONCURRENTLY uq_x_idx ON t (col);
ALTER TABLE t ADD CONSTRAINT uq_x UNIQUE USING INDEX uq_x_idx;

  • 用 pg_get_constraintdef() 生成 DDL，不要手写检查表达式。 它保证与目录完全一致。

    sql
    SELECT pg_get_constraintdef(oid) FROM pg_constraint WHERE conname = 'ck_orders_total';

  • 定期用 pg_constraint 巡检三类异常：自动生成的约束名、非预期的可延迟约束、以及 conenforced = false 的非强制约束。

  • 导入／迁移这类「中间态必然违反」的场景，优先 SET CONSTRAINTS ... DEFERRED 而不是删约束再加回。 后者期间的完整性是真空的。

  • 重命名约束前后，同步核对 conindid 指向的索引名。 保证监控与脚本引用一致。

下一节

继续学习：B-Tree 索引原理——唯一约束与主键背后都是唯一 B-tree 索引，理解它的页结构、分裂与查找代价，才能真正理解约束检查的成本。

参考链接 ​