PostgreSQL

Appearance

约束（Constraints） ​

概述 ​

在 PostgreSQL 中，约束（Constraints） 是一种强制数据完整性的机制，确保数据库中的数据满足特定的业务规则和逻辑要求。约束比数据类型提供了更精细的数据控制能力。

为什么需要约束？

数据类型虽然能限制数据的基本格式，但对于复杂的业务逻辑来说还不够。比如：

  • 产品价格必须为正数
  • 每个产品编号只能出现一次
  • 订单必须关联到已存在的产品

约束类型概览 ​

约束类型作用适用场景语法关键字
检查约束验证数据是否满足指定条件价格为正数、年龄范围等CHECK
非空约束确保列不能为空值必填字段NOT NULL
唯一约束确保列值在表中唯一用户名、邮箱等UNIQUE
主键约束唯一标识表中的每一行表的主要标识符PRIMARY KEY
外键约束维护表间的引用完整性关联关系表REFERENCES
排除约束防止特定条件下的数据重叠时间段不重叠等EXCLUDE

5.1 检查约束（Check Constraints） ​

检查约束是最灵活的约束类型，允许您定义自定义的数据验证规则。

基本语法 ​

sql
-- 列级检查约束
CREATE TABLE products (
    product_no integer,
    name text,
    price numeric CHECK (price > 0)  -- 价格必须为正数
);

-- 表级检查约束
CREATE TABLE products (
    product_no integer,
    name text,
    price numeric,
    CHECK (price > 0)  -- 表级约束
);

命名约束 ​

最佳实践

为约束指定有意义的名称，便于错误诊断和后续维护。

sql
CREATE TABLE products (
    product_no integer,
    name text,
    price numeric CONSTRAINT positive_price CHECK (price > 0)
);

多列检查约束 ​

sql
CREATE TABLE products (
    product_no integer,
    name text,
    price numeric CHECK (price > 0),
    discounted_price numeric CHECK (discounted_price > 0),
    CHECK (price > discounted_price)  -- 原价必须高于折扣价
);

复杂检查约束示例 ​

sql
-- 员工信息表，包含多种业务规则
CREATE TABLE employees (
    emp_id integer PRIMARY KEY,
    name text NOT NULL,
    age integer CONSTRAINT valid_age CHECK (age >= 18 AND age <= 65),
    salary numeric CONSTRAINT positive_salary CHECK (salary > 0),
    department text,
    hire_date date DEFAULT CURRENT_DATE,
    
    -- 表级约束：确保某些部门的最低工资标准
    CONSTRAINT dept_salary_rule CHECK (
        (department = 'IT' AND salary >= 50000) OR
        (department = 'HR' AND salary >= 40000) OR
        (department NOT IN ('IT', 'HR'))
    )
);
sql
-- 成功插入
INSERT INTO employees (emp_id, name, age, salary, department) 
VALUES (1, '张三', 28, 60000, 'IT');

-- 失败示例（工资不符合部门要求）
INSERT INTO employees (emp_id, name, age, salary, department) 
VALUES (2, '李四', 25, 35000, 'IT');
-- ERROR: new row violates check constraint "dept_salary_rule"

NULL 值处理 ​

IMPORTANT

检查约束在遇到 NULL 值时会返回 TRUE，因此不会阻止 NULL 值的插入。如需防止 NULL 值，需要配合 NOT NULL 约束。

sql
CREATE TABLE test_null (
    id integer,
    value integer CHECK (value > 0)
);

-- 这些插入都会成功
INSERT INTO test_null VALUES (1, 10);    -- value > 0: TRUE
INSERT INTO test_null VALUES (2, NULL);  -- NULL > 0: NULL (被视为TRUE)

约束的限制 ​

重要限制

PostgreSQL 的 CHECK 约束不能：

  1. 引用其他表的数据
  2. 引用当前行以外的数据
  3. 包含子查询
  4. 调用非确定性函数（如 random()、now()）

5.2 非空约束（Not-Null Constraints） ​

非空约束是最简单但最常用的约束之一，确保列不能包含 NULL 值。

基本用法 ​

sql
CREATE TABLE products (
    product_no integer NOT NULL,
    name text NOT NULL,
    price numeric  -- 允许为NULL
);

多个约束组合 ​

sql
CREATE TABLE products (
    product_no integer NOT NULL,
    name text NOT NULL,
    price numeric NOT NULL CHECK (price > 0)  -- 既不能为空，又必须为正数
);

