PHP小码农

Appearance

NOT NULL / CHECK / DEFAULT ​

NOT NULL、CHECK、DEFAULT 是三种「不建索引」的列级约束：它们不帮你加速查询，但决定了数据能不能被写进来。本节讲清 NOT NULL 与 CHECK 的语义等价与性能差异、CHECK 里 NULL 放行的关键陷阱、NOT VALID 在线加约束的技巧，以及 DEFAULT 的求值时机与 PostgreSQL 11 之后加列不再重写全表的优化。

前置知识

阅读本节前，建议先了解：UNIQUE 唯一约束

基础概念 ​

这三种约束的共同点是：只在写入/更新时校验，不产生任何索引。这也是它们与 UNIQUE / PRIMARY KEY 最本质的区别——后两者必须维护一个 B-tree 索引，而它们只需要存一段元数据。

概念术语说明
非空约束NOT NULL列值不得为 NULL，是元数据级检查
非空（无约束条目）NOT NULL（无 pg_constraint 行）18 之前只存在 pg_attribute.attnotnull
检查约束CHECK每行必须让布尔表达式为真
默认值DEFAULT插入未提供该列时的填充表达式
未校验约束NOT VALID只对新数据生效，跳过历史数据校验
校验约束VALIDATE CONSTRAINT补做历史数据校验，不阻塞读写
三值逻辑Three-Valued Logic布尔结果可为 TRUE / FALSE / UNKNOWN
不继承检查NO INHERIT子表不继承父表的该检查约束

三者在系统表里的可见性不同：

约束是否出现在 pg_constraintcontype是否可 NOT VALID是否可 DEFERRABLE
NOT NULLPostgreSQL 18 起有记录n否否
CHECK是（列级/表级都会生成）c✅ 是❌ 否
DEFAULT否（不是约束）———

DEFAULT 严格来说不是约束，它只存在于 pg_attribute.adsrc（由 pg_get_expr(adbin, adrelid) 还原）里，在 pg_constraint 中完全查不到。

text
NOT NULL / CHECK                 →  pg_constraint 中有记录（NOT NULL 需 PostgreSQL 18+）
DEFAULT                          →  只在 pg_attrdef（pg_attribute 侧）中，不属于约束

语法与用法 ​

text
-- 列约束写法
column_name data_type [ CONSTRAINT constraint_name ]
  { NOT NULL [ NO INHERIT ]
  | NULL
  | DEFAULT default_expr
  | CHECK ( expression ) [ NO INHERIT ] }

-- 表约束写法（CHECK 可以引用多列，NOT NULL/DEFAULT 不行）
[ CONSTRAINT constraint_name ] CHECK ( expression ) [ NO INHERIT ]

-- 已有表上增删
ALTER TABLE table_name ALTER COLUMN column_name { SET | DROP } NOT NULL;
ALTER TABLE table_name ALTER COLUMN column_name { SET | DROP } DEFAULT expression;
ALTER TABLE table_name ADD [ CONSTRAINT constraint_name ] CHECK ( expression ) [ NOT VALID ];
ALTER TABLE table_name VALIDATE CONSTRAINT constraint_name;
ALTER TABLE table_name DROP CONSTRAINT constraint_name [ CASCADE ];

最简可运行示例：

sql
-- 三种约束一次写全
CREATE TABLE users (
  id         bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  email      text        NOT NULL,                       -- 非空
  nickname   text        NOT NULL DEFAULT '',            -- 非空 + 默认值
  age        integer     CHECK (age >= 0 AND age <= 200),-- 检查（跨列亦可）
  status     text        NOT NULL DEFAULT 'active',
  created_at timestamptz NOT NULL DEFAULT now(),

  -- 表约束：跨列检查
  CONSTRAINT ck_users_age_lt_200 CHECK (age IS NULL OR age < 200)
);
text
CREATE TABLE

详细说明 ​

NOT NULL 与 CHECK (col IS NOT NULL) 的语义等价 ​

从「拦不拦得住 NULL」这个角度看，两者完全等价：

sql
CREATE TABLE t_nn_a (
  id bigint PRIMARY KEY,
  v  text NOT NULL                     -- 写法 A：内置约束
);

CREATE TABLE t_nn_b (
  id bigint PRIMARY KEY,
  v  text CHECK (v IS NOT NULL)        -- 写法 B：CHECK 表达同样语义
);

两种写法对 NULL 的拒绝行为一致：

sql
-- 两种表都会报错，错误信息略有不同
INSERT INTO t_nn_a (id, v) VALUES (1, NULL);
text
ERROR:  null value in column "v" of relation "t_nn_a" violates not-null constraint
DETAIL:  Failing row contains (1, null).
sql
INSERT INTO t_nn_b (id, v) VALUES (1, NULL);
text
ERROR:  new row for relation "t_nn_b" violates check constraint "t_nn_b_v_check"
DETAIL:  Failing row contains (1, null).

真正的差别在于代价与能力：

对比项NOT NULLCHECK (col IS NOT NULL)
检查方式元数据标记，写入时直接看 attnotnull每行求值一个布尔表达式
CPU 开销极低每次写入都要执行表达式
优化器利用能直接推断「该列无 NULL」，可做外连接消除等改写不能用于优化器推断
能否被 DEFERRABLE否否
能否 NOT VALID否✅ 是
NOT NULL 列与 CHECK 并存建议只留 NOT NULL—

