PostgreSQL教程FG024-PG表设计进阶：复合约束/检查约束/默认值

本文档详细介绍PostgreSQL表设计中的复合约束、检查约束和默认值的使用方法，风哥教程参考PostgreSQL官方文档内容，适合数据库管理员和开发人员在生产环境中进行表设计。

Part01-基础概念与理论知识

1.1 PostgreSQL约束概念

约束是数据库表中的规则，用于确保数据的完整性和一致性。PostgreSQL支持多种类型的约束，包括主键约束、外键约束、唯一约束、检查约束等。更多视频教程www.fgedu.net.cn

约束的作用：

确保数据完整性
防止无效数据的插入
维护数据一致性
提高查询性能

1.2 PostgreSQL约束类型

— PostgreSQL约束类型

— 1. 主键约束（PRIMARY KEY）
— 唯一标识表中的记录
CREATE TABLE fgedu_users (
id SERIAL PRIMARY KEY,
username VARCHAR(50) NOT NULL
);

— 2. 外键约束（FOREIGN KEY）
— 建立表之间的关系
CREATE TABLE fgedu_orders (
id SERIAL PRIMARY KEY,
user_id INTEGER REFERENCES fgedu_users(id)
);

— 3. 唯一约束（UNIQUE）
— 确保列值唯一
CREATE TABLE fgedu_users (
id SERIAL PRIMARY KEY,
username VARCHAR(50) UNIQUE NOT NULL
);

— 4. 非空约束（NOT NULL）
— 确保列值不为空
CREATE TABLE fgedu_users (
id SERIAL PRIMARY KEY,
username VARCHAR(50) NOT NULL
);

— 5. 检查约束（CHECK）
— 确保列值满足指定条件
CREATE TABLE fgedu_users (
id SERIAL PRIMARY KEY,
age INTEGER CHECK (age >= 18)
);

— 6. 复合约束
— 多个列组合的约束
CREATE TABLE fgedu_users (
id SERIAL,
username VARCHAR(50),
email VARCHAR(100),
PRIMARY KEY (id),
UNIQUE (username, email)
);

1.3 PostgreSQL默认值

默认值是当插入数据时，如果没有指定该列的值，系统会自动使用的值。默认值可以是常量、表达式或函数。学习交流加群风哥微信: itpux-com

— PostgreSQL默认值

— 1. 常量默认值
CREATE TABLE fgedu_users (
id SERIAL PRIMARY KEY,
username VARCHAR(50) NOT NULL,
active BOOLEAN DEFAULT true
);

