返回所有指南
SQL··阅读约需 7 分钟

SQL 约束详解:NOT NULL、UNIQUE、CHECK、DEFAULT 等

掌握 SQL 约束以保护数据完整性。通过清晰的示例学习 NOT NULL、UNIQUE、CHECK、DEFAULT、PRIMARY KEY 和 FOREIGN KEY。

带有 NOT NULL、UNIQUE、非负价格 CHECK 和 DEFAULT 零的 Products 表
在本指南中
  1. 什么是 SQL 约束?
  2. NOT NULL
  3. 唯一
  4. CHECK
  5. DEFAULT
  6. PRIMARY KEY 和 FOREIGN KEY 约束
  7. 列级与表级约束
  8. 命名约束及其重要性
  9. 组合约束与常用模式
  10. 常见陷阱
  11. 参考资料与延伸阅读

除非另外命名了方言,否则 SQL 示例使用 PostgreSQL。简短的代码片段假定其引用的表已存在。在应用 DDL 之前,请在目标数据库中验证 DDL。

什么是 SQL 约束?

约束 是数据库引擎强制执行的规则,以保证表中数据的准确性和可靠性。它们充当护栏——在数据库边界阻止无效的插入、更新和删除。它们保护声明的规则;应用程序验证和事务设计仍然很重要。

SQL 定义了几种约束类型。在本指南中,我们将深入探讨每种约束,展示何时使用它,并强调常见的陷阱。

NOT NULL

这 NOT NULL 约束确保列始终有值。任何 INSERT 或 UPDATE 尝试将列设置为 NULL 将被拒绝。空字符串仍然是一个值;当禁止空文本时,添加一个 CHECK。

CREATE TABLE products (
    id    SERIAL       PRIMARY KEY,
    name  VARCHAR(150) NOT NULL,   -- every product MUST have a name
    price NUMERIC(10,2) NOT NULL   -- every product MUST have a price
);

-- This will fail:
INSERT INTO products (name, price) VALUES (NULL, 9.99);
-- ERROR: null value in column "name" violates not-null constraint

提示: 默认为 NOT NULL 对于每一列,只有在有明确的业务理由允许 null 时才删除它。Null 会将三值逻辑引入查询,并且是细微错误的常见来源。

唯一

A UNIQUE 约束保证受约束的列(或列组合)中没有两行包含相同的值。与主键不同, UNIQUE 列 可以 包含 NULL。PostgreSQL 和 MySQL 通常允许唯一列中有多个空值;SQL Server 在单列唯一约束中允许一个空值。这是一条唯一性规则,而不是评估 NULL = NULL 的结果。

CREATE TABLE users (
    id       SERIAL       PRIMARY KEY,
    email    VARCHAR(255) NOT NULL UNIQUE,
    username VARCHAR(50)  NOT NULL UNIQUE
);

-- Multi-column unique constraint
CREATE TABLE reservations (
    id        SERIAL PRIMARY KEY,
    room_id   INT    NOT NULL,
    date      DATE   NOT NULL,
    CONSTRAINT uq_room_date UNIQUE (room_id, date)
);

多列 UNIQUE 上面的约束确保了同一个房间不能在同一天被预订两次。

CHECK

CHECK 约束用于在值存储之前验证它是否满足布尔表达式。它们强制执行仅靠类型系统无法表达的行级规则。CHECK 接受 TRUE 或 UNKNOWN;当需要值时,将其与 NOT NULL 结合使用。

CREATE TABLE employees (
    id     SERIAL       PRIMARY KEY,
    name   VARCHAR(100) NOT NULL,
    age    INT          NOT NULL CHECK (age >= 18 AND age <= 120),
    salary NUMERIC(12,2) NOT NULL CHECK (salary > 0)
);

-- Named CHECK constraint with multiple columns
CREATE TABLE events (
    id         SERIAL      PRIMARY KEY,
    start_date DATE        NOT NULL,
    end_date   DATE        NOT NULL,
    CONSTRAINT chk_dates CHECK (end_date >= start_date)
);

注意事项: 在 8.0.16 之前的 MySQL 版本中,CHECK 约束已被解析,但 被静默忽略。请务必验证您的数据库版本是否实际强制执行了它们。

DEFAULT

这 DEFAULT 在从一个 INSERT 或者显式请求 DEFAULT。显式 NULL 不会触发默认值。默认值减少了应用程序代码中的样板文件,并使架构具有自文档化能力。

CREATE TABLE orders (
    id         SERIAL       PRIMARY KEY,
    status     VARCHAR(20)  NOT NULL DEFAULT 'pending',
    created_at TIMESTAMPTZ  NOT NULL DEFAULT now(),
    quantity   INT          NOT NULL DEFAULT 1
);

-- Inserting without specifying status, created_at, or quantity
INSERT INTO orders DEFAULT VALUES;
-- Result: status='pending', created_at=<current time>, quantity=1

带表达式的 DEFAULT

PostgreSQL 和 SQL Server 允许在 defaults 中使用表达式和函数调用。MySQL 自 8.0.13 版本以来支持此功能(针对某些类型)。

-- PostgreSQL: UUID default
CREATE TABLE tokens (
    id    UUID DEFAULT gen_random_uuid() PRIMARY KEY,
    value TEXT NOT NULL
);

