17 列类型与约束

在实际的表定义中组合数据类型、默认值和约束。

实际的表会为每一列选择一个类型，通常还会添加约束——数据库对每一行强制执行的规则。最常见的约束包括 NOT NULL（列必须有值）、DEFAULT（未提供值时使用该值）、CHECK（必须为真的布尔表达式）、UNIQUE、PRIMARY KEY 和 REFERENCES（外键）。每种约束都会单独讲解；这里把其中几种组合到一个贴近实际的表中。

本章要点

在一个 CREATE TABLE 中组合多种类型和约束
内联 DEFAULT 和 NOT NULL
用于业务规则的 CHECK 约束
使用 timestamptz，并以 current_timestamp 作为默认值
在系统目录中检查约束

示例

示例 17.1 列类型与约束

DROP TABLE IF EXISTS products;

CREATE TABLE products (
  id          integer PRIMARY KEY,
  name        text NOT NULL,
  price       numeric(10, 2) NOT NULL CHECK (price >= 0),
  in_stock    boolean NOT NULL DEFAULT true,
  category    text DEFAULT 'misc',
  created_at  timestamptz NOT NULL DEFAULT current_timestamp,
  CONSTRAINT name_not_blank CHECK (length(trim(name)) > 0)
);

INSERT INTO products (id, name, price)              VALUES (1, 'Widget',    19.99);
INSERT INTO products (id, name, price, in_stock)    VALUES (2, 'Gadget',    49.99, false);
INSERT INTO products (id, name, price, category)    VALUES (3, 'Sprocket',  4.95, 'parts');

SELECT * FROM products ORDER BY id;

-- Inspect the constraints on the table
SELECT conname, pg_get_constraintdef(oid) AS def
FROM pg_constraint
WHERE conrelid = 'products'::regclass
ORDER BY conname;

每个约束都是数据库对你的数据作出的一项承诺。NOT NULL 表示该值始终存在。DEFAULT 会在 INSERT 省略该列时提供一个值。CHECK (expr) 会拒绝 expr 为假的任何行（NULL 值会通过）。PRIMARY KEY 意味着 NOT NULL 和 UNIQUE，并会创建索引。

约束可以是内联的（位于列类型之后，如上），也可以是表级的（位于所有列之后）。复合的 PRIMARY KEY、UNIQUE 和外键约束必须使用表级语法。CHECK 在两种位置都可以引用多个列；列约束和表约束都可以用 CONSTRAINT name 命名，以便生成更清晰的错误信息。

timestamptz NOT NULL DEFAULT current_timestamp 是最常见的列定义形式之一——行的创建时间存储为 UTC，永不为空，并自动设置。

运行示例

运行方式：

$ psql -f source/column-types-and-constraints.sql postgres
DROP TABLE
CREATE TABLE
INSERT 0 1
INSERT 0 1
INSERT 0 1
 id |   name   | price | in_stock | category |          created_at
----+----------+-------+----------+----------+-------------------------------
  1 | Widget   | 19.99 | t        | misc     | 2024-xx-xx xx:xx:xx.xxxxxx+00
  2 | Gadget   | 49.99 | f        | misc     | 2024-xx-xx xx:xx:xx.xxxxxx+00
  3 | Sprocket |  4.95 | t        | parts    | 2024-xx-xx xx:xx:xx.xxxxxx+00
(3 rows)
 ...

常见陷阱

忘记在本不应为空的列上添加 NOT NULL——以后添加需要先回填数据，而且在大表上可能需要长时间持有锁。
CHECK 在表达式为 NULL 时接受该行。若要拒绝 NULL 值，请将 NOT NULL 与 CHECK 结合使用。
将 now() 用作 DEFAULT：它确实有效，但在长事务中，每个默认值都是事务开始时的时间。通常这没有问题；如果确实需要每一行的挂钟时间，请使用 clock_timestamp()。
过去，在超大表上为新列添加 DEFAULT 和 NOT NULL 会重写整个表；现代 PostgreSQL（11+）在默认值为常量时会避免这种重写，但易变的默认值仍会触发表重写。

提示

为约束命名。PostgreSQL 生成的默认约束名虽然实用，却晦涩难懂。在生产环境的错误信息中出现 products_price_nonneg 这样的名称，会有很大帮助。

动手练习

尝试执行 INSERT INTO products (id, name, price) VALUES (4, ' ', 5);，观察 name_not_blank 检查约束拒绝该插入。再尝试执行 INSERT INTO products (id, name, price) VALUES (5, 'Negative', -1);，看看价格检查如何拒绝该行。然后在表创建之后添加一个约束：ALTER TABLE products ADD CONSTRAINT category_valid CHECK (category IN ('misc','parts','tools'));。

17 列类型与约束

本章要点

示例

运行示例

常见陷阱

下载