印刻数据库

外观

PostgreSQL 主键与外键设计 ​

主键设计原则与最佳实践 ​

1. 主键的基本特性 ​

主键是表中用于唯一标识每行数据的列或列组合，具有以下特性：

  • 唯一性：主键值必须唯一，不能重复
  • 非空性：主键列不能包含NULL值
  • 稳定性：主键值应长期稳定，尽量避免更新
  • 简洁性：主键应尽可能简洁，避免使用过长的组合键
  • 高效性：主键应便于索引，提高查询性能

2. 主键类型选择 ​

序列主键（SERIAL/BIGSERIAL） ​

sql
-- 使用SERIAL创建自增主键（适用于小表）
CREATE TABLE users (
    user_id SERIAL PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    created_at TIMESTAMP DEFAULT NOW()
);

-- 使用BIGSERIAL创建自增主键（适用于大表，支持更大的数值范围）
CREATE TABLE orders (
    order_id BIGSERIAL PRIMARY KEY,
    user_id INT REFERENCES users(user_id),
    order_date TIMESTAMP DEFAULT NOW(),
    total_amount DECIMAL(10,2) NOT NULL
);

UUID主键 ​

sql
-- 安装uuid-ossp扩展
CREATE EXTENSION IF NOT EXISTS uuid-ossp;

-- 使用UUID作为主键
CREATE TABLE sessions (
    session_id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    user_id INT REFERENCES users(user_id),
    login_time TIMESTAMP DEFAULT NOW(),
    logout_time TIMESTAMP,
    ip_address INET
);

-- 或使用pgcrypto扩展生成UUID
CREATE EXTENSION IF NOT EXISTS pgcrypto;

CREATE TABLE api_tokens (
    token_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id INT REFERENCES users(user_id),
    token VARCHAR(255) UNIQUE NOT NULL,
    expires_at TIMESTAMP NOT NULL
);

复合主键 ​

sql
-- 使用复合主键（适用于关联表）
CREATE TABLE user_roles (
    user_id INT REFERENCES users(user_id),
    role_id INT REFERENCES roles(role_id),
    assigned_at TIMESTAMP DEFAULT NOW(),
    PRIMARY KEY (user_id, role_id) -- 复合主键
);

-- 订单商品关联表
CREATE TABLE order_items (
    order_id BIGINT REFERENCES orders(order_id),
    product_id INT REFERENCES products(product_id),
    quantity INT NOT NULL,
    unit_price DECIMAL(10,2) NOT NULL,
    PRIMARY KEY (order_id, product_id)
);

自然主键 ​

sql
-- 使用自然主键（适用于有天然唯一标识的场景）
CREATE TABLE countries (
    country_code CHAR(2) PRIMARY KEY, -- ISO国家代码作为主键
    country_name VARCHAR(100) NOT NULL,
    population BIGINT
);

-- 产品SKU作为主键
CREATE TABLE products (
    sku VARCHAR(20) PRIMARY KEY, -- 库存单元号作为主键
    product_name VARCHAR(100) NOT NULL,
    category_id INT REFERENCES categories(category_id),
    price DECIMAL(10,2) NOT NULL
);

3. 主键设计最佳实践 ​

  1. 优先使用代理主键：对于大多数表，建议使用代理主键（如SERIAL、UUID）而非自然主键
  2. 根据表大小选择合适的主键类型：小表使用SERIAL，大表使用BIGSERIAL，分布式系统使用UUID
  3. 避免使用业务字段作为主键：业务字段可能会变化，影响主键稳定性
  4. 复合主键不宜过长：复合主键包含的列数不宜过多，一般不超过3列
  5. 考虑索引性能：主键会自动创建唯一索引，应考虑索引的存储和查询性能
  6. 避免使用UUID v1：UUID v1包含时间戳和MAC地址，可能存在隐私问题和性能问题
  7. 使用UUID v4：UUID v4是随机生成的，更适合作为主键

外键设计原则与最佳实践 ​

1. 外键的基本概念 ​

外键是表中用于建立与其他表关联关系的列或列组合，具有以下作用：

  • 维护数据完整性：确保引用的数据存在
  • 建立表间关系：明确表与表之间的关联关系
  • 实现级联操作：支持级联更新和级联删除
  • 提高查询性能：外键会自动创建索引（PostgreSQL 12+）

2. 外键约束配置 ​

基本外键约束 ​

sql
-- 创建基本外键约束
CREATE TABLE orders (
    order_id BIGSERIAL PRIMARY KEY,
    user_id INT REFERENCES users(user_id), -- 外键引用users表的user_id
    order_date TIMESTAMP DEFAULT NOW(),
    total_amount DECIMAL(10,2) NOT NULL
);