优化器推断这一条很实际：只有 NOT NULL 能让优化器确定「这一列一定不为空」，进而把 LEFT JOIN ... WHERE t.col IS NULL 改写成反连接短路。用 CHECK 写的等价约束起不到这个作用。

从 PostgreSQL 18 起，NOT NULL 在 pg_constraint 里也有了独立记录（contype = 'n'），这解决了老版本里「非空约束在约束巡检中隐身」的问题：

sql
-- PostgreSQL 18+
SELECT conname, contype, pg_get_constraintdef(oid) AS def
FROM pg_constraint
WHERE conrelid = 'users'::regclass
ORDER BY contype;
text
     conname     | contype |        def
-----------------+---------+--------------------
 users_pkey      | p       | PRIMARY KEY (id)
 users_email_not_null | n  | NOT NULL email
 ck_users_age_lt_200 | c   | CHECK ((age IS NULL) OR (age < 200))
(3 rows)

在 18 之前，非空信息只能从 pg_attribute 侧读：

sql
-- 兼容所有版本：从 pg_attribute 读非空标记
SELECT attname, attnotnull
FROM pg_attribute
WHERE attrelid = 'users'::regclass AND attnum > 0 AND NOT attisdropped
ORDER BY attnum;
text
  attname   | attnotnull
------------+------------
 id         | t
 email      | t
 nickname   | t
 age        | f
 status     | t
 created_at | t
(6 rows)

NOT NULL 为什么比 CHECK 更高效 ​

NOT NULL 的开销几乎为零：它是元数据层面的标记。写入一行时，执行器只看一下 attnotnull 标志与传入值是否为 NULL，不需要构造表达式、不需要函数调用、不需要类型转换。

sql
-- 用 EXPLAIN 观察：NOT NULL 不会出现在 Filter 里，也不会产生额外表达式求值
EXPLAIN (ANALYZE, COSTS OFF)
INSERT INTO t_nn_a (id, v) VALUES (1, 'x');
text
                  QUERY PLAN
----------------------------------------------
 Insert on t_nn_a (actual time=0.021 rows=0)
   Buffers: shared hit=2
 Planning Time: 0.028 ms
 Execution Time: 0.045 ms

CHECK (v IS NOT NULL) 每次插入都要走一遍表达式求值器（ExecEvalExpr），在高频写入场景下，几十万 TPS 的差距往往就来自这类「看起来无所谓」的表达式。

因此实践结论只有一条：要表达非空就用 NOT NULL，不要用 CHECK (col IS NOT NULL) 代替。只有当需要在非空之外再加一层业务条件、或者需要 NOT VALID 能力时，才用 CHECK。

CHECK 的语义：TRUE 通过，FALSE 拒绝，UNKNOWN 放行（关键陷阱） ​

这是 CHECK 最容易踩的坑。标准规定：CHECK 约束在表达式结果为 FALSE 时违反，结果为 TRUE 或 NULL（UNKNOWN）时通过。

sql
CREATE TABLE accounts (
  id      bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  user_id bigint,
  age     integer,
  balance numeric(12,2) CHECK (balance >= 0),
  CONSTRAINT ck_accounts_age CHECK (age > 0)      -- 看起来要求 age 为正数
);

直觉上 CHECK (age > 0) 应该拒绝 age 为空的行，实际不会：

sql
-- 成功！age IS NULL 时 age > 0 的结果是 UNKNOWN，而非 FALSE
INSERT INTO accounts (user_id, age, balance) VALUES (1, NULL, 100.00);
text
INSERT 0 1
sql
-- 而负数确实被拦住
INSERT INTO accounts (user_id, age, balance) VALUES (1, -5, 100.00);
text
ERROR:  new row for relation "accounts" violates check constraint "ck_accounts_age"
DETAIL:  Failing row contains (2, 1, -5, 100.00).

同理，balance >= 0 也无法拦住 balance 为 NULL 的行：

sql
-- 成功！balance IS NULL → UNKNOWN → 放行
INSERT INTO accounts (user_id, age, balance) VALUES (1, 20, NULL);
text
INSERT 0 1

如果「必须为正数」是业务硬要求，就必须把非空与检查一起写：

sql
CREATE TABLE accounts_strict (
  id      bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  user_id bigint        NOT NULL,
  age     integer       NOT NULL CHECK (age > 0),           -- 先非空，再检查
  balance numeric(12,2) NOT NULL CHECK (balance >= 0)
);
sql
-- 现在 NULL 会被 NOT NULL 拦下
INSERT INTO accounts_strict (user_id, age, balance) VALUES (1, NULL, 100.00);
text
ERROR:  null value in column "age" of relation "accounts_strict" violates not-null constraint
DETAIL:  Failing row contains (2, 1, null, 100.00).

也可以把非空条件写进同一个 CHECK（用 IS NOT NULL AND），但推荐拆开写：NOT NULL 更高效，报错信息也更明确。

sql
-- 可行但不推荐：把非空塞进 CHECK
CONSTRAINT ck_age CHECK (age IS NOT NULL AND age > 0)

-- 推荐：职责分离
age integer NOT NULL CHECK (age > 0)

