PostgreSQL - 约束

PostgreSQL - 约束 (Constraints)

约束（Constraint）是对表中数据列强制执行的规则，以确保数据完整性、准确性和可靠性。它们防止无效数据进入数据库。约束可以在列级别或表级别定义。

PostgreSQL 中最常见的约束包括：

• NOT NULL：确保列不能包含 NULL 值。
• UNIQUE：确保列（或一组列）中的所有值都是唯一的。
• PRIMARY KEY（主键）：NOT NULL 和 UNIQUE 的组合，用于唯一标识表中的每一行。
• FOREIGN KEY（外键）：确保列（或一组列）中的值与另一个表中的 PRIMARY KEY 或 UNIQUE 列中的值匹配，从而强制执行参照完整性。
• CHECK：确保列中的值满足特定条件。
• EXCLUSION：UNIQUE 约束的一种更通用形式，确保如果在指定列上使用指定运算符比较任意两行，则至少其中一个运算符比较将返回 false 或 null。

PRIMARY KEY（主键）约束

PRIMARY KEY 是最重要的约束。它唯一标识表中的每条记录。一个表只能有一个主键，主键可以由单个列或多个列（复合键）组成。

CREATE TABLE departments (
    id SERIAL PRIMARY KEY, -- 'SERIAL' 自动创建一个序列
    name VARCHAR(100) NOT NULL UNIQUE
);

CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    department_id INT
);

FOREIGN KEY（外键）约束

FOREIGN KEY 在两个表之间创建链接，维护参照完整性。它防止导致孤立记录的操作。您可以为 ON UPDATE 和 ON DELETE 事件指定操作。

示例

让我们为 employees 表添加一个引用 departments 表的外键。

ALTER TABLE employees
ADD CONSTRAINT fk_department
FOREIGN KEY (department_id) REFERENCES departments(id)
ON DELETE SET NULL; -- 如果一个部门被删除，则将员工的 department_id 设置为 NULL

常见的 ON DELETE / ON UPDATE 操作包括 CASCADE（删除/更新引用行）、SET NULL（将外键列设置为 NULL）、SET DEFAULT 和 RESTRICT（阻止操作，默认值）。

CHECK 约束

CHECK 约束确保列中的所有值都满足特定条件或布尔表达式。

示例

让我们添加一个 CHECK 约束，以确保员工的薪资是正数。

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    price NUMERIC(10, 2) NOT NULL CHECK (price > 0),
    stock_count INT NOT NULL CHECK (stock_count >= 0)
);

EXCLUSION 约束

EXCLUSION 约束保证，如果在指定列或表达式上使用指定运算符比较任意两行，则至少其中一个运算符比较将返回 false 或 null。它是 UNIQUE 约束的推广形式。一个常见的用例是防止时间范围重叠。

示例

要将排他约束与范围类型一起使用，您首先需要 btree_gist 扩展。此示例可防止同一会议室的预订时间重叠。

-- 首先，为每个数据库启用一次所需的扩展
CREATE EXTENSION IF NOT EXISTS btree_gist;

CREATE TABLE meeting_bookings (
    id SERIAL PRIMARY KEY,
    room_id INT NOT NULL,
    booking_period TSTZRANGE NOT NULL, -- Timestamp with timezone range
    EXCLUDE USING GIST (room_id WITH =, booking_period WITH &&)
);
-- '&&' 是范围类型的“重叠”运算符

有了这个约束，如果您尝试为与同一房间的现有预订在时间上重叠的房间插入新的预订，数据库将引发错误。

删除约束

要删除约束，您需要知道其名称。您可以使用 psql 中的 \d table_name 命令或通过查询 information_schema 来查找约束名称。

ALTER TABLE table_name DROP CONSTRAINT constraint_name;

示例

-- 让我们删除之前创建的外键
ALTER TABLE employees DROP CONSTRAINT fk_department;