PostgreSQL约束

概述

约束（Constraints）用于限制表中数据的类型和范围，确保数据的准确性和可靠性。PostgreSQL支持多种类型的约束。

约束类型

约束类型	说明
NOT NULL	列不能包含空值
UNIQUE	列值必须唯一
PRIMARY KEY	主键，唯一标识每行
FOREIGN KEY	外键，引用其他表
CHECK	检查条件
EXCLUSION	排他约束

NOT NULL约束

创建表时添加

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

修改现有表

-- 添加NOT NULL约束
ALTER TABLE users ALTER COLUMN username SET NOT NULL;

-- 移除NOT NULL约束
ALTER TABLE users ALTER COLUMN username DROP NOT NULL;

UNIQUE约束

单列唯一

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    email VARCHAR(100) UNIQUE
);

多列唯一（复合唯一）

CREATE TABLE user_roles (
    user_id INTEGER,
    role_id INTEGER,
    UNIQUE (user_id, role_id)
);

命名唯一约束

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    sku VARCHAR(50),
    CONSTRAINT unique_sku UNIQUE (sku)
);

修改现有表

-- 添加唯一约束
ALTER TABLE users ADD CONSTRAINT unique_email UNIQUE (email);

-- 删除唯一约束
ALTER TABLE users DROP CONSTRAINT unique_email;

PRIMARY KEY约束

单列主键

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

复合主键

CREATE TABLE order_items (
    order_id INTEGER,
    product_id INTEGER,
    quantity INTEGER,
    PRIMARY KEY (order_id, product_id)
);

命名主键约束

CREATE TABLE products (
    id SERIAL,
    name VARCHAR(100),
    CONSTRAINT pk_products PRIMARY KEY (id)
);

FOREIGN KEY约束

基本外键

-- 主表
CREATE TABLE departments (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL
);

-- 从表
CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    department_id INTEGER REFERENCES departments(id)
);

完整外键语法

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

外键动作选项

动作	ON DELETE	ON UPDATE
CASCADE	级联删除	级联更新
SET NULL	设为NULL	设为NULL
SET DEFAULT	设为默认值	设为默认值
RESTRICT	禁止删除	禁止更新
NO ACTION	延迟检查	延迟检查

添加外键到现有表

ALTER TABLE employees
ADD CONSTRAINT fk_department
    FOREIGN KEY (department_id)
    REFERENCES departments(id)
    ON DELETE SET NULL;

CHECK约束

单列检查

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    price DECIMAL(10, 2) CHECK (price > 0),
    stock INTEGER CHECK (stock >= 0)
);

多列检查

CREATE TABLE events (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    start_date DATE,
    end_date DATE,
    CHECK (end_date >= start_date)
);

命名检查约束

CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    age INTEGER,
    salary DECIMAL(10, 2),
    CONSTRAINT check_age CHECK (age >= 18 AND age <= 65),
    CONSTRAINT check_salary CHECK (salary > 0)
);

添加检查约束

ALTER TABLE products
ADD CONSTRAINT check_price CHECK (price > 0);

EXCLUSION约束

排他约束用于确保任意两行在指定列上的比较不会都返回TRUE。

-- 需要先创建扩展
CREATE EXTENSION IF NOT EXISTS btree_gist;

-- 创建时间范围不重叠的约束
CREATE TABLE room_reservations (
    id SERIAL PRIMARY KEY,
    room_id INTEGER NOT NULL,
    reserved_at TSRANGE NOT NULL,
    EXCLUDE USING GIST (
        room_id WITH =,
        reserved_at WITH &&
    )
);

-- 插入测试数据
INSERT INTO room_reservations (room_id, reserved_at)
VALUES (1, '[2024-01-15 10:00, 2024-01-15 12:00)');

-- 这条会失败（时间重叠）
INSERT INTO room_reservations (room_id, reserved_at)
VALUES (1, '[2024-01-15 11:00, 2024-01-15 13:00)');

查看表约束

-- 使用psql命令
\d table_name

-- 查询系统表
SELECT
    tc.constraint_name,
    tc.constraint_type,
    kcu.column_name
FROM information_schema.table_constraints tc
JOIN information_schema.key_column_usage kcu
    ON tc.constraint_name = kcu.constraint_name
WHERE tc.table_name = 'users';

-- 查看检查约束定义
SELECT conname, pg_get_constraintdef(oid)
FROM pg_constraint
WHERE conrelid = 'products'::regclass;

删除约束

-- 删除命名约束
ALTER TABLE users DROP CONSTRAINT unique_email;

-- 删除主键
ALTER TABLE users DROP CONSTRAINT users_pkey;

-- 删除外键
ALTER TABLE employees DROP CONSTRAINT fk_department;

禁用和启用约束

-- 禁用触发器（包括外键检查）
ALTER TABLE employees DISABLE TRIGGER ALL;

-- 启用触发器
ALTER TABLE employees ENABLE TRIGGER ALL;

-- 注意：PostgreSQL不支持直接禁用约束
-- 需要删除后重新创建，或使用NOT VALID选项
ALTER TABLE employees
ADD CONSTRAINT fk_department
    FOREIGN KEY (department_id)
    REFERENCES departments(id)
    NOT VALID;

-- 之后验证约束
ALTER TABLE employees VALIDATE CONSTRAINT fk_department;

最佳实践

1. 始终使用主键：每个表都应该有主键
2. 命名约束：使用有意义的约束名称便于维护
3. 外键索引：在外键列上创建索引提高性能
4. 适度使用CHECK：复杂的业务逻辑放在应用层
5. 文档化：记录约束的业务含义