CHECK 的三条硬性限制 ​

CHECK 表达式的能力边界必须记牢，否则会遇到一堆「语法上没问题、运行时被判非法」的困惑。

限制一：不能包含子查询（Subquery）

sql
-- 错误！CHECK 中不允许子查询
CREATE TABLE bad_check_sub (
  id      bigint PRIMARY KEY,
  user_id bigint,
  CONSTRAINT ck_bad_sub CHECK (user_id IN (SELECT id FROM users))
);
text
ERROR:  cannot use subquery in check constraint

限制二：不能使用非 IMMUTABLE 函数

约束在行写入时求值，如果表达式结果会随时间或环境变化，约束就失去了确定含义。

sql
-- 错误！now() 是 STABLE，不是 IMMUTABLE
CREATE TABLE bad_check_now (
  id         bigint PRIMARY KEY,
  created_at timestamptz,
  CONSTRAINT ck_bad_now CHECK (created_at <= now())
);
text
ERROR:  functions in check constraint must be marked IMMUTABLE

限制三：不能引用其他表的列、也不能引用同一表/同一行的其他行

sql
-- 错误！跨表引用
CREATE TABLE bad_check_cross (
  id      bigint PRIMARY KEY,
  user_id bigint,
  CONSTRAINT ck_bad_cross CHECK (user_id = users.id)
);
text
ERROR:  cross-database references are not implemented: "demo.users.id"

CHECK 只能引用同一行内的列，而且必须是所在表的列。这条限制划定了 CHECK 的适用面：

想表达的需求CHECK 能做吗正确方案
值域范围（age > 0）✅CHECK
枚举取值（status IN (...)）✅CHECK
跨列比较（end_at > start_at）✅CHECK
引用其他表的值❌外键 FOREIGN KEY
引用同一表其他行❌EXCLUDE 排他约束或触发器
依赖当前时间❌触发器 / 应用层

跨列检查是允许的，这常被误以为不行：

sql
CREATE TABLE reservations (
  id       bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  room_id  integer     NOT NULL,
  starts_at timestamptz NOT NULL,
  ends_at   timestamptz NOT NULL,
  CONSTRAINT ck_period_ok CHECK (ends_at > starts_at)   -- 合法：同一行的两列
);
sql
-- 时间倒置会被拦下
INSERT INTO reservations (room_id, starts_at, ends_at)
VALUES (1, '2026-09-24 10:00+08', '2026-09-24 09:00+08');
text
ERROR:  new row for relation "reservations" violates check constraint "ck_period_ok"
DETAIL:  Failing row contains (1, 1, 2026-09-24 10:00:00+08, 2026-09-24 09:00:00+08).

NOT VALID：在线加 CHECK 约束的关键技巧 ​

在大表上直接 ADD CONSTRAINT ... CHECK (...) 会扫描整表校验历史数据，期间持有 ACCESS EXCLUSIVE 锁——线上等同于短暂停服。对 CHECK 而言，有一条优雅的在线路径：NOT VALID。

sql
-- 第 1 步：只对新增/修改的行生效，跳过历史数据（极快，只改元数据）
SET lock_timeout = '3s';
ALTER TABLE orders
  ADD CONSTRAINT ck_orders_amount CHECK (amount >= 0) NOT VALID;
RESET lock_timeout;
text
ALTER TABLE

此时约束已生效，但只作用于新数据：

sql
SELECT conname, convalidated, pg_get_constraintdef(oid) AS def
FROM pg_constraint
WHERE conrelid = 'orders'::regclass AND contype = 'c';
text
     conname      | convalidated |               def
------------------+--------------+----------------------------------
 ck_orders_amount | f            | CHECK ((amount >= 0)) NOT VALID
(1 row)
sql
-- 新插入的负数已经被拦住
INSERT INTO orders (user_id, order_no, amount) VALUES (1, 'SO-X', -1.00);
text
ERROR:  new row for relation "orders" violates check constraint "ck_orders_amount"
DETAIL:  Failing row contains (1, 1, SO-X, -1.00, ...).
sql
-- 第 2 步：校验历史数据。只取 SHARE UPDATE EXCLUSIVE 锁，不阻塞读写
ALTER TABLE orders VALIDATE CONSTRAINT ck_orders_amount;
text
ALTER TABLE
sql
-- 校验完成后 convalidated 变成 t
SELECT conname, convalidated FROM pg_constraint
WHERE conrelid = 'orders'::regclass AND contype = 'c';
text
     conname      | convalidated
------------------+--------------
 ck_orders_amount | t
(1 row)

如果历史数据里有违规行，VALIDATE 会直接报错（注意这里只报「存在违规」，不像外键那样给出具体键值）：

sql
-- 错误！历史数据中存在 amount < 0 的行
ALTER TABLE orders VALIDATE CONSTRAINT ck_orders_amount;
text
ERROR:  check constraint "ck_orders_amount" of relation "orders" is violated by some row

先定位再修复：

sql
-- 找出所有违规行并修复
SELECT count(*) FROM orders WHERE amount < 0;
UPDATE orders SET amount = 0 WHERE amount < 0;

ALTER TABLE orders VALIDATE CONSTRAINT ck_orders_amount;