-- 显式指定外键名称
CREATE TABLE order_items (
    order_item_id BIGSERIAL PRIMARY KEY,
    order_id BIGINT,
    product_id INT,
    quantity INT NOT NULL,
    unit_price DECIMAL(10,2) NOT NULL,
    CONSTRAINT fk_order_items_order FOREIGN KEY (order_id) REFERENCES orders(order_id),
    CONSTRAINT fk_order_items_product FOREIGN KEY (product_id) REFERENCES products(product_id)
);

级联操作配置 ​

sql
-- 级联删除：当父表记录被删除时，子表相关记录也被删除
CREATE TABLE comments (
    comment_id BIGSERIAL PRIMARY KEY,
    post_id BIGINT REFERENCES posts(post_id) ON DELETE CASCADE,
    user_id INT REFERENCES users(user_id),
    content TEXT NOT NULL,
    created_at TIMESTAMP DEFAULT NOW()
);

-- 级联更新：当父表主键被更新时，子表外键也被更新
CREATE TABLE employee_departments (
    employee_id INT,
    department_id INT,
    assigned_at TIMESTAMP DEFAULT NOW(),
    PRIMARY KEY (employee_id, department_id),
    CONSTRAINT fk_emp_dept_emp FOREIGN KEY (employee_id) REFERENCES employees(employee_id) ON UPDATE CASCADE,
    CONSTRAINT fk_emp_dept_dept FOREIGN KEY (department_id) REFERENCES departments(department_id) ON UPDATE CASCADE
);

-- 设置为NULL：当父表记录被删除时，子表外键字段设为NULL
CREATE TABLE products (
    product_id INT PRIMARY KEY,
    category_id INT REFERENCES categories(category_id) ON DELETE SET NULL,
    product_name VARCHAR(100) NOT NULL,
    price DECIMAL(10,2) NOT NULL
);

-- 禁止删除：当子表存在相关记录时，禁止删除父表记录（默认行为）
CREATE TABLE orders (
    order_id BIGSERIAL PRIMARY KEY,
    user_id INT REFERENCES users(user_id) ON DELETE RESTRICT,
    order_date TIMESTAMP DEFAULT NOW(),
    total_amount DECIMAL(10,2) NOT NULL
);

3. 外键设计最佳实践 ​

  1. 明确表间关系：外键应准确反映表与表之间的业务关系
  2. 合理设置级联操作：根据业务需求选择合适的级联操作策略
  3. 避免循环引用：尽量避免表之间的循环外键引用
  4. 考虑性能影响：外键约束会影响写入性能，对于高频写入的表需谨慎使用
  5. 使用索引优化查询：确保外键列上有合适的索引（PostgreSQL 12+会自动创建）
  6. 保持外键简洁：外键应尽可能简洁，避免使用复杂的复合外键
  7. 定期检查外键完整性：使用系统视图检查外键关系

主键与外键的性能优化 ​

1. 主键索引优化 ​

sql
-- 查看主键索引大小
SELECT 
    i.relname AS index_name,
    t.relname AS table_name,
    pg_size_pretty(pg_relation_size(i.oid)) AS index_size
FROM 
    pg_index ix
JOIN 
    pg_class i ON i.oid = ix.indexrelid
JOIN 
    pg_class t ON t.oid = ix.indrelid
WHERE 
    ix.indisprimary;

-- 重建主键索引（如果存在碎片）
REINDEX INDEX idx_table_pkey;

-- 或重建整个表的索引
REINDEX TABLE users;

2. 外键性能优化 ​

sql
-- 查看外键约束
SELECT 
    conname AS constraint_name,
    conrelid::regclass AS table_name,
    a.attname AS column_name,
    confrelid::regclass AS referenced_table,
    af.attname AS referenced_column
FROM 
    pg_constraint c
JOIN 
    pg_attribute a ON a.attrelid = c.conrelid AND a.attnum = ANY(c.conkey)
JOIN 
    pg_attribute af ON af.attrelid = c.confrelid AND af.attnum = ANY(c.confkey)
WHERE 
    contype = 'f';

-- 检查外键列是否有索引
SELECT 
    c.conname AS constraint_name,
    c.conrelid::regclass AS table_name,
    a.attname AS column_name,
    CASE 
        WHEN EXISTS (
            SELECT 1 FROM pg_index i 
            WHERE i.indrelid = c.conrelid AND (i.indkey::int[]) @> ARRAY[a.attnum]
        ) THEN '有索引'
        ELSE '无索引'
    END AS has_index
FROM 
    pg_constraint c
JOIN 
    pg_attribute a ON a.attrelid = c.conrelid AND a.attnum = ANY(c.conkey)
WHERE 
    contype = 'f';

3. 批量操作优化 ​

sql
-- 禁用外键约束（批量插入前，谨慎使用）
ALTER TABLE order_items DISABLE TRIGGER ALL;