PRIMARY KEY 和 FOREIGN KEY 约束

这些是关系数据库中最结构化的约束。一个 PRIMARY KEY 唯一标识每一行并隐式添加 NOT NULL + UNIQUE。一个 FOREIGN KEY 链接一个表到另一个表并强制执行引用完整性。

CREATE TABLE departments (
    id   SERIAL      PRIMARY KEY,
    name VARCHAR(80) NOT NULL UNIQUE
);

CREATE TABLE employees (
    id            SERIAL      PRIMARY KEY,
    name          VARCHAR(100) NOT NULL,
    department_id INT          NOT NULL,
    CONSTRAINT fk_emp_dept
        FOREIGN KEY (department_id)
        REFERENCES departments (id)
        ON DELETE RESTRICT
        ON UPDATE CASCADE
);

请参阅我们关于的深入指南 主键与外键 用于级联操作、复合键和最佳实践。您还可以在其中以可视化方式建模这些关系 Erd Go 编辑器.

列级与表级约束

可以定义约束 内联 (列级) 或作为单独的子句 所有列之后 (表级)。结果是相同的——但当约束跨越多个列时,表级是必需的。

-- Column-level (single column)
CREATE TABLE items (
    id    SERIAL PRIMARY KEY,
    code  VARCHAR(20) NOT NULL UNIQUE,
    price NUMERIC(10,2) NOT NULL CHECK (price >= 0)
);

-- Table-level (multi-column constraint)
CREATE TABLE shipments (
    id          SERIAL PRIMARY KEY,
    origin      VARCHAR(3) NOT NULL,
    destination VARCHAR(3) NOT NULL,
    CONSTRAINT chk_different_locations CHECK (origin <> destination),
    CONSTRAINT uq_route UNIQUE (origin, destination)
);

命名约束及其重要性

始终使用 CONSTRAINT 关键字。当违反约束时,数据库会在错误消息中返回约束名称——一个有意义的名称,如 chk_positive_salary 比自动生成的哈希值更有帮助。

-- Good: named constraint
ALTER TABLE products
    ADD CONSTRAINT chk_positive_price CHECK (price > 0);

-- You can also drop a constraint by name
ALTER TABLE products
    DROP CONSTRAINT chk_positive_price;

一致的命名规范使迁移、调试和团队上手更加顺畅。常见的模式是:

  • pk_<table> — 主键
  • fk_<table>_<column> — 外键
  • uq_<table>_<column(s)> — 唯一约束
  • chk_<table>_<rule> — 检查约束

组合约束与常用模式

在实践中,列同时带有多个约束。以下是你将反复使用的模式:

CREATE TABLE accounts (
    id         SERIAL       PRIMARY KEY,
    email      VARCHAR(255) NOT NULL,
    role       VARCHAR(20)  NOT NULL DEFAULT 'user'
                   CHECK (role IN ('user', 'admin', 'moderator')),
    balance    NUMERIC(12,2) NOT NULL DEFAULT 0.00
                   CHECK (balance >= 0),
    is_active  BOOLEAN      NOT NULL DEFAULT TRUE,
    created_at TIMESTAMPTZ  NOT NULL DEFAULT now(),
    deleted_at TIMESTAMPTZ           -- nullable: NULL means "not deleted"
);

软删除模式

这 deleted_at 上面的列实现了 软删除。您无需删除行,而是设置 deleted_at = now()。将其与部分唯一索引配对,以防止出现“活动”重复项,同时仍允许具有相同电子邮箱的已删除行存在。该表刻意没有在电子邮箱上设置全局 UNIQUE 约束;保留这样的约束也会拒绝已删除行之间的重复项:

-- PostgreSQL: unique email only among active accounts
CREATE UNIQUE INDEX uq_accounts_active_email
    ON accounts (email)
    WHERE deleted_at IS NULL;

常见陷阱

  • NULL 和 UNIQUE: 大多数数据库允许多个 NULL 中的值 UNIQUE 列,因为 NULL 不等于任何东西——包括它自己。SQL Server 是个例外,它将 NULL 默认情况下作为重复项;请使用过滤索引来解决此问题。
  • 旧版本 MySQL 中的 CHECK: 如上所述,MySQL 8.0.16 之前的版本会解析但忽略 CHECK 约束。验证您的 MySQL 版本或在应用程序代码中强制执行规则。
  • 延迟约束: PostgreSQL 支持对声明的外键、唯一、主键和排除约束进行延迟检查 DEFERRABLE INITIALLY DEFERRED。CHECK 和 NOT NULL 不能延迟。这对于循环引用很有用,但可能会增加调试的难度。
  • CHECK 的性能: 简单 CHECK 约束的开销可以忽略不计。避免在其中嵌入子查询或函数调用 CHECK — 它们甚至可能不受支持,并且会隐藏缓慢的逻辑。
  • 更改大型表上的约束: 添加 NOT NULL PostgreSQL 中的约束可能需要进行表扫描,除非现有经过验证的 CHECK 证明该列不包含空值。对于非常大的表,请考虑添加 CHECK (column IS NOT NULL) NOT VALID 首先,然后单独验证。

在以下位置同时审查 SQL 和图表中的约束: SQL转ERD编辑器, 然后在目标数据库中验证它们的行为。图表不强制执行数据完整性。

参考资料与延伸阅读

更多文章