还有一个容易忽略的点：NOT VALID 的 CHECK 约束可以被查询优化器部分利用。优化器不敢信任历史数据，但可以基于它做「新数据一定满足」的推断，因此不会完全忽略它。

NO INHERIT：不向下继承的检查约束 ​

CHECK 默认会被子表继承。NO INHERIT 可以切断这一点：

sql
CREATE TABLE base_orders (
  id     bigint PRIMARY KEY,
  status text NOT NULL,
  CONSTRAINT ck_base_status CHECK (status IN ('new', 'paid')) NO INHERIT
);

CREATE TABLE child_orders (
  note text
) INHERITS (base_orders);

NO INHERIT 的含义是：子表不会继承这条约束，因此子表可以插入父表不允许的值：

sql
-- 父表：被约束拦住
INSERT INTO base_orders (id, status) VALUES (1, 'refunded');
text
ERROR:  new row for relation "base_orders" violates check constraint "ck_base_status"
DETAIL:  Failing row contains (1, refunded).
sql
-- 子表：没有继承约束，允许（这是 NO INHERIT 的语义）
INSERT INTO child_orders (id, status, note) VALUES (2, 'refunded', '子表专有状态');
text
INSERT 0 1

反之，如果去掉 NO INHERIT，子表会继承约束，上面这条插入就会失败。

sql
-- 查看约束的继承标记
SELECT conname, conislocal, coninhcount
FROM pg_constraint
WHERE conrelid = 'child_orders'::regclass;
text
      conname      | conislocal | coninhcount
-------------------+------------+-------------
 child_orders_pkey | t          | 0
(1 row)

DEFAULT 的求值时机与表达式限制 ​

DEFAULT 的规则很简单：只在 INSERT 未提供该列时求值一次，且只对新行生效。

sql
CREATE TABLE events (
  id         bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  payload    jsonb       NOT NULL DEFAULT '{}'::jsonb,
  tags       text[]      NOT NULL DEFAULT '{}',
  created_at timestamptz NOT NULL DEFAULT now(),     -- 每次插入都重新求值
  seq        bigint      NOT NULL DEFAULT nextval('events_seq')
);

关键点在于 now() 与 nextval() 这类函数是逐行求值的，不是「建表时算一次」：

sql
INSERT INTO events (payload) VALUES ('{"a":1}'), ('{"b":2}');
SELECT id, created_at, seq FROM events ORDER BY id;
text
 id |          created_at           | seq
----+-------------------------------+-----
  1 | 2026-09-24 10:12:33.40123+08  |   1
  2 | 2026-09-24 10:12:33.40124+08  |   2
(2 rows)

两行的 created_at 有微秒级差异、seq 各不相同——证明是每行各求值一次。如果是「语句级只求值一次」，两行值会完全相同。

关于函数易变性（Volatility）与 DEFAULT 的关系，容易产生误解：

易变性举例能否用于 DEFAULT
IMMUTABLE'{}'::jsonb、upper('a')✅ 可以
STABLEnow()、current_user✅ 可以（但同一语句内值相同）
VOLATILErandom()、nextval()、clock_timestamp()✅ 可以，且每行重新求值

很多人以为 VOLATILE 函数不能出现在 DEFAULT 里，实际上是可以的——因为 DEFAULT 是逐行求值的，不存在「结果被缓存导致语义错误」的问题。真正限制 VOLATILE 的是 CHECK 约束（要求 IMMUTABLE）与索引表达式（要求 IMMUTABLE）。

sql
-- 合法：random() 是 VOLATILE，但 DEFAULT 逐行求值，语义没问题
CREATE TABLE demo_rand (
  id  bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  val double precision NOT NULL DEFAULT random()
);

INSERT INTO demo_rand DEFAULT VALUES;
INSERT INTO demo_rand DEFAULT VALUES;
SELECT id, round(val::numeric, 4) AS val FROM demo_rand;
text
 id |  val
----+--------
  1 | 0.4231
  2 | 0.8872
(2 rows)

注意 now() 是 STABLE（事务级时间戳），同一事务内多次调用返回相同值；想拿「真实当前时刻」要用 clock_timestamp()（VOLATILE）：

sql
BEGIN;
SELECT now() = now() AS same_in_tx;                        -- t
SELECT clock_timestamp() = clock_timestamp() AS same_now;  -- f
COMMIT;

DEFAULT 与 NOT NULL 的组合 ​

「非空 + 默认值」是最常见的搭配，但要小心二者独立生效的顺序：

sql
CREATE TABLE cfg (
  key   text NOT NULL,
  value text NOT NULL DEFAULT ''
);

DEFAULT 只在你没有提到该列时生效。显式写 NULL 不行：

sql
-- 正确：未提及 value，取默认值 ''
INSERT INTO cfg (key) VALUES ('a');

-- 错误！显式写 NULL，DEFAULT 不生效，被 NOT NULL 拦下
INSERT INTO cfg (key, value) VALUES ('b', NULL);
text
ERROR:  null value in column "value" of relation "cfg" violates not-null constraint
DETAIL:  Failing row contains (b, null).

ON CONFLICT DO UPDATE 里的 EXCLUDED 也遵循同样规则：

sql
-- 冲突时不覆盖 value（保留原值）
INSERT INTO cfg (key, value) VALUES ('a', '')
ON CONFLICT (key) DO NOTHING;