-- 执行批量插入
COPY order_items FROM '/path/to/order_items.csv' WITH CSV HEADER;

-- 启用外键约束并验证
ALTER TABLE order_items ENABLE TRIGGER ALL;

-- 验证外键完整性
SELECT * FROM order_items WHERE order_id NOT IN (SELECT order_id FROM orders);
SELECT * FROM order_items WHERE product_id NOT IN (SELECT product_id FROM products);

主键与外键的维护与管理 ​

1. 检查主键与外键完整性 ​

sql
-- 检查主键完整性（查找重复值）
SELECT user_id, COUNT(*) FROM users GROUP BY user_id HAVING COUNT(*) > 1;

-- 检查外键完整性
SELECT 
    'orders' AS table_name,
    order_id,
    'user_id' AS column_name,
    user_id AS invalid_value
FROM 
    orders
WHERE 
    user_id NOT IN (SELECT user_id FROM users)
UNION ALL
SELECT 
    'order_items' AS table_name,
    order_item_id,
    'order_id' AS column_name,
    order_id AS invalid_value
FROM 
    order_items
WHERE 
    order_id NOT IN (SELECT order_id FROM orders);

-- 使用系统函数检查外键完整性
SELECT constraint_name, table_name, error 
FROM check_foreign_keys();

2. 修改主键与外键 ​

sql
-- 添加主键约束
ALTER TABLE products
ADD CONSTRAINT pk_products PRIMARY KEY (product_id);

-- 删除主键约束
ALTER TABLE temp_table
DROP CONSTRAINT IF EXISTS pk_temp_table;

-- 添加外键约束
ALTER TABLE orders
ADD CONSTRAINT fk_orders_user
FOREIGN KEY (user_id) REFERENCES users(user_id)
ON DELETE CASCADE;

-- 删除外键约束
ALTER TABLE orders
DROP CONSTRAINT IF EXISTS fk_orders_user;

-- 修改外键约束的级联行为
-- 注意：PostgreSQL不支持直接修改外键约束，需要先删除再重建
ALTER TABLE orders
DROP CONSTRAINT IF EXISTS fk_orders_user;

ALTER TABLE orders
ADD CONSTRAINT fk_orders_user
FOREIGN KEY (user_id) REFERENCES users(user_id)
ON DELETE SET NULL;

3. 监控主键与外键性能 ​

sql
-- 监控主键索引使用情况
SELECT 
    t.relname AS table_name,
    i.relname AS index_name,
    idx_scan AS scan_count,
    idx_tup_read AS tuples_read,
    idx_tup_fetch AS tuples_fetched
FROM 
    pg_stat_user_indexes ui
JOIN 
    pg_class i ON i.oid = ui.indexrelid
JOIN 
    pg_class t ON t.oid = ui.relid
JOIN 
    pg_index ix ON ix.indexrelid = i.oid
WHERE 
    ix.indisprimary;

-- 监控外键相关的写入操作
SELECT 
    relname AS table_name,
    n_tup_ins AS insert_count,
    n_tup_upd AS update_count,
    n_tup_del AS delete_count,
    last_autovacuum
FROM 
    pg_stat_user_tables
ORDER BY 
    (n_tup_ins + n_tup_upd + n_tup_del) DESC;

常见问题与解决方案 ​

1. 主键冲突问题 ​

sql
-- 问题：插入数据时出现主键冲突
-- 解决方案1：使用ON CONFLICT子句
INSERT INTO users (user_id, username, email)
VALUES (1, 'john_doe', 'john@example.com')
ON CONFLICT (user_id) DO UPDATE
SET username = EXCLUDED.username, email = EXCLUDED.email;

-- 解决方案2：使用SERIAL/BIGSERIAL自动生成主键
INSERT INTO users (username, email)
VALUES ('jane_smith', 'jane@example.com')
RETURNING user_id;

-- 解决方案3：使用UUID避免冲突
INSERT INTO sessions (user_id, ip_address)
VALUES (1, '192.168.1.1')
RETURNING session_id;

2. 外键约束违反 ​

sql
-- 问题：删除父表记录时违反外键约束
-- 解决方案1：使用级联删除
ALTER TABLE orders
ADD CONSTRAINT fk_orders_user
FOREIGN KEY (user_id) REFERENCES users(user_id)
ON DELETE CASCADE;

-- 解决方案2：先删除子表相关记录
DELETE FROM orders WHERE user_id = 1;
DELETE FROM users WHERE user_id = 1;

-- 解决方案3：临时禁用外键约束（谨慎使用）
ALTER TABLE orders DISABLE TRIGGER ALL;
DELETE FROM users WHERE user_id = 1;
ALTER TABLE orders ENABLE TRIGGER ALL;

3. 主键更新问题 ​