NULL vs NOT NULL 对比 ​

sql
-- 显式允许NULL（默认行为）
CREATE TABLE example1 (
    col1 integer NULL,     -- 等同于没有指定
    col2 integer           -- 默认允许NULL
);

-- 要求非空
CREATE TABLE example2 (
    col1 integer NOT NULL,
    col2 text NOT NULL
);

实际应用示例 ​

sql
CREATE TABLE users (
    user_id SERIAL PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL,
    password_hash TEXT NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    
    -- 可选字段
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    phone VARCHAR(20),
    bio TEXT
);
sql
-- 成功插入（所有必填字段都有值）
INSERT INTO users (username, email, password_hash) 
VALUES ('john_doe', '[email protected]', 'hashed_password');

-- 失败插入（缺少必填字段）
INSERT INTO users (username) 
VALUES ('jane_doe');
-- ERROR: null value in column "email" violates not-null constraint

TIP

在大多数数据库设计中，核心业务字段都应该标记为 NOT NULL，这样可以避免很多数据质量问题。

5.3 唯一约束（Unique Constraints） ​

唯一约束确保列或列组合在表中的值是唯一的。

单列唯一约束 ​

sql
-- 列级约束
CREATE TABLE products (
    product_no integer UNIQUE,
    name text,
    price numeric
);

-- 表级约束
CREATE TABLE products (
    product_no integer,
    name text,
    price numeric,
    UNIQUE (product_no)
);

多列唯一约束 ​

sql
CREATE TABLE flight_bookings (
    booking_id SERIAL PRIMARY KEY,
    flight_number VARCHAR(10),
    passenger_name VARCHAR(100),
    seat_number VARCHAR(5),
    UNIQUE (flight_number, seat_number)  -- 同一航班的座位号不能重复
);

命名唯一约束 ​

sql
CREATE TABLE products (
    product_no integer CONSTRAINT unique_product_no UNIQUE,
    name text,
    price numeric
);

NULL 值处理 ​

NULL 值的特殊性

默认情况下，唯一约束允许多个 NULL 值，因为 NULL 不等于 NULL。

sql
CREATE TABLE test_unique (
    id SERIAL PRIMARY KEY,
    code VARCHAR(10) UNIQUE
);

-- 这些插入都会成功
INSERT INTO test_unique (code) VALUES ('A001');
INSERT INTO test_unique (code) VALUES (NULL);
INSERT INTO test_unique (code) VALUES (NULL);  -- 第二个NULL也可以

-- 这个会失败
INSERT INTO test_unique (code) VALUES ('A001');
-- ERROR: duplicate key value violates unique constraint

NULLS NOT DISTINCT 选项 ​

sql
CREATE TABLE products (
    product_no integer,
    code VARCHAR(10) UNIQUE NULLS NOT DISTINCT  -- NULL值也被视为相同
);

-- 或者表级约束形式
CREATE TABLE products (
    product_no integer,
    code VARCHAR(10),
    UNIQUE NULLS NOT DISTINCT (code)
);

实际应用示例 ​

sql
CREATE TABLE user_accounts (
    user_id SERIAL PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    phone VARCHAR(20) UNIQUE,
    
    -- 命名约束便于管理
    CONSTRAINT unique_username UNIQUE (username),
    CONSTRAINT unique_email UNIQUE (email)
);
sql
CREATE TABLE products (
    product_id SERIAL PRIMARY KEY,
    sku VARCHAR(50) UNIQUE NOT NULL,
    name VARCHAR(200) NOT NULL,
    category_id INTEGER,
    
    -- 确保同一分类下的产品名称唯一
    UNIQUE (name, category_id)
);

5.4 主键约束（Primary Key Constraints） ​

主键约束是唯一约束和非空约束的组合，用于唯一标识表中的每一行。

基本概念 ​

主键 = 唯一性 + 非空性

sql
-- 这两种定义是等价的
CREATE TABLE products (
    product_no integer UNIQUE NOT NULL,
    name text,
    price numeric
);

CREATE TABLE products (
    product_no integer PRIMARY KEY,
    name text,
    price numeric
);

单列主键 ​

sql
CREATE TABLE users (
    user_id SERIAL PRIMARY KEY,  -- 自增主键
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL
);

复合主键 ​