ALTER COLUMN：SET / DROP DEFAULT 与 SET NOT NULL ​

DEFAULT 的增删只改元数据，速度极快，不会重写历史行：

sql
-- 加上默认值：只对将来插入的行生效，已有行的值不变
ALTER TABLE cfg ALTER COLUMN value SET DEFAULT 'unknown';

-- 去掉默认值：将来插入缺列时会得到 NULL（若同时有 NOT NULL 则会报错）
ALTER TABLE cfg ALTER COLUMN value DROP DEFAULT;

这一点务必记住：SET DEFAULT 不会回溯填充已有行。已经存在的 NULL 需要自己 UPDATE：

sql
-- 已有行不会被自动填充，需要显式回填
UPDATE cfg SET value = 'unknown' WHERE value IS NULL;

SET NOT NULL 则一定要扫全表校验（除非已有等价的 CHECK 或版本优化）：

sql
-- 大表上会全表扫描并持 ACCESS EXCLUSIVE 锁
ALTER TABLE orders ALTER COLUMN user_id SET NOT NULL;

PostgreSQL 12+ 有一处优化：如果表上存在一个已被验证（convalidated = true）的 CHECK (col IS NOT NULL) 约束，SET NOT NULL 可以跳过全表扫描，直接借用该约束的结论。这是历史项目在无法停机时加非空的经典手法。

sql
-- 第 1 步：先加一条 NOT VALID 的等价 CHECK（很快）
ALTER TABLE orders ADD CONSTRAINT ck_orders_user_id_nn CHECK (user_id IS NOT NULL) NOT VALID;

-- 第 2 步：后台校验（不阻塞读写）
ALTER TABLE orders VALIDATE CONSTRAINT ck_orders_user_id_nn;

-- 第 3 步：此时 SET NOT NULL 可跳过全表扫描（PostgreSQL 12+）
ALTER TABLE orders ALTER COLUMN user_id SET NOT NULL;

-- 第 4 步：非空已落地，可删掉这条「垫脚」约束
ALTER TABLE orders DROP CONSTRAINT ck_orders_user_id_nn;

注意

只在 PostgreSQL 12 及以上版本才会自动利用已有的已验证 CHECK 约束来跳过 SET NOT NULL 的全表扫描；更早版本仍会扫描整表。

ADD COLUMN ... DEFAULT 不再重写全表（PostgreSQL 11+） ​

这是 PostgreSQL 11 引入的一个重要优化，也是最常被引用、却常被误述的一条。

PostgreSQL 11 之前：ALTER TABLE ... ADD COLUMN c type DEFAULT expr 会物理重写整张表，把默认值逐行写入每一行。对亿级大表来说，这意味着长时间持锁 + 磁盘空间翻倍。

PostgreSQL 11 及以后：如果 DEFAULT 是非易变（non-volatile）的，PostgreSQL 只把默认值记录在元数据（pg_attribute.attmissingval）中，不重写任何数据行。读取时若发现某行「这一列不存在」，就用元数据里的默认值补上。

sql
-- PostgreSQL 11+：只改元数据，秒级完成，即使表有上亿行
ALTER TABLE orders ADD COLUMN channel text NOT NULL DEFAULT 'web';
text
ALTER TABLE

验证这次变更确实是「元数据级」的：

sql
-- 查看是否存在 attmissingval（非 NULL 说明列是通过元数据默认值补上的）
SELECT attname, atthasmissing, attmissingval
FROM pg_attribute
WHERE attrelid = 'orders'::regclass AND attname = 'channel';
text
 attname | atthasmissing | attmissingval
---------+---------------+---------------
 channel | t             | {web}
(1 row)

atthasmissing = t 就是「没有重写表」的证据。触发一次行更新后，该行会物理写入真实值：

sql
-- 更新后该行物理落盘，但列的元数据默认值对未更新行依然有效
UPDATE orders SET channel = 'app' WHERE id = 1;

需要重写全表的例外情况（这些仍会造新表）：

情况是否重写原因
DEFAULT 是常量或 IMMUTABLE 表达式❌ 不重写可写入 attmissingval
DEFAULT 是 now()、random()、nextval() 等✅ 重写每行应得到不同/当时的值，无法用统一默认值代替
同时修改列类型✅ 重写类型转换需要逐行计算
SET NOT NULL（无 CHECK 垫脚）❌ 不重写但全表扫描只读校验
加 STORAGE / COMPRESSION 变更✅ 视情况需重写以应用新策略
sql
-- 会重写全表：VOLATILE 默认值
-- 大表上不要这样做
ALTER TABLE orders ADD COLUMN created_at2 timestamptz DEFAULT clock_timestamp();

-- 安全写法：先加可空列（不重写），再后台回填
ALTER TABLE orders ADD COLUMN created_at2 timestamptz;
UPDATE orders SET created_at2 = now() WHERE created_at2 IS NULL;   -- 分批执行
ALTER TABLE orders ALTER COLUMN created_at2 SET NOT NULL;

查看与维护约束 ​

CHECK 约束统一在 pg_constraint 中查看，pg_get_constraintdef() 可以把内部表达式还原成可读 SQL：

sql
SELECT
  c.conname                    AS constraint_name,
  c.contype                    AS type,
  c.convalidated               AS validated,
  c.connoinherit               AS no_inherit,
  pg_get_constraintdef(c.oid)  AS definition