sql
-- 问题：需要更新主键值
-- 解决方案1：使用级联更新
ALTER TABLE orders
ADD CONSTRAINT fk_orders_user
FOREIGN KEY (user_id) REFERENCES users(user_id)
ON UPDATE CASCADE;

-- 执行主键更新
UPDATE users SET user_id = 1001 WHERE user_id = 1;

-- 解决方案2：不更新主键，而是创建新记录并迁移数据
-- 这种方法更安全，避免了级联更新可能带来的性能问题

主键与外键设计案例分析 ​

1. 电商系统设计 ​

sql
-- 用户表（使用BIGSERIAL主键）
CREATE TABLE users (
    user_id BIGSERIAL PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT NOW()
);

-- 商品表（使用SERIAL主键）
CREATE TABLE products (
    product_id SERIAL PRIMARY KEY,
    product_name VARCHAR(100) NOT NULL,
    category_id INT REFERENCES categories(category_id) ON DELETE SET NULL,
    price DECIMAL(10,2) NOT NULL,
    stock_quantity INT DEFAULT 0
);

-- 订单表（使用BIGSERIAL主键，外键关联用户）
CREATE TABLE orders (
    order_id BIGSERIAL PRIMARY KEY,
    user_id BIGINT REFERENCES users(user_id) ON DELETE SET NULL,
    order_date TIMESTAMP DEFAULT NOW(),
    total_amount DECIMAL(10,2) NOT NULL,
    status VARCHAR(20) DEFAULT 'pending'
);

-- 订单项表（复合外键关联订单和商品）
CREATE TABLE order_items (
    order_item_id BIGSERIAL PRIMARY KEY,
    order_id BIGINT REFERENCES orders(order_id) ON DELETE CASCADE,
    product_id INT REFERENCES products(product_id) ON DELETE SET NULL,
    quantity INT NOT NULL,
    unit_price DECIMAL(10,2) NOT NULL
);

2. 社交媒体系统设计 ​

sql
-- 用户表
CREATE TABLE users (
    user_id BIGSERIAL PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    created_at TIMESTAMP DEFAULT NOW()
);

-- 帖子表
CREATE TABLE posts (
    post_id BIGSERIAL PRIMARY KEY,
    user_id BIGINT REFERENCES users(user_id) ON DELETE CASCADE,
    content TEXT NOT NULL,
    created_at TIMESTAMP DEFAULT NOW(),
    likes_count INT DEFAULT 0
);

-- 关注关系表（复合主键）
CREATE TABLE follows (
    follower_id BIGINT REFERENCES users(user_id) ON DELETE CASCADE,
    following_id BIGINT REFERENCES users(user_id) ON DELETE CASCADE,
    followed_at TIMESTAMP DEFAULT NOW(),
    PRIMARY KEY (follower_id, following_id)
);

-- 评论表
CREATE TABLE comments (
    comment_id BIGSERIAL PRIMARY KEY,
    post_id BIGINT REFERENCES posts(post_id) ON DELETE CASCADE,
    user_id BIGINT REFERENCES users(user_id) ON DELETE CASCADE,
    content TEXT NOT NULL,
    created_at TIMESTAMP DEFAULT NOW()
);

常见问题（FAQ） ​

Q1：什么时候使用SERIAL主键，什么时候使用UUID？ ​

A1：

  • SERIAL/BIGSERIAL：适用于大多数传统应用，性能好，存储空间小，易于理解和调试
  • UUID：适用于分布式系统、微服务架构、需要离线生成主键的场景，避免了主键冲突问题

Q2：复合主键和单一主键哪个更好？ ​

A2：

  • 单一主键：更简洁，查询性能更好，便于维护，推荐优先使用
  • 复合主键：适用于关联表（如多对多关系），准确反映业务关系

Q3：外键会影响性能吗？ ​

A3：外键约束会对写入性能产生一定影响，因为每次写入操作都需要检查外键完整性。但外键带来的数据完整性保障通常大于性能损失，对于大多数应用是值得的。对于高频写入的表，可以考虑在应用层实现外键逻辑。

Q4：如何禁用和启用外键约束？ ​

A4：

sql
-- 禁用表的所有触发器（包括外键约束）
ALTER TABLE table_name DISABLE TRIGGER ALL;

-- 启用表的所有触发器
ALTER TABLE table_name ENABLE TRIGGER ALL;

Q5：如何检查数据库中的所有主键和外键？ ​

A5：

sql
-- 查看所有主键
SELECT 
    conname AS constraint_name,
    conrelid::regclass AS table_name
FROM 
    pg_constraint
WHERE 
    contype = 'p';

-- 查看所有外键
SELECT 
    conname AS constraint_name,
    conrelid::regclass AS table_name,
    confrelid::regclass AS referenced_table
FROM 
    pg_constraint
WHERE 
    contype = 'f';