第五章：表与约束

最后更新: 2024-01-01 作者: PostgreSQL Team
页面目录

第五章：表与约束

5.1 表设计原则

良好的表设计是数据库性能和数据完整性的基础。

┌─────────────────────────────────────────────────────────────┐
│                    表设计核心原则                             │
├─────────────────────────────────────────────────────────────┤
│  1. 规范化设计（避免数据冗余）                                 │
│  2. 适当反规范化（提升查询性能）                                │
│  3. 主键设计（唯一标识记录）                                   │
│  4. 索引设计（优化查询效率）                                   │
│  5. 约束设计（保证数据完整性）                                 │
└─────────────────────────────────────────────────────────────┘

5.2 约束类型

PostgreSQL 支持多种约束类型：

约束类型 说明
NOT NULL 非空约束
UNIQUE 唯一约束
PRIMARY KEY 主键约束
FOREIGN KEY 外键约束
CHECK 检查约束
EXCLUDE 排他约束

5.3 NOT NULL 约束

-- 创建表并定义 NOT NULL
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL,
    age INTEGER,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

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

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

-- 测试约束
INSERT INTO users (username, email) VALUES ('test', 'test@example.com');
-- 成功

INSERT INTO users (username, email) VALUES (NULL, 'test@example.com');
-- 失败: ERROR: null value in column "username" violates not-null constraint

5.4 UNIQUE 约束

-- 单列唯一约束
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL UNIQUE
);

-- 多列唯一约束（复合唯一）
CREATE TABLE user_products (
    user_id INTEGER NOT NULL,
    product_id INTEGER NOT NULL,
    quantity INTEGER DEFAULT 1,
    UNIQUE (user_id, product_id)  -- 复合唯一键
);

-- 使用表约束语法
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    code VARCHAR(20) NOT NULL,
    name VARCHAR(100) NOT NULL,
    CONSTRAINT uk_products_code UNIQUE (code)
);

-- 给唯一约束创建索引（自动创建）
\d users  -- 会看到唯一的索引名

5.5 PRIMARY KEY 约束

主键是表中每行记录的唯一标识。

-- 单列主键
CREATE TABLE categories (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50) NOT NULL
);

-- 复合主键
CREATE TABLE order_items (
    order_id INTEGER NOT NULL,
    product_id INTEGER NOT NULL,
    quantity INTEGER DEFAULT 1,
    PRIMARY KEY (order_id, product_id)
);

-- 添加主键约束到已有表
ALTER TABLE users ADD PRIMARY KEY (id);

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

5.5.1 自增主键

-- SERIAL 方式
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50)
);

-- IDENTITY 方式（SQL 标准，推荐）
CREATE TABLE users (
    id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name VARCHAR(50)
);

-- 指定序列选项
CREATE TABLE users (
    id INTEGER GENERATED BY DEFAULT AS IDENTITY (
        START WITH 1000
        INCREMENT BY 1
        MINVALUE 1000
        MAXVALUE 999999999
        CYCLE
    ) PRIMARY KEY,
    name VARCHAR(50)
);

-- 获取当前序列值
SELECT currval('users_id_seq');
SELECT lastval();
SELECT nextval('users_id_seq');

5.6 FOREIGN KEY 约束

外键约束用于维护表之间的引用完整性。

-- 创建带外键的表
CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    user_id INTEGER NOT NULL REFERENCES users(id),
    order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    total_amount DECIMAL(10, 2)
);

-- 完整语法
CREATE TABLE order_items (
    item_id SERIAL PRIMARY KEY,
    order_id INTEGER NOT NULL,
    product_id INTEGER NOT NULL,
    quantity INTEGER DEFAULT 1,
    price DECIMAL(10, 2),
    FOREIGN KEY (order_id) REFERENCES orders(order_id)
        ON DELETE CASCADE
        ON UPDATE CASCADE,
    FOREIGN KEY (product_id) REFERENCES products(id)
        ON DELETE RESTRICT
);

-- 使用表约束语法
CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    user_id INTEGER NOT NULL,
    order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_orders_user 
        FOREIGN KEY (user_id) 
        REFERENCES users(id)
        ON DELETE RESTRICT
);

5.6.1 外键动作

