19 主键与唯一约束

选择主键和唯一约束，包括它们如何处理空值。

一个主键可以唯一标识一行。每张表都应该有一个主键——它为索引、连接、外键和应用程序代码提供了稳定的行定位依据。一张表至多有一个主键（主键可以由多个列组成）。UNIQUE 是更通用的“这里不能有重复值”约束；每张表可以有多个 UNIQUE 约束，其中可以包含允许空值的列。

本章要点

PRIMARY KEY 与 UNIQUE 的区别
单列主键与复合（多列）主键
代理键（生成的 id）与自然键（例如电子邮件地址）
UNIQUE 如何处理 NULL（每个 NULL 都不同，除非使用 NULLS NOT DISTINCT）
PK / UNIQUE 自动创建的索引

示例

示例 19.1 主键与唯一约束

DROP TABLE IF EXISTS users_example;

-- Inline primary key + unique
CREATE TABLE users_example (
  id    integer PRIMARY KEY,
  email text UNIQUE NOT NULL,
  name  text NOT NULL
);

INSERT INTO users_example (id, email, name) VALUES
  (1, 'alice@example.com', 'Alice'),
  (2, 'bob@example.com',   'Bob');

SELECT * FROM users_example ORDER BY id;

-- Composite primary key (across two columns)
DROP TABLE IF EXISTS enrollments_example;
CREATE TABLE enrollments_example (
  student_id integer,
  course_id  integer,
  enrolled_at timestamptz NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (student_id, course_id)
);

INSERT INTO enrollments_example (student_id, course_id) VALUES (1, 100), (1, 200), (2, 100);
SELECT * FROM enrollments_example ORDER BY student_id, course_id;

-- See indexes that the constraints created
SELECT indexname, indexdef
FROM pg_indexes
WHERE tablename IN ('users_example', 'enrollments_example')
ORDER BY indexname;

主键将 NOT NULL 与 UNIQUE 结合起来，并创建一个 B-树索引。PostgreSQL 使用主键索引执行连接、处理 WHERE id = ? 查找，并把它作为外键中的自然行标识符。每个设计良好的表都应该有一个主键。

UNIQUE 是更灵活的近亲。每张表可以有多个唯一约束——UNIQUE (email)、UNIQUE (handle)。UNIQUE 约束允许空值；按照标准，NULL 与其他每个 NULL 都不同，因此同一唯一列可以在多行中取 NULL。从 PostgreSQL 15 开始，如果希望把 NULL 视为重复值，可以用 UNIQUE NULLS NOT DISTINCT 覆盖这种行为。

复合键（PRIMARY KEY (a, b)）常用于表示多对多关系的关联表（一个学生可以选修多门课程；一门课程可以有多名学生；这两个值的组合是唯一的）。

运行示例

运行方式：

$ psql -f source/primary-keys-and-unique.sql postgres
DROP TABLE
CREATE TABLE
INSERT 0 2
 id |       email       | name
----+-------------------+-------
  1 | alice@example.com | Alice
  2 | bob@example.com   | Bob
(2 rows)
 ...

常见陷阱

允许空值的 UNIQUE 列：默认情况下，两个空值不算重复项。如果你的业务规则是“该列可以省略，但一旦设置就必须唯一”，默认行为正是如此；但如果规则要求“把空值视为相等”，请使用 UNIQUE NULLS NOT DISTINCT（PG 15+）。带有 WHERE col IS NOT NULL 的部分唯一索引仍允许多个空值，不能替代这种约束。
选择 email 这样的自然键作为主键看似简洁——直到用户修改了自己的电子邮件地址。代理键（id + UNIQUE (email)）通常更安全。
复合主键会传递到外键中；引用复合主键的子表必须同时包含这两列。通常，更简单的设计是为父表设置一个代理 id。
PRIMARY KEY 和 UNIQUE 都会自动创建一个索引；不要在同一列上再创建另一个 B-树索引。

提示

对于任何预计会增长到几百万行以上的表，主键应使用 bigint（或 id bigint GENERATED ALWAYS AS IDENTITY）。以后再从 integer 改成 bigint 会很痛苦——拿不准时，应在一开始就选择更大的类型。

动手练习

尝试插入重复值：INSERT INTO users_example (id, email, name) VALUES (1, 'x@example.com', 'X');——主键会拒绝它。然后执行 INSERT ... VALUES (3, 'alice@example.com', 'Alice2');——email 列上的 UNIQUE 约束会拒绝它。最后在 psql 中运行 \d users_example，查看索引和约束。

19 主键与唯一约束

本章要点

示例

运行示例

常见陷阱

下载