sql
CREATE TABLE order_items (
    order_id INTEGER,
    product_id INTEGER,
    quantity INTEGER NOT NULL,
    unit_price DECIMAL(10,2) NOT NULL,
    
    PRIMARY KEY (order_id, product_id)  -- 复合主键
);

主键的特性 ​

实际应用示例 ​

sql
-- 用户表
CREATE TABLE users (
    user_id SERIAL PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL
);

-- 订单表
CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    user_id INTEGER REFERENCES users(user_id),
    order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    total_amount DECIMAL(12,2)
);

-- 订单详情表（复合主键）
CREATE TABLE order_details (
    order_id INTEGER,
    product_id INTEGER,
    quantity INTEGER NOT NULL,
    unit_price DECIMAL(10,2) NOT NULL,
    
    PRIMARY KEY (order_id, product_id),
    FOREIGN KEY (order_id) REFERENCES orders(order_id)
);
sql
-- 插入用户
INSERT INTO users (username, email) 
VALUES ('alice', '[email protected]');

-- 插入订单
INSERT INTO orders (user_id, total_amount) 
VALUES (1, 299.99);

-- 插入订单详情
INSERT INTO order_details (order_id, product_id, quantity, unit_price) 
VALUES (1, 101, 2, 149.99);

-- 尝试插入重复的复合主键（会失败）
INSERT INTO order_details (order_id, product_id, quantity, unit_price) 
VALUES (1, 101, 1, 199.99);
-- ERROR: duplicate key value violates unique constraint "order_details_pkey"

IMPORTANT

每个表最多只能有一个主键，但可以有多个唯一约束。主键通常用于：

  • 表的逻辑标识
  • 外键引用的目标
  • 数据库复制和同步

5.5 外键约束（Foreign Key Constraints） ​

外键约束维护表之间的引用完整性，确保子表中的值在父表中确实存在。

基本概念 ​

基本语法 ​

sql
-- 父表
CREATE TABLE products (
    product_id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    price DECIMAL(10,2) NOT NULL
);

-- 子表
CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    product_id INTEGER REFERENCES products(product_id),  -- 外键
    quantity INTEGER NOT NULL
);

简化语法 ​

sql
-- 当引用主键时，可以省略列名
CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    product_id INTEGER REFERENCES products,  -- 自动引用products的主键
    quantity INTEGER NOT NULL
);

命名外键约束 ​

sql
CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    product_id INTEGER,
    quantity INTEGER NOT NULL,
    CONSTRAINT fk_order_product 
        FOREIGN KEY (product_id) REFERENCES products(product_id)
);

复合外键 ​

sql
-- 父表
CREATE TABLE categories (
    category_id INTEGER,
    region_id INTEGER,
    name VARCHAR(100),
    PRIMARY KEY (category_id, region_id)
);

-- 子表
CREATE TABLE products (
    product_id SERIAL PRIMARY KEY,
    category_id INTEGER,
    region_id INTEGER,
    name VARCHAR(100),
    FOREIGN KEY (category_id, region_id) 
        REFERENCES categories(category_id, region_id)
);

外键约束的行为选项 ​

当父表中的记录被删除或更新时，外键约束可以采取不同的处理策略：

选项行为使用场景
NO ACTION阻止操作（默认）需要手动处理关联数据
RESTRICT立即阻止操作严格的引用完整性
CASCADE级联删除/更新子记录依赖于父记录
SET NULL设置为NULL可选的关联关系
SET DEFAULT设置为默认值有默认关联对象

删除行为示例 ​

sql
CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    customer_id INTEGER REFERENCES customers ON DELETE CASCADE,
    product_id INTEGER REFERENCES products ON DELETE RESTRICT,
    quantity INTEGER NOT NULL
);

-- 删除客户时，该客户的所有订单也会被删除
-- 删除产品时，如果有订单引用该产品，删除操作会被阻止
sql
CREATE TABLE employees (
    emp_id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    manager_id INTEGER REFERENCES employees ON DELETE SET NULL
);

-- 当经理被删除时，下属员工的manager_id被设置为NULL
sql
CREATE TABLE posts (
    post_id SERIAL PRIMARY KEY,
    title VARCHAR(200) NOT NULL,
    category_id INTEGER DEFAULT 1 
        REFERENCES categories ON DELETE SET DEFAULT
);

-- 当分类被删除时，相关文章的分类设置为默认分类(ID=1)

更新行为 ​