FROM pg_constraint c
WHERE c.conrelid = 'orders'::regclass
ORDER BY c.contype, c.conname;
text
     constraint_name     | type | validated | no_inherit |                definition
-------------------------+------+-----------+------------+-------------------------------------------
 ck_orders_amount        | c    | t         | f          | CHECK ((amount >= 0))
 orders_pkey             | p    | t         | f          | PRIMARY KEY (id)
 uq_orders_no            | u    | t         | f          | UNIQUE (order_no)
 fk_orders_user          | f    | t         | f          | FOREIGN KEY (user_id) REFERENCES users(id)
(4 rows)

contype 的完整取值表（这是运维巡检时最常用的对照）：

contype约束类型阶段
c检查（Check）—
f外键（Foreign Key）—
p主键（Primary Key）—
u唯一（Unique）—
x排他（Exclude）—
n非空（Not Null）PostgreSQL 18+

也可以用 \d 一屏看全（Check constraints 段）：

sql
\d orders
text
                         Table "public.orders"
   Column   |           Type           | Nullable |              Default
------------+--------------------------+----------+-----------------------------------
 id         | bigint                   | not null | generated by default as identity
 user_id    | bigint                   | not null |
 order_no   | text                     | not null |
 amount     | numeric(12,2)            | not null |
Indexes:
    "orders_pkey" PRIMARY KEY, btree (id)
    "uq_orders_no" UNIQUE CONSTRAINT, btree (order_no)
Check constraints:
    "ck_orders_amount" CHECK (amount >= 0)
Foreign-key constraints:
    "fk_orders_user" FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE RESTRICT

删除约束用 DROP CONSTRAINT，删掉后再也拦不住可疑数据：

sql
ALTER TABLE orders DROP CONSTRAINT ck_orders_amount;

实战示例 ​

场景：给已有 200 万行的 orders 表在线补上 NOT NULL 与 CHECK 约束，全程不阻塞读写，最后验证约束确实生效。

第 1 步：准备数据（含少量脏数据）

sql
DROP TABLE IF EXISTS orders;

CREATE TABLE orders (
  id         bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  user_id    bigint,
  order_no   text,
  amount     numeric(12,2),
  status     text,
  created_at timestamptz NOT NULL DEFAULT now()
);

INSERT INTO orders (user_id, order_no, amount, status)
SELECT
  CASE WHEN g % 50000 = 0 THEN NULL ELSE (g % 1000) + 1 END,                     -- 少量 NULL
  'SO-' || lpad(g::text, 10, '0'),
  CASE WHEN g % 70000 = 0 THEN -1.00 ELSE (random() * 1000)::numeric(12,2) END,  -- 少量负数
  (ARRAY['pending', 'paid', 'shipped', 'bad_status'])[1 + (g % 4)],
  now() - (g || ' seconds')::interval
FROM generate_series(1, 2000000) AS g;

ANALYZE orders;

SELECT pg_size_pretty(pg_relation_size('orders')) AS table_size,
       count(*) FILTER (WHERE user_id IS NULL)    AS null_user,
       count(*) FILTER (WHERE amount < 0)         AS bad_amount,
       count(*) FILTER (WHERE status NOT IN ('pending', 'paid', 'shipped')) AS bad_status
FROM orders;
text
 table_size | null_user | bad_amount | bad_status
------------+-----------+------------+------------
 155 MB     |        40 |         28 |     500000
(1 row)

第 2 步：直接加约束会怎样（不要在生产执行）

sql
-- 会全表校验并持 ACCESS EXCLUSIVE 锁，200 万行期间业务全部阻塞
ALTER TABLE orders ADD CONSTRAINT ck_orders_status
  CHECK (status IN ('pending', 'paid', 'shipped'));
text
ERROR:  check constraint "ck_orders_status" of relation "orders" is violated by some row

连约束都加不上——历史数据里有 50 万行非法 status。

第 3 步：清洗脏数据

sql
-- 3.1 非法状态归入 pending
UPDATE orders SET status = 'pending'
WHERE status NOT IN ('pending', 'paid', 'shipped');

-- 3.2 负数金额归零
UPDATE orders SET amount = 0 WHERE amount < 0;

-- 3.3 空 user_id 归入「未知用户」，为后续 NOT NULL 做准备
INSERT INTO users (id, email) VALUES (999999, 'unknown@example.com') ON CONFLICT DO NOTHING;
UPDATE orders SET user_id = 999999 WHERE user_id IS NULL;

-- 复核
SELECT count(*) FILTER (WHERE status NOT IN ('pending', 'paid', 'shipped')) AS bad_status,
       count(*) FILTER (WHERE amount < 0)    AS bad_amount,
       count(*) FILTER (WHERE user_id IS NULL) AS null_user
FROM orders;
text
 bad_status | bad_amount | null_user
------------+------------+-----------
          0 |          0 |         0
(1 row)

第 4 步：用 NOT VALID 在线加 CHECK（不阻塞）

sql
SET lock_timeout = '3s';

ALTER TABLE orders ADD CONSTRAINT ck_orders_status
  CHECK (status IN ('pending', 'paid', 'shipped')) NOT VALID;