-- ON DELETE 和 ON UPDATE 选项：
-- NO ACTION    - 默认，引用的行存在时阻止删除/更新
-- RESTRICT     - 类似于 NO ACTION，但不可延迟
-- CASCADE      - 删除/更新时同步删除/更新引用行
-- SET NULL     - 删除/更新时设置引用列为 NULL
-- SET DEFAULT  - 删除/更新时设置引用列为默认值

-- 示例：CASCADE 删除
CREATE TABLE posts (
    id SERIAL PRIMARY KEY,
    user_id INTEGER REFERENCES users(id) ON DELETE CASCADE,
    title VARCHAR(200),
    content TEXT
);

-- 删除用户时，所有帖子自动删除
DELETE FROM users WHERE id = 1;

5.6.2 外键约束验证

-- 检查外键是否有效
SELECT 
    tc.constraint_name,
    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';

-- 查找孤立数据
SELECT * FROM orders WHERE user_id NOT IN (SELECT id FROM users);

5.7 CHECK 约束

CHECK 约束用于限制列中值的范围。

-- 列级 CHECK 约束
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    price DECIMAL(10, 2) CHECK (price > 0),
    quantity INTEGER CHECK (quantity >= 0),
    discount DECIMAL(3, 2) CHECK (discount >= 0 AND discount <= 1)
);

-- 表级 CHECK 约束
CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    user_id INTEGER NOT NULL,
    subtotal DECIMAL(10, 2),
    tax DECIMAL(10, 2),
    total DECIMAL(10, 2),
    CHECK (tax >= 0),
    CHECK (subtotal >= 0),
    CHECK (total = subtotal + tax)
);

-- 添加 CHECK 约束
ALTER TABLE products ADD CONSTRAINT chk_products_price 
    CHECK (price > 0 AND price < 100000);

-- 命名 CHECK 约束（便于管理）
ALTER TABLE products ADD CONSTRAINT chk_products_discount 
    CHECK (discount >= 0 AND discount <= 1);

-- 删除 CHECK 约束
ALTER TABLE products DROP CONSTRAINT chk_products_price;

5.7.1 CHECK 约束示例

-- 邮箱格式验证
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    email VARCHAR(255),
    CONSTRAINT chk_users_email 
        CHECK (email ~* '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$')
);

-- 手机号格式验证
CREATE TABLE contacts (
    id SERIAL PRIMARY KEY,
    phone VARCHAR(20),
    CONSTRAINT chk_contacts_phone 
        CHECK (phone ~ '^\+?[1-9]\d{1,14}$')
);

-- 日期范围验证
CREATE TABLE bookings (
    id SERIAL PRIMARY KEY,
    check_in DATE,
    check_out DATE,
    CONSTRAINT chk_bookings_dates 
        CHECK (check_out > check_in)
);

5.8 EXCLUDE 约束

排他约束确保特定比较运算符下没有重复值。

-- 安装 btree_gist 扩展（支持范围类型排除）
CREATE EXTENSION IF NOT EXISTS btree_gist;

-- 防止同一房间同一时间段被重复预订
CREATE TABLE room_bookings (
    id SERIAL PRIMARY KEY,
    room_number INTEGER NOT NULL,
    booking_period TSTZRANGE NOT NULL,
    guest_name VARCHAR(100),
    EXCLUDE USING gist (
        room_number WITH =,
        booking_period WITH &&
    )
);

-- 测试排他约束
INSERT INTO room_bookings (room_number, booking_period, guest_name)
VALUES (101, '[2024-06-15 14:00, 2024-06-18 12:00)', '张三');

-- 这个会失败（时间段重叠）
INSERT INTO room_bookings (room_number, booking_period, guest_name)
VALUES (101, '[2024-06-16 14:00, 2024-06-19 12:00)', '李四');
-- ERROR: conflicting key value violates exclusion constraint

5.9 模式 (Schema)

模式是数据库的命名空间，用于组织表和其他对象。

5.9.1 默认模式

PostgreSQL 默认使用 public 模式。

-- 查看所有模式
SELECT schema_name FROM information_schema.schemata;

-- 查看 public 模式中的表
\dt public.*