sql
CREATE TABLE order_items (
    item_id SERIAL PRIMARY KEY,
    order_id INTEGER 
        REFERENCES orders(order_id) ON UPDATE CASCADE,
    product_id INTEGER 
        REFERENCES products(product_id) ON UPDATE RESTRICT
);

自引用外键 ​

sql
CREATE TABLE employees (
    emp_id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    department VARCHAR(50),
    manager_id INTEGER REFERENCES employees(emp_id)
);

完整的多表关系示例 ​

sql
-- 客户表
CREATE TABLE customers (
    customer_id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL
);

-- 产品表
CREATE TABLE products (
    product_id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    price DECIMAL(10,2) NOT NULL CHECK (price > 0)
);

-- 订单表
CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    customer_id INTEGER NOT NULL,
    order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    FOREIGN KEY (customer_id) 
        REFERENCES customers(customer_id) ON DELETE RESTRICT
);

-- 订单详情表
CREATE TABLE order_items (
    order_id INTEGER,
    product_id INTEGER,
    quantity INTEGER NOT NULL CHECK (quantity > 0),
    unit_price DECIMAL(10,2) NOT NULL,
    
    PRIMARY KEY (order_id, product_id),
    FOREIGN KEY (order_id) 
        REFERENCES orders(order_id) ON DELETE CASCADE,
    FOREIGN KEY (product_id) 
        REFERENCES products(product_id) ON DELETE RESTRICT
);
sql
-- 插入基础数据
INSERT INTO customers (name, email) VALUES 
('张三', '[email protected]'),
('李四', '[email protected]');

INSERT INTO products (name, price) VALUES 
('笔记本电脑', 5999.99),
('无线鼠标', 199.99);

-- 创建订单
INSERT INTO orders (customer_id) VALUES (1);

-- 添加订单详情
INSERT INTO order_items (order_id, product_id, quantity, unit_price) VALUES 
(1, 1, 1, 5999.99),
(1, 2, 2, 199.99);

-- 尝试删除被引用的产品（会失败）
DELETE FROM products WHERE product_id = 1;
-- ERROR: update or delete on table "products" violates foreign key constraint

-- 删除订单（订单详情会被级联删除）
DELETE FROM orders WHERE order_id = 1;

MATCH 选项 ​

sql
CREATE TABLE order_items (
    order_id INTEGER,
    product_id INTEGER,
    supplier_id INTEGER,
    
    FOREIGN KEY (product_id, supplier_id) 
        REFERENCES products(product_id, supplier_id) MATCH FULL
);
  • MATCH FULL：所有外键列都必须非空，或者全部为空
  • MATCH PARTIAL：PostgreSQL 不支持
  • 默认行为：允许部分列为空

5.6 排除约束（Exclude Constraints） ​

排除约束是 PostgreSQL 特有的高级约束类型，用于防止某些条件下的数据重叠。

基本概念 ​

排除约束确保任意两行在指定列上使用指定操作符比较时，至少有一个比较返回 false。

基本语法 ​

sql
CREATE TABLE circles (
    circle_id SERIAL PRIMARY KEY,
    area CIRCLE,
    EXCLUDE USING gist (area WITH &&)  -- 确保圆形区域不重叠
);

时间段不重叠示例 ​

sql
-- 需要安装btree_gist扩展
CREATE EXTENSION IF NOT EXISTS btree_gist;

CREATE TABLE room_bookings (
    booking_id SERIAL PRIMARY KEY,
    room_id INTEGER NOT NULL,
    start_time TIMESTAMP NOT NULL,
    end_time TIMESTAMP NOT NULL,
    user_name VARCHAR(100) NOT NULL,
    
    -- 检查时间范围有效性
    CHECK (start_time < end_time),
    
    -- 确保同一房间的预订时间不重叠
    EXCLUDE USING gist (
        room_id WITH =,
        tsrange(start_time, end_time) WITH &&
    )
);
sql
-- 成功插入
INSERT INTO room_bookings (room_id, start_time, end_time, user_name) VALUES 
(1, '2024-01-15 09:00:00', '2024-01-15 10:00:00', '张三'),
(1, '2024-01-15 10:30:00', '2024-01-15 11:30:00', '李四');

-- 失败插入（时间重叠）
INSERT INTO room_bookings (room_id, start_time, end_time, user_name) VALUES 
(1, '2024-01-15 09:30:00', '2024-01-15 10:30:00', '王五');
-- ERROR: conflicting key value violates exclusion constraint

几何对象不重叠 ​