ALTER TABLE orders ADD CONSTRAINT ck_orders_amount
  CHECK (amount >= 0) NOT VALID;

RESET lock_timeout;
text
ALTER TABLE
ALTER TABLE

第 5 步：后台补校验（不阻塞读写）

sql
ALTER TABLE orders VALIDATE CONSTRAINT ck_orders_status;
ALTER TABLE orders VALIDATE CONSTRAINT ck_orders_amount;

SELECT conname, convalidated
FROM pg_constraint
WHERE conrelid = 'orders'::regclass AND contype = 'c'
ORDER BY conname;
text
      conname      | convalidated
-------------------+--------------
 ck_orders_amount  | t
 ck_orders_status  | t
(2 rows)

第 6 步：用「CHECK 垫脚」方式在线加 NOT NULL

sql
-- 6.1 先加等价 CHECK 并校验
ALTER TABLE orders ADD CONSTRAINT ck_orders_user_id_nn
  CHECK (user_id IS NOT NULL) NOT VALID;
ALTER TABLE orders VALIDATE CONSTRAINT ck_orders_user_id_nn;

-- 6.2 PostgreSQL 12+：SET NOT NULL 复用已验证的 CHECK，跳过全表扫描
SET lock_timeout = '3s';
ALTER TABLE orders ALTER COLUMN user_id SET NOT NULL;
RESET lock_timeout;

-- 6.3 删掉垫脚 CHECK，留着会带来重复检查开销
ALTER TABLE orders DROP CONSTRAINT ck_orders_user_id_nn;
text
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE

第 7 步：验证约束真的生效

sql
-- 7.1 非法状态被拦
INSERT INTO orders (user_id, order_no, amount, status) VALUES (1, 'SO-BAD-1', 10.00, 'bad_status');
text
ERROR:  new row for relation "orders" violates check constraint "ck_orders_status"
DETAIL:  Failing row contains (2000001, 1, SO-BAD-1, 10.00, bad_status, ...).
sql
-- 7.2 负数金额被拦
INSERT INTO orders (user_id, order_no, amount, status) VALUES (1, 'SO-BAD-2', -1.00, 'paid');
text
ERROR:  new row for relation "orders" violates check constraint "ck_orders_amount"
DETAIL:  Failing row contains (2000001, 1, SO-BAD-2, -1.00, paid, ...).
sql
-- 7.3 NULL user_id 被拦
INSERT INTO orders (user_id, order_no, amount, status) VALUES (NULL, 'SO-BAD-3', 10.00, 'paid');
text
ERROR:  null value in column "user_id" of relation "orders" violates not-null constraint
DETAIL:  Failing row contains (2000001, null, SO-BAD-3, 10.00, paid, ...).

第 8 步：查看最终约束与「元数据默认值」优化

sql
ALTER TABLE orders ADD COLUMN channel text NOT NULL DEFAULT 'web';

SELECT attname, atthasmissing, attmissingval
FROM pg_attribute
WHERE attrelid = 'orders'::regclass AND attname = 'channel';
text
 attname | atthasmissing | attmissingval
---------+---------------+---------------
 channel | t             | {web}
(1 row)

atthasmissing = t 说明这次 ADD COLUMN ... DEFAULT 只改了元数据，200 万行没有被重写。

第 9 步：清理

sql
DROP TABLE IF EXISTS orders;

注意事项 ​

  • CHECK 遇到 NULL 会放行。 表达式结果为 UNKNOWN（NULL）不构成违反，这是最高频的误解。
sql
-- 危险！age IS NULL 时放行
CONSTRAINT ck_age CHECK (age > 0)

-- 正确
age integer NOT NULL CHECK (age > 0)
  • CHECK 里不能有子查询、不能引用其他表、不能引用其他行。
sql
-- 错误！cannot use subquery in check constraint
CHECK (user_id IN (SELECT id FROM users))
  • CHECK 里不能用非 IMMUTABLE 函数（区别于 DEFAULT，后者允许 VOLATILE）。
sql
-- 错误！functions in check constraint must be marked IMMUTABLE
CHECK (created_at <= now())
  • 用 NOT NULL 而不是 CHECK (col IS NOT NULL)。 前者是元数据检查、开销近似为零，且能被优化器用于推断。
sql
-- 不推荐
v text CHECK (v IS NOT NULL)

-- 推荐
v text NOT NULL
  • ADD CONSTRAINT ... CHECK 默认扫全表校验并持 ACCESS EXCLUSIVE 锁。 大表必须用 NOT VALID + VALIDATE CONSTRAINT。
sql
-- 大表不要直接执行
ALTER TABLE orders ADD CONSTRAINT ck_x CHECK (amount >= 0);

-- 正确：两步走
ALTER TABLE orders ADD CONSTRAINT ck_x CHECK (amount >= 0) NOT VALID;
ALTER TABLE orders VALIDATE CONSTRAINT ck_x;
  • DEFAULT 不会作用于已存在的行。 ALTER COLUMN ... SET DEFAULT 只影响将来的插入。
sql
ALTER TABLE cfg ALTER COLUMN value SET DEFAULT 'unknown';
-- 已有 NULL 行不会变化，需要自己回填
UPDATE cfg SET value = 'unknown' WHERE value IS NULL;
  • ADD COLUMN ... DEFAULT 仅在 PostgreSQL 11+ 且默认值为非易变表达式时才不重写表。