-- 在 public 模式中创建表
CREATE TABLE users (id SERIAL PRIMARY KEY);
-- 等价于
CREATE TABLE public.users (id SERIAL PRIMARY KEY);

5.9.2 创建模式

-- 创建新模式
CREATE SCHEMA hr;

-- 创建带授权的模式
CREATE SCHEMA analytics AUTHORIZATION db_admin;

-- 创建并指定默认所有者
CREATE SCHEMA production OWNER db_admin;

-- 使用模式前缀创建对象
CREATE TABLE hr.employees (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    salary DECIMAL(10, 2)
);

5.9.3 模式搜索路径

-- 查看当前搜索路径
SHOW search_path;
SELECT current_schema();

-- 设置搜索路径
SET search_path TO hr, public;

-- 恢复默认路径
SET search_path TO DEFAULT;

-- 在数据库级别设置默认搜索路径
ALTER DATABASE mydb SET search_path TO hr, public;

5.9.4 模式管理

-- 重命名模式
ALTER SCHEMA hr RENAME TO human_resources;

-- 修改模式所有者
ALTER SCHEMA human_resources OWNER TO new_admin;

-- 删除空模式
DROP SCHEMA hr;

-- 删除模式及其所有对象
DROP SCHEMA hr CASCADE;

-- 将对象移动到另一个模式
ALTER TABLE hr.employees SET SCHEMA public;
ALTER TABLE employees SET SCHEMA production;

5.9.5 模式使用示例

-- 多租户数据库设计
CREATE SCHEMA tenant_a;
CREATE SCHEMA tenant_b;

CREATE TABLE tenant_a.users (id SERIAL, name TEXT);
CREATE TABLE tenant_b.users (id SERIAL, name TEXT);

-- 按模式查询
SELECT * FROM tenant_a.users;
SELECT * FROM tenant_b.users;

5.10 表管理

5.10.1 重命名表

ALTER TABLE old_table_name RENAME TO new_table_name;

5.10.2 修改表所有者

ALTER TABLE users OWNER TO new_owner;

5.10.3 清空表

-- 删除所有行（保留表结构，可重置序列）
TRUNCATE TABLE users RESTART IDENTITY;

-- 级联清空多个表
TRUNCATE TABLE users, orders, products RESTART IDENTITY CASCADE;

-- 只清空（不重置序列）
TRUNCATE TABLE users;

5.10.4 复制表

-- 复制表结构
CREATE TABLE users_copy (LIKE users INCLUDING ALL);

-- 复制表结构和数据
CREATE TABLE users_backup AS SELECT * FROM users;

-- 复制带条件的表
CREATE TABLE active_users AS 
SELECT * FROM users WHERE status = true;

-- 复制到远程数据库
CREATE TABLE remote_users (LIKE users INCLUDING ALL);

5.10.5 查看表信息

-- 列出当前模式所有表
\dt

-- 列出所有模式中的表
\dt *.*

-- 查看表结构
\d users

-- 查看表详细信息
\d+ users

-- 查看表大小
SELECT pg_size_pretty(pg_total_relation_size('users'));
SELECT pg_size_pretty(pg_relation_size('users'));

-- 查看表占用空间
SELECT 
    relname AS table_name,
    pg_size_pretty(pg_total_relation_size(relid)) AS total_size
FROM pg_catalog.pg_statio_user_tables
WHERE schemaname = 'public'
ORDER BY pg_total_relation_size(relid) DESC;

5.11 注释管理

-- 给表添加注释
COMMENT ON TABLE users IS '用户信息表';

-- 给列添加注释
COMMENT ON COLUMN users.username IS '用户名，唯一标识';

-- 查看注释
SELECT obj_description('users'::regclass);
SELECT col_description('users'::regclass, 2);  -- 2 是列的序号

5.12 本章小结

本章介绍了 PostgreSQL 的表设计和约束管理：

主题 关键点
约束类型 NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, CHECK
模式管理 CREATE SCHEMA, search_path, 模式命名空间
表维护 RENAME, TRUNCATE, COPY
最佳实践 合理使用约束，保证数据完整性

📌 下一章预告

下一章将深入介绍 PostgreSQL 的查询语法，包括复杂查询、窗口函数和高级查询技巧。