sql
-- 确保在同一层级上的区域不重叠
CREATE TABLE floor_areas (
    area_id SERIAL PRIMARY KEY,
    floor_level INTEGER NOT NULL,
    area_polygon POLYGON NOT NULL,
    area_name VARCHAR(100),
    
    EXCLUDE USING gist (
        floor_level WITH =,
        area_polygon WITH &&
    )
);

排除约束的操作符 ​

数据类型常用操作符含义
数值类型=相等
时间范围&&重叠
几何类型&&相交
数组类型&&有公共元素
文本类型=相等

复杂排除约束示例 ​

sql
CREATE EXTENSION IF NOT EXISTS btree_gist;

CREATE TABLE work_schedules (
    schedule_id SERIAL PRIMARY KEY,
    employee_id INTEGER NOT NULL,
    shift_date DATE NOT NULL,
    start_time TIME NOT NULL,
    end_time TIME NOT NULL,
    
    CHECK (start_time < end_time),
    
    -- 确保同一员工在同一天不能有重叠的班次
    EXCLUDE USING gist (
        employee_id WITH =,
        shift_date WITH =,
        timerange(start_time, end_time) WITH &&
    )
);
sql
CREATE TABLE ip_allocations (
    allocation_id SERIAL PRIMARY KEY,
    network_segment INET NOT NULL,
    allocated_to VARCHAR(100),
    allocation_date DATE DEFAULT CURRENT_DATE,
    
    -- 确保IP网段不重叠
    EXCLUDE USING gist (network_segment inet_ops WITH &&)
);

约束管理 ​

添加约束 ​

sql
-- 添加检查约束
ALTER TABLE products 
ADD CONSTRAINT check_positive_price CHECK (price > 0);

-- 添加外键约束
ALTER TABLE orders 
ADD CONSTRAINT fk_customer 
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id);

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

删除约束 ​

sql
-- 删除命名约束
ALTER TABLE products DROP CONSTRAINT check_positive_price;

-- 删除外键约束
ALTER TABLE orders DROP CONSTRAINT fk_customer;

查看约束信息 ​

sql
SELECT 
    conname AS constraint_name,
    contype AS constraint_type,
    pg_get_constraintdef(oid) AS definition
FROM pg_constraint 
WHERE conrelid = 'products'::regclass;
sql
SELECT 
    tc.table_name, 
    kcu.column_name, 
    ccu.table_name AS foreign_table_name,
    ccu.column_name AS foreign_column_name 
FROM 
    information_schema.table_constraints AS tc 
    JOIN information_schema.key_column_usage AS kcu
      ON tc.constraint_name = kcu.constraint_name
    JOIN information_schema.constraint_column_usage AS ccu
      ON ccu.constraint_name = tc.constraint_name
WHERE tc.constraint_type = 'FOREIGN KEY' 
    AND tc.table_name='orders';

性能考虑 ​

索引自动创建 ​

外键索引建议 ​

sql
-- 在外键列上手动创建索引以提高性能
CREATE TABLE order_items (
    item_id SERIAL PRIMARY KEY,
    order_id INTEGER REFERENCES orders(order_id),
    product_id INTEGER REFERENCES products(product_id),
    quantity INTEGER
);

-- 手动创建外键索引
CREATE INDEX idx_order_items_order_id ON order_items(order_id);
CREATE INDEX idx_order_items_product_id ON order_items(product_id);

最佳实践 ​

约束设计建议

  1. 命名规范：为所有约束指定有意义的名称
  2. 性能优化：为外键列创建索引
  3. 业务逻辑：用约束实现关键业务规则
  4. 数据完整性：优先在数据库层面保证数据一致性
  5. 约束组合：合理组合不同类型的约束

注意事项

  • 约束会影响 INSERT/UPDATE 性能
  • 外键约束可能导致锁等待
  • 约束违反会导致操作失败
  • 需要考虑约束对数据迁移的影响

总结 ​

PostgreSQL 的约束系统提供了强大的数据完整性保障机制：

  1. 检查约束：最灵活的自定义验证规则
  2. 非空约束：确保关键字段不为空
  3. 唯一约束：保证数据的唯一性
  4. 主键约束：表的主要标识符
  5. 外键约束：维护表间关系的一致性
  6. 排除约束：处理复杂的数据重叠问题

通过合理使用这些约束，可以在数据库层面建立强大的数据质量保障体系，减少应用程序中的数据验证逻辑，提高系统的可靠性和数据的一致性。