sql
-- PostgreSQL 11+：元数据变更，秒级完成
ALTER TABLE orders ADD COLUMN channel text NOT NULL DEFAULT 'web';

-- 仍会重写：VOLATILE 默认值
ALTER TABLE orders ADD COLUMN ts timestamptz DEFAULT clock_timestamp();
  • SET NOT NULL 默认会全表扫描。 仅当存在已验证的 CHECK (col IS NOT NULL) 时（PostgreSQL 12+）才能跳过。
sql
ALTER TABLE orders ALTER COLUMN user_id SET NOT NULL;
  • SET NOT NULL 会失败于存量 NULL 行。 必须先修数据。
sql
-- 错误！column "user_id" of relation "orders" contains null values
ALTER TABLE orders ALTER COLUMN user_id SET NOT NULL;
  • NO INHERIT 会让子表不继承父表的 CHECK，可能让子表绕过父表的业务规则。
sql
CONSTRAINT ck_base_status CHECK (status IN ('new', 'paid')) NO INHERIT
  • DEFAULT 不属于约束，不会出现在 pg_constraint 里。 巡检「默认值一致性」要查 pg_attrdef / pg_get_expr(adbin, adrelid)。
sql
SELECT a.attname, pg_get_expr(d.adbin, d.adrelid) AS default_expr
FROM pg_attrdef d
JOIN pg_attribute a ON a.attrelid = d.adrelid AND a.attnum = d.adnum
WHERE d.adrelid = 'orders'::regclass;
  • NOT VALID 的约束只拦新数据，不保证历史数据合法。 上线后必须补 VALIDATE，否则这个约束是「半生效」状态。
sql
SELECT conname, convalidated FROM pg_constraint
WHERE conrelid = 'orders'::regclass AND NOT convalidated;
  • 删除 CHECK 约束会立即失去防护，应用层若依赖它兜底，删之前先确认代码已补校验。
sql
ALTER TABLE orders DROP CONSTRAINT ck_orders_amount;

最佳实践 ​

  • 非空一律用 NOT NULL，与业务检查分开写。 一个约束只表达一件事，报错信息也更好定位。
sql
age integer NOT NULL CHECK (age > 0)
  • 枚举与值域用 CHECK 显式表达，别只在应用层拦。 数据库是最后一道防线。
sql
CONSTRAINT ck_users_status CHECK (status IN ('active', 'disabled', 'banned'))
  • 跨列一致性用表级 CHECK（时间区间、金额关系），这是 CHECK 最有价值的能力。
sql
CONSTRAINT ck_period_ok CHECK (ends_at > starts_at)
  • 给所有约束显式命名（ck_<表>_<语义>），自动名（t_col_check）在迁移脚本与巡检 SQL 里极难引用。
sql
CONSTRAINT ck_orders_amount CHECK (amount >= 0)
  • 大表加约束一律走 NOT VALID + VALIDATE CONSTRAINT，把全表扫描从持锁阶段剥离出去。
sql
SET lock_timeout = '3s';
ALTER TABLE orders ADD CONSTRAINT ck_orders_amount CHECK (amount >= 0) NOT VALID;
RESET lock_timeout;
ALTER TABLE orders VALIDATE CONSTRAINT ck_orders_amount;
  • 大表加 NOT NULL 用「CHECK ... NOT VALID → VALIDATE → SET NOT NULL → DROP CHECK」四步法（PostgreSQL 12+）。
sql
ALTER TABLE orders ADD CONSTRAINT ck_o_user_nn CHECK (user_id IS NOT NULL) NOT VALID;
ALTER TABLE orders VALIDATE CONSTRAINT ck_o_user_nn;
ALTER TABLE orders ALTER COLUMN user_id SET NOT NULL;
ALTER TABLE orders DROP CONSTRAINT ck_o_user_nn;
  • DEFAULT 只用常量或 IMMUTABLE 表达式，这样 ADD COLUMN ... DEFAULT 才能走「不重写表」的快路径。
sql
status     text        NOT NULL DEFAULT 'active'
created_at timestamptz NOT NULL DEFAULT now()
  • 注意 now() 与 clock_timestamp() 的语义差异：前者是事务时间戳（同事务内恒定），后者是真实时钟。
sql
created_at  timestamptz NOT NULL DEFAULT now()              -- 事务一致，推荐
updated_at  timestamptz NOT NULL DEFAULT clock_timestamp()  -- 需要真实时刻才用
  • 在线变更脚本统一设置 lock_timeout，避免 ALTER TABLE 在锁队列里堵住后续所有查询。
sql
SET lock_timeout = '3s';
ALTER TABLE orders ADD CONSTRAINT ck_x CHECK (amount >= 0) NOT VALID;
RESET lock_timeout;
  • 上线后巡检未校验约束，把 NOT VALID 的残留当成技术债定期清理。
sql
SELECT conrelid::regclass AS tbl, conname, contype
FROM pg_constraint
WHERE NOT convalidated
ORDER BY 1, 2;
  • CHECK 约束数量要克制。 每个约束都是每次写入的一层表达式求值，宽表上堆十几个大范围 CHECK 会明显拖慢写入。

下一节

继续学习：EXCLUDE 排他约束

参考链接 ​