— 2. 表达式默认值
CREATE TABLE fgedu_orders (
id SERIAL PRIMARY KEY,
order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

— 3. 函数默认值
CREATE TABLE fgedu_products (
id SERIAL PRIMARY KEY,
name VARCHAR(100) NOT NULL,
created_at TIMESTAMP DEFAULT now()
);

— 4. NULL默认值
CREATE TABLE fgedu_users (
id SERIAL PRIMARY KEY,
username VARCHAR(50) NOT NULL,
bio TEXT DEFAULT NULL
);

风哥提示：默认值的设置要根据业务需求来确定，避免设置不合理的默认值导致数据不一致。学习交流加群风哥QQ113257174

Part02-生产环境规划与建议

2.1 约束使用最佳实践

— 约束使用最佳实践

— 1. 合理使用主键
— 选择合适的主键类型，如SERIAL、UUID等
CREATE TABLE fgedu_users (
id SERIAL PRIMARY KEY, — 自增主键
— 或
id UUID PRIMARY KEY DEFAULT gen_random_uuid() — UUID主键
);

— 2. 外键约束
— 明确指定级联操作
CREATE TABLE fgedu_orders (
id SERIAL PRIMARY KEY,
user_id INTEGER REFERENCES fgedu_users(id) ON DELETE CASCADE
);

— 3. 唯一约束
— 对于需要唯一的列添加唯一约束
CREATE TABLE fgedu_users (
id SERIAL PRIMARY KEY,
username VARCHAR(50) UNIQUE NOT NULL,
email VARCHAR(100) UNIQUE NOT NULL
);

— 4. 检查约束
— 确保数据符合业务规则
CREATE TABLE fgedu_products (
id SERIAL PRIMARY KEY,
name VARCHAR(100) NOT NULL,
price DECIMAL(10,2) CHECK (price > 0),
stock INTEGER CHECK (stock >= 0)
);

— 5. 复合约束
— 多个列组合的唯一约束
CREATE TABLE fgedu_user_roles (
user_id INTEGER REFERENCES fgedu_users(id),
role_id INTEGER REFERENCES fgedu_roles(id),
PRIMARY KEY (user_id, role_id) — 复合主键
);

2.2 默认值设置策略

默认值设置策略：

布尔类型：设置为false或true，根据业务需求
时间类型：设置为CURRENT_TIMESTAMP或now()
数值类型：设置为0或其他合理的默认值
字符串类型：根据业务需求设置为空字符串或NULL
枚举类型：设置为默认枚举值

风哥教程针对风哥教程针对生产环境建议：默认值的设置要考虑业务逻辑的合理性，避免使用可能导致数据不一致的默认值。from PostgreSQL视频:www.itpux.com

2.3 约束性能影响

— 约束性能影响

— 1. 主键约束
— 会自动创建索引，提高查询性能
— 但插入操作会增加索引维护成本

— 2. 唯一约束
— 会自动创建索引，提高查询性能
— 但插入操作会增加索引维护成本

— 3. 外键约束
— 会增加插入和更新操作的成本
— 建议在查询频繁的场景使用

— 4. 检查约束
— 会增加插入和更新操作的成本
— 建议在数据完整性要求高的场景使用

— 5. 性能优化建议
— 只在必要的场景使用约束
— 合理设计约束，避免过度约束
— 定期维护约束相关的索引

Part03-生产环境项目实施方案

3.1 复合约束实现

3.1.1 复合主键

— 复合主键实现

— 1. 创建复合主键
CREATE TABLE fgedu_user_roles (
user_id INTEGER NOT NULL,
role_id INTEGER NOT NULL,
assigned_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (user_id, role_id)
);

— 2. 向复合主键表插入数据
INSERT INTO fgedu_user_roles (user_id, role_id) VALUES (1, 1);
INSERT INTO fgedu_user_roles (user_id, role_id) VALUES (1, 2);
INSERT INTO fgedu_user_roles (user_id, role_id) VALUES (2, 1);

— 3. 尝试插入重复数据（会失败）
INSERT INTO fgedu_user_roles (user_id, role_id) VALUES (1, 1);
— 错误: 重复键违反唯一约束 “fgedu_user_roles_pkey”
— 详细: 键 (user_id, role_id)=(1, 1) 已经存在。

3.1.2 复合唯一约束

— 复合唯一约束实现

— 1. 创建复合唯一约束
CREATE TABLE fgedu_users (
id SERIAL PRIMARY KEY,
first_name VARCHAR(50) NOT NULL,
last_name VARCHAR(50) NOT NULL,
email VARCHAR(100) NOT NULL,
UNIQUE (first_name, last_name, email)
);

— 2. 向表插入数据
INSERT INTO fgedu_users (first_name, last_name, email)
VALUES (‘张’, ‘三’, ‘zhangsan@fgedu.net.cn’);

— 3. 尝试插入重复数据（会失败）
INSERT INTO fgedu_users (first_name, last_name, email)
VALUES (‘张’, ‘三’, ‘zhangsan@fgedu.net.cn’);
— 错误: 重复键违反唯一约束 “fgedu_users_first_name_last_name_email_key”
— 详细: 键 (first_name, last_name, email)=(张, 三, zhangsan@fgedu.net.cn) 已经存在。

3.2 检查约束实现

3.2.1 基本检查约束

— 基本检查约束实现

— 1. 创建检查约束
CREATE TABLE fgedu_products (
id SERIAL PRIMARY KEY,
name VARCHAR(100) NOT NULL,
price DECIMAL(10,2) CHECK (price > 0),
stock INTEGER CHECK (stock >= 0),
category VARCHAR(50) CHECK (category IN (‘电子产品’, ‘服装’, ‘食品’))
);

— 2. 向表插入有效数据
INSERT INTO fgedu_products (name, price, stock, category)
VALUES (‘手机’, 5999.99, 100, ‘电子产品’);

— 3. 尝试插入无效数据（价格为负数）
INSERT INTO fgedu_products (name, price, stock, category)
VALUES (‘电脑’, -4999.99, 50, ‘电子产品’);
— 错误: 关系 “fgedu_products” 的新行违反检查约束 “fgedu_products_price_check”
— 详细: 失败, 行包含 (-4999.99)

— 4. 尝试插入无效数据（库存为负数）
INSERT INTO fgedu_products (name, price, stock, category)
VALUES (‘平板’, 2999.99, -10, ‘电子产品’);
— 错误: 关系 “fgedu_products” 的新行违反检查约束 “fgedu_products_stock_check”
— 详细: 失败, 行包含 (-10)

— 5. 尝试插入无效数据（类别不在允许范围内）
INSERT INTO fgedu_products (name, price, stock, category)
VALUES (‘汽车’, 100000.00, 10, ‘交通工具’);
— 错误: 关系 “fgedu_products” 的新行违反检查约束 “fgedu_products_category_check”
— 详细: 失败, 行包含 (交通工具)

3.2.2 复杂检查约束

— 复杂检查约束实现

— 1. 创建复杂检查约束
CREATE TABLE fgedu_orders (
id SERIAL PRIMARY KEY,
order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
delivery_date TIMESTAMP,
CHECK (delivery_date > order_date OR delivery_date IS NULL)
);

— 2. 向表插入有效数据
INSERT INTO fgedu_orders (delivery_date) VALUES (‘2026-04-10 10:00:00’);

— 3. 尝试插入无效数据（ delivery_date 早于 order_date）
INSERT INTO fgedu_orders (order_date, delivery_date)
VALUES (‘2026-04-08 10:00:00’, ‘2026-04-07 10:00:00’);
— 错误: 关系 “fgedu_orders” 的新行违反检查约束 “fgedu_orders_check”
— 详细: 失败, 行包含 (2026-04-08 10:00:00, 2026-04-07 10:00:00)

3.3 默认值实现

3.3.1 基本默认值

— 基本默认值实现

— 1. 创建带有默认值的表
CREATE TABLE fgedu_users (
id SERIAL PRIMARY KEY,
username VARCHAR(50) NOT NULL,
email VARCHAR(100) NOT NULL,
active BOOLEAN DEFAULT true,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
last_login TIMESTAMP DEFAULT NULL
);

— 2. 插入数据时不指定默认值列
INSERT INTO fgedu_users (username, email)
VALUES (‘zhangsan’, ‘zhangsan@fgedu.net.cn’);

3.3.2 表达式默认值

— 表达式默认值实现

— 1. 创建带有表达式默认值的表
CREATE TABLE fgedu_products (
id SERIAL PRIMARY KEY,
name VARCHAR(100) NOT NULL,
price DECIMAL(10,2) NOT NULL,
tax DECIMAL(10,2) DEFAULT price * 0.13,
total_price DECIMAL(10,2) DEFAULT price * 1.13,
created_at TIMESTAMP DEFAULT now()
);

— 2. 插入数据
INSERT INTO fgedu_products (name, price)
VALUES (‘手机’, 5999.99);

— 3. 查看插入的数据
SELECT * FROM fgedu_products;
— id | name | price | tax | total_price | created_at
— —-+——+———-+———-+————-+——————————-
— 1 | 手机 | 5999.99 | 779.9987 | 6779.9887 | 2026-04-07 14:05:00.123456

风哥提示：默认值的表达式计算是在插入时进行的，因此如果后续修改了price列的值，tax和total_price列不会自动更新。如果需要自动更新，建议使用触发器或计算列。更多学习教程公众号风哥教程itpux_com

Part04-生产案例与实战讲解

4.1 复合约束案例

— 复合约束案例：用户角色管理

— 1. 创建用户表
CREATE TABLE fgedu_users (
id SERIAL PRIMARY KEY,
username VARCHAR(50) UNIQUE NOT NULL,
email VARCHAR(100) UNIQUE NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

— 2. 创建角色表
CREATE TABLE fgedu_roles (
id SERIAL PRIMARY KEY,
name VARCHAR(50) UNIQUE NOT NULL,
description TEXT
);

— 3. 创建用户角色关联表（使用复合主键）
CREATE TABLE fgedu_user_roles (
user_id INTEGER NOT NULL REFERENCES fgedu_users(id) ON DELETE CASCADE,
role_id INTEGER NOT NULL REFERENCES fgedu_roles(id) ON DELETE CASCADE,
assigned_by INTEGER REFERENCES fgedu_users(id),
assigned_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (user_id, role_id)
);

— 4. 插入测试数据
INSERT INTO fgedu_users (username, email)
VALUES (‘admin’, ‘admin@fgedu.net.cn’),
(‘user1’, ‘user1@fgedu.net.cn’),
(‘user2’, ‘user2@fgedu.net.cn’);

INSERT INTO fgedu_roles (name, description)
VALUES (‘管理员’, ‘系统管理员’),
(‘普通用户’, ‘普通系统用户’),
(‘审核员’, ‘内容审核员’);

— 5. 分配角色
INSERT INTO fgedu_user_roles (user_id, role_id, assigned_by)
VALUES (1, 1, 1), — admin 分配管理员角色给自己
(2, 2, 1), — admin 分配普通用户角色给 user1
(3, 2, 1), — admin 分配普通用户角色给 user2
(3, 3, 1); — admin 分配审核员角色给 user2

— 6. 查看用户角色
SELECT u.username, r.name AS role_name
FROM fgedu_users u
JOIN fgedu_user_roles ur ON u.id = ur.user_id
JOIN fgedu_roles r ON ur.role_id = r.id
ORDER BY u.username, r.name;
— username | role_name
— ———-+———–
— admin | 管理员
— user1 | 普通用户
— user2 | 普通用户
— user2 | 审核员

4.2 检查约束案例

— 检查约束案例：商品库存管理

— 1. 创建商品表
CREATE TABLE fgedu_products (
id SERIAL PRIMARY KEY,
name VARCHAR(100) NOT NULL,
sku VARCHAR(50) UNIQUE NOT NULL,
price DECIMAL(10,2) CHECK (price > 0),
cost_price DECIMAL(10,2) CHECK (cost_price > 0),
stock INTEGER CHECK (stock >= 0),
min_stock INTEGER CHECK (min_stock >= 0),
status VARCHAR(20) CHECK (status IN (‘在售’, ‘下架’, ‘缺货’)),
CHECK (price >= cost_price),
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

— 2. 插入测试数据
INSERT INTO fgedu_products (name, sku, price, cost_price, stock, min_stock, status)
VALUES (‘iPhone 15’, ‘IP15-256G’, 5999.99, 4999.99, 100, 10, ‘在售’),
(‘MacBook Pro’, ‘MBP-14-16G’, 12999.99, 9999.99, 50, 5, ‘在售’),
(‘AirPods Pro’, ‘APP-2’, 1999.99, 1499.99, 0, 20, ‘缺货’);

— 3. 尝试插入无效数据（价格低于成本价）
INSERT INTO fgedu_products (name, sku, price, cost_price, stock, min_stock, status)
VALUES (‘iPad Pro’, ‘IPAD-12-128G’, 4999.99, 5999.99, 30, 5, ‘在售’);
— 错误: 关系 “fgedu_products” 的新行违反检查约束 “fgedu_products_check”
— 详细: 失败, 行包含 (4999.99, 5999.99)

— 4. 尝试插入无效数据（库存为负数）
INSERT INTO fgedu_products (name, sku, price, cost_price, stock, min_stock, status)
VALUES (‘Apple Watch’, ‘AW-9’, 2999.99, 2499.99, -10, 5, ‘在售’);
— 错误: 关系 “fgedu_products” 的新行违反检查约束 “fgedu_products_stock_check”
— 详细: 失败, 行包含 (-10)

4.3 默认值案例

— 默认值案例：用户注册系统

— 1. 创建用户表
CREATE TABLE fgedu_users (
id SERIAL PRIMARY KEY,
username VARCHAR(50) UNIQUE NOT NULL,
email VARCHAR(100) UNIQUE NOT NULL,
password VARCHAR(100) NOT NULL,
active BOOLEAN DEFAULT true,
role VARCHAR(20) DEFAULT ‘普通用户’,
registration_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
last_login TIMESTAMP DEFAULT NULL,
failed_login_attempts INTEGER DEFAULT 0,
locked_until TIMESTAMP DEFAULT NULL
);

— 2. 插入新用户（只提供必要字段）
INSERT INTO fgedu_users (username, email, password)
VALUES (‘zhangsan’, ‘zhangsan@fgedu.net.cn’, ‘password123’),
(‘lisi’, ‘lisi@fgedu.net.cn’, ‘password456’),
(‘wangwu’, ‘wangwu@fgedu.net.cn’, ‘password789’);

— 4. 更新用户登录时间
UPDATE fgedu_users
SET last_login = CURRENT_TIMESTAMP
WHERE username = ‘zhangsan’;

— 5. 查看更新结果
SELECT username, last_login FROM fgedu_users WHERE username = ‘zhangsan’;
— username | last_login
— ———-+—————————–
— zhangsan | 2026-04-07 14:25:00.123456

风哥教程针对风哥教程针对生产环境建议：在生产环境中，密码应该使用加密存储，而不是明文存储。这里为了演示方便，使用了明文密码。

Part05-风哥经验总结与分享

5.1 约束使用技巧

约束使用技巧：

合理使用主键：选择合适的主键类型，如SERIAL、UUID等
外键约束：明确指定级联操作，避免数据不一致
唯一约束：对于需要唯一的列添加唯一约束
检查约束：确保数据符合业务规则
复合约束：使用复合约束确保多列组合的唯一性
默认值：根据业务需求设置合理的默认值
性能考虑：只在必要的场景使用约束，避免过度约束
约束命名：为约束指定有意义的名称，便于维护

5.2 约束常见问题解决

— 约束常见问题解决

— 1. 主键冲突
— 问题：插入数据时出现主键冲突
— 解决：
— – 检查是否存在重复的主键值
— – 使用SERIAL或UUID类型自动生成主键
— – 对于复合主键，确保组合值唯一

— 2. 外键约束错误
— 问题：插入或更新数据时出现外键约束错误
— 解决：
— – 确保引用的外键值存在
— – 检查级联操作设置是否合理
— – 考虑使用延迟约束

— 3. 检查约束错误
— 问题：插入或更新数据时出现检查约束错误
— 解决：
— – 确保数据符合检查约束条件
— – 检查检查约束的逻辑是否正确
— – 考虑是否需要调整检查约束

— 4. 默认值不生效
— 问题：默认值没有按预期生效
— 解决：
— – 检查默认值的设置是否正确
— – 确保插入数据时没有显式指定该列的值
— – 检查表达式默认值的计算逻辑

— 5. 约束性能问题
— 问题：约束导致插入或更新操作变慢
— 解决：
— – 评估约束的必要性
— – 考虑使用触发器替代复杂的检查约束
— – 定期维护约束相关的索引

5.3 约束维护与管理

— 约束维护与管理

— 1. 查看表的约束
SELECT conname, contype, condef
FROM pg_constraint
WHERE conrelid = ‘fgedu_users’::regclass;

— 2. 添加约束
— 添加检查约束
ALTER TABLE fgedu_users
ADD CONSTRAINT fgedu_users_age_check CHECK (age >= 18);

— 添加唯一约束
ALTER TABLE fgedu_users
ADD CONSTRAINT fgedu_users_email_unique UNIQUE (email);

— 3. 删除约束
— 删除检查约束
ALTER TABLE fgedu_users
DROP CONSTRAINT fgedu_users_age_check;

— 删除唯一约束
ALTER TABLE fgedu_users
DROP CONSTRAINT fgedu_users_email_unique;

— 4. 修改约束
— 修改检查约束（需要先删除再添加）
ALTER TABLE fgedu_users
DROP CONSTRAINT fgedu_users_age_check;

ALTER TABLE fgedu_users
ADD CONSTRAINT fgedu_users_age_check CHECK (age >= 16);

— 5. 禁用约束（仅适用于外键约束）
ALTER TABLE fgedu_orders
DISABLE TRIGGER ALL;

— 启用约束
ALTER TABLE fgedu_orders
ENABLE TRIGGER ALL;

风哥提示：约束的维护和管理是数据库管理的重要部分，定期检查和调整约束可以提高数据库的性能和可靠性。在生产环境中，建议根据业务需求和性能考虑，合理使用和管理约束。

本文由风哥教程整理发布,仅用于学习测试使用,转载注明出处:http://www.fgedu.net.cn/10327.html