PostgreSQL 唯一约束与检查约束

约束的定义与作用

约束（Constraint）是数据库中用于确保数据完整性和一致性的规则，它定义了表中数据必须满足的条件。约束可以在表创建时定义，也可以在表创建后添加。

约束的类型

PostgreSQL支持多种约束类型：

主键约束（PRIMARY KEY）：唯一标识表中的每一行数据
唯一约束（UNIQUE）：确保列或列组合的值唯一
检查约束（CHECK）：确保列的值满足指定的条件
外键约束（FOREIGN KEY）：确保引用完整性
非空约束（NOT NULL）：确保列不接受空值

约束的优缺点

优点	缺点
确保数据完整性和一致性	增加写操作开销
提高数据质量	可能影响查询性能
简化应用逻辑	增加表结构复杂度
提供明确的数据规则	可能导致插入失败

唯一约束（UNIQUE）

唯一约束的定义

唯一约束确保表中指定的列或列组合的值在表中是唯一的，即不允许有重复值。唯一约束允许空值（NULL），但多个NULL值被视为不同的值。

唯一约束的创建

表创建时创建唯一约束

sql

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

-- 复合唯一约束
CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    order_number VARCHAR(20),
    customer_id INT,
    order_date DATE,
    UNIQUE (order_number, customer_id),
    FOREIGN KEY (customer_id) REFERENCES customers(id)
);

表创建后添加唯一约束

sql

-- 为现有表添加唯一约束
ALTER TABLE users
ADD CONSTRAINT uq_users_phone UNIQUE (phone);

-- 为现有表添加复合唯一约束
ALTER TABLE orders
ADD CONSTRAINT uq_orders_customer_date UNIQUE (customer_id, order_date);

使用CREATE UNIQUE INDEX创建

sql

-- 使用索引创建唯一约束
CREATE UNIQUE INDEX uq_users_username ON users(username);

-- 复合唯一索引
CREATE UNIQUE INDEX uq_orders_number_customer ON orders(order_number, customer_id);

唯一约束的使用场景

用户标识
- 用户名唯一
- 邮箱地址唯一
- 手机号码唯一
业务实体标识
- 订单编号唯一
- 产品编码唯一
- 发票号码唯一
防止重复操作
- 同一用户对同一资源的操作记录唯一
- 特定条件下的记录唯一
复合业务规则
- 同一客户在同一日期的订单唯一
- 同一产品在同一仓库的库存记录唯一

唯一约束的性能影响

写操作性能
- 插入和更新操作需要检查唯一性
- 唯一性检查会增加写操作的延迟
- 高并发场景下可能导致锁竞争
读操作性能
- 唯一约束自动创建唯一索引，提高查询性能
- 基于唯一键的查询效率高
- 支持索引覆盖扫描
存储开销
- 唯一约束需要存储空间存储索引
- 复合唯一约束的存储开销更大
- 索引维护需要额外开销

唯一约束与NULL值

sql

-- 唯一约束允许多个NULL值
INSERT INTO users (username, email) VALUES (NULL, 'user1@example.com');
INSERT INTO users (username, email) VALUES (NULL, 'user2@example.com');
-- 以上操作都会成功，因为NULL被视为不同的值

-- 但以下操作会失败，因为email值重复
INSERT INTO users (username, email) VALUES ('user3', 'user1@example.com');
-- ERROR:  duplicate key value violates unique constraint "users_email_key"

检查约束（CHECK）

检查约束的定义

检查约束确保列中的值满足指定的条件，它可以用于限制列值的范围、格式或其他业务规则。

检查约束的创建

表创建时创建检查约束

sql

-- 单列检查约束
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    price NUMERIC(10,2) CHECK (price > 0),
    stock INT CHECK (stock >= 0),
    status VARCHAR(20) CHECK (status IN ('active', 'inactive', 'discontinued'))
);

-- 复合检查约束
CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    order_date DATE NOT NULL,
    ship_date DATE,
    CHECK (ship_date >= order_date OR ship_date IS NULL)
);

表创建后添加检查约束

sql

-- 为现有表添加检查约束
ALTER TABLE users
ADD CONSTRAINT chk_users_age CHECK (age >= 18 AND age <= 120);

-- 为现有表添加复合检查约束
ALTER TABLE orders
ADD CONSTRAINT chk_orders_dates CHECK (ship_date >= order_date OR ship_date IS NULL);

使用表达式创建复杂检查约束

sql

-- 使用函数的检查约束
ALTER TABLE users
ADD CONSTRAINT chk_users_email_format CHECK (email ~* '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Z|a-z]{2,}$');

-- 使用CASE表达式的检查约束
ALTER TABLE products
ADD CONSTRAINT chk_products_discount CHECK (
    CASE 
        WHEN status = 'active' THEN discount <= 50
        ELSE discount IS NULL
    END
);

检查约束的使用场景

数值范围限制
- 价格必须大于0
- 库存必须大于等于0
- 年龄必须在18-120之间
状态值限制
- 订单状态只能是指定的几个值
- 用户状态只能是active或inactive
日期时间关系
- 发货日期必须大于等于订单日期
- 结束日期必须大于开始日期
格式验证
- 邮箱格式必须符合规范
- 电话号码格式必须符合规范
- 身份证号码格式必须符合规范
业务规则验证
- 折扣不能超过50%
- VIP用户才能享受某些优惠
- 特定条件下某些字段必须填写

检查约束的性能影响

写操作性能
- 插入和更新操作需要执行检查约束条件
- 复杂表达式会增加写操作的延迟
- 高并发场景下可能影响性能
读操作性能
- 检查约束不直接影响读操作性能
- 但复杂的检查约束可能影响查询优化器的决策
存储开销
- 检查约束本身不占用存储空间
- 但约束条件的计算需要CPU资源

检查约束与触发器

特性	检查约束	触发器
执行时机	行级，插入/更新前	可配置为行级或语句级，插入/更新前/后
性能	通常比触发器快	比检查约束慢
复杂度	支持简单到中等复杂度的条件	支持复杂逻辑，可调用函数
可移植性	标准SQL，可移植	数据库特定，不可移植
错误处理	直接返回错误	可自定义错误消息
适用场景	简单的数据验证	复杂的业务规则，跨表验证

约束管理

查看约束

sql

-- 查看表的所有约束
SELECT conname, contype, condef
FROM pg_constraint
WHERE conrelid = 'users'::regclass;

-- 查看表的唯一约束
SELECT conname, condef
FROM pg_constraint
WHERE conrelid = 'users'::regclass AND contype = 'u';

-- 查看表的检查约束
SELECT conname, condef
FROM pg_constraint
WHERE conrelid = 'users'::regclass AND contype = 'c';

-- 使用information_schema查看约束
SELECT constraint_name, constraint_type, column_name
FROM information_schema.constraint_column_usage
WHERE table_name = 'users';

修改约束

修改约束名称

sql

-- 修改约束名称
ALTER TABLE users
RENAME CONSTRAINT uq_users_email TO uq_users_email_address;

禁用/启用约束
sql
```
-- 禁用约束
ALTER TABLE users
DISABLE TRIGGER ALL;

-- 启用约束
ALTER TABLE users
ENABLE TRIGGER ALL;
```
注意：PostgreSQL不支持直接禁用/启用单个检查约束或唯一约束，但可以通过禁用/启用所有触发器来间接实现。

更新约束定义

sql

-- 更新约束需要先删除再添加
ALTER TABLE users
DROP CONSTRAINT chk_users_age;

ALTER TABLE users
ADD CONSTRAINT chk_users_age CHECK (age >= 16 AND age <= 120);

删除约束

sql

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

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

-- 删除约束（如果存在）
ALTER TABLE users
DROP CONSTRAINT IF EXISTS uq_users_phone;

约束验证

sql

-- 验证表中的数据是否满足约束
ALTER TABLE users
VALIDATE CONSTRAINT chk_users_age;

-- 验证所有约束
ALTER TABLE users
VALIDATE CONSTRAINT ALL;

最佳实践

唯一约束最佳实践

合理选择唯一键
- 选择业务上有意义的列作为唯一键
- 避免使用过长的复合唯一键
- 考虑未来业务扩展，避免频繁修改唯一约束
使用命名约束
- 为约束指定有意义的名称
- 遵循统一的命名规范，如：
  - 唯一约束：uq_表名_列名
  - 检查约束：chk_表名_约束描述
考虑性能影响
- 避免在频繁更新的列上创建唯一约束
- 高并发场景下，考虑使用部分唯一索引
- 对于大型表，创建唯一索引时考虑使用CONCURRENTLY选项
处理NULL值
- 明确唯一约束对NULL值的处理方式
- 如需禁止多个NULL值，考虑使用COALESCE函数
sql
```
-- 禁止多个NULL值
CREATE UNIQUE INDEX uq_users_phone ON users(COALESCE(phone, ''));
```

使用部分唯一索引

sql

-- 仅对活跃用户强制执行唯一邮箱
CREATE UNIQUE INDEX uq_users_email_active ON users(email) WHERE status = 'active';

检查约束最佳实践

保持约束简单
- 避免在检查约束中使用复杂表达式
- 避免在检查约束中调用用户定义函数
- 避免跨表检查（使用触发器代替）
考虑性能影响
- 避免在频繁更新的列上创建复杂检查约束
- 对于复杂业务规则，考虑使用触发器或应用层验证
使用标准表达式
- 使用SQL标准表达式，提高可移植性
- 避免使用数据库特定的函数
考虑数据类型
- 确保约束与列的数据类型匹配
- 考虑类型转换的性能影响

验证现有数据

添加检查约束前，确保现有数据满足约束条件
使用VALIDATE CONSTRAINT验证数据

sql

-- 先验证数据，再添加约束
ALTER TABLE users
ADD CONSTRAINT chk_users_age CHECK (age >= 18)
NOT VALID;

-- 验证现有数据
ALTER TABLE users
VALIDATE CONSTRAINT chk_users_age;

约束设计的一般原则

数据完整性优先
- 优先使用约束确保数据完整性
- 不要依赖应用层验证
分层验证
- 数据库层：基础数据完整性
- 应用层：复杂业务规则
- 服务层：跨系统数据一致性
考虑扩展性
- 设计约束时考虑未来业务扩展
- 避免过于严格的约束限制业务发展
监控约束性能
- 监控约束对写操作性能的影响
- 定期审查约束的必要性
文档化约束
- 记录约束的业务规则
- 说明约束的设计意图
- 记录约束的变更历史

不同版本的差异

PostgreSQL 9.x

检查约束不支持在表达式中使用子查询
不支持部分唯一索引
不支持CONCURRENTLY创建唯一索引

PostgreSQL 10.x

支持部分唯一索引
支持CONCURRENTLY创建唯一索引
增强了约束验证功能

PostgreSQL 12.x

支持覆盖索引（Index Only Scan）优化
增强了查询优化器对约束的处理
支持并行创建索引

PostgreSQL 14.x

支持增量刷新物化视图
增强了约束错误消息
支持更多数据类型的约束

常见问题与解决方案

Q1: 唯一约束冲突如何处理？

A1: 处理唯一约束冲突的方法：

使用ON CONFLICT子句

sql

-- 冲突时更新
INSERT INTO users (username, email) 
VALUES ('john', 'john@example.com')
ON CONFLICT (username) 
DO UPDATE SET email = EXCLUDED.email;

-- 冲突时忽略
INSERT INTO users (username, email) 
VALUES ('john', 'john@example.com')
ON CONFLICT (username) 
DO NOTHING;

使用UPSERT操作

sql

-- UPSERT示例
INSERT INTO users (username, email) 
VALUES ('john', 'john@example.com')
ON CONFLICT (username) 
DO UPDATE SET 
    email = EXCLUDED.email,
    updated_at = CURRENT_TIMESTAMP;

预处理检查

sql

-- 插入前检查
IF NOT EXISTS (SELECT 1 FROM users WHERE username = 'john') THEN
    INSERT INTO users (username, email) VALUES ('john', 'john@example.com');
ELSE
    UPDATE users SET email = 'john@example.com' WHERE username = 'john';
END IF;

Q2: 如何处理检查约束验证失败？

A2: 处理检查约束验证失败的方法：

修改数据满足约束
- 调整输入数据，使其满足约束条件
- 检查业务规则，确认约束是否合理
修改约束条件
- 如果约束过于严格，考虑调整约束条件
- 确保约束与业务规则一致

使用条件插入/更新

sql

-- 条件插入
INSERT INTO products (name, price, status)
VALUES ('Product A', 100, 'active')
WHERE 100 > 0;

-- 条件更新
UPDATE products
SET discount = 60
WHERE id = 1 AND status = 'active' AND 60 <= 50;

Q3: 如何优化唯一约束的性能？

A3: 优化唯一约束性能的方法：

使用合适的索引类型
- 对于等值查询，使用B-tree索引
- 对于特定数据类型，使用合适的索引类型
使用部分唯一索引
- 仅对需要的行强制执行唯一约束
- 减少索引大小和维护成本

使用CONCURRENTLY创建索引

sql

-- 并发创建唯一索引，减少锁等待
CREATE UNIQUE INDEX CONCURRENTLY uq_users_username ON users(username);

优化高并发场景
- 使用批量插入减少索引维护开销
- 考虑使用延迟约束检查
- 优化应用逻辑，减少并发冲突

Q4: 如何处理复杂业务规则？

A4: 处理复杂业务规则的方法：

分层验证
- 简单规则：使用检查约束
- 复杂规则：使用触发器
- 跨表规则：使用应用层验证

使用触发器

sql

-- 创建触发器函数
CREATE OR REPLACE FUNCTION check_order_business_rule()
RETURNS TRIGGER AS $$
BEGIN
    -- 复杂业务规则
    IF NEW.status = 'shipped' AND NEW.payment_status != 'paid' THEN
        RAISE EXCEPTION 'Cannot ship an unpaid order';
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- 创建触发器
CREATE TRIGGER trg_check_order_business_rule
BEFORE INSERT OR UPDATE ON orders
FOR EACH ROW
EXECUTE FUNCTION check_order_business_rule();

应用层验证
- 在应用层实现复杂业务规则
- 使用事务确保数据一致性
- 记录验证日志

常见问题（FAQ）

Q1: 唯一约束和主键约束有什么区别？

A1: 唯一约束和主键约束的区别：

特性	唯一约束	主键约束
允许NULL值	是	否
一个表中可以有多个	是	否
自动创建唯一索引	是	是
可以作为外键引用	是	是
主要用途	确保列值唯一	唯一标识表中的行

Q2: 检查约束和触发器有什么区别？

A2: 检查约束和触发器的区别：

特性	检查约束	触发器
执行时机	仅在插入/更新时	可以在插入/更新/删除前/后执行
复杂度	简单到中等	可以非常复杂
跨表操作	不支持	支持
性能	较高	较低
可移植性	高	低（数据库特定）
错误处理	简单	可以自定义错误消息

Q3: 如何禁用唯一约束？

A3: PostgreSQL不支持直接禁用单个唯一约束，但可以通过以下方法间接实现：

删除唯一约束，需要时重新创建
使用部分唯一索引，通过调整WHERE条件实现
禁用所有触发器（会影响所有约束）
使用临时表或视图绕过约束

Q4: 检查约束中可以使用用户定义函数吗？

A4: 可以在检查约束中使用用户定义函数，但需要注意：

函数必须是IMMUTABLE或STABLE的
函数不能有副作用
函数的性能会影响约束检查的性能
函数的变更可能导致约束失效

Q5: 如何处理约束冲突的错误消息？

A5: 处理约束冲突错误消息的方法：

使用TRY...CATCH（在PL/pgSQL中）

sql

BEGIN
    INSERT INTO users (username) VALUES ('john');
EXCEPTION WHEN unique_violation THEN
    RAISE NOTICE 'Username already exists';
END;

在应用层捕获异常
- 使用ORM框架的异常处理机制
- 解析错误消息，返回友好提示
- 记录详细错误日志
使用ON CONFLICT子句
- 避免抛出异常
- 优雅处理冲突情况
- 提高应用程序的健壮性

Q6: 约束会影响备份和恢复吗？

A6: 约束会影响备份和恢复：

备份会包含约束定义
恢复时会重新创建约束
恢复过程中会检查约束
可以使用DISABLE TRIGGER ALL加速恢复

Q7: 如何迁移带有约束的表？

A7: 迁移带有约束的表的方法：

使用pg_dump/pg_restore
- 完整备份和恢复
- 自动处理约束
分步迁移
- 先创建表结构，禁用约束
- 导入数据
- 启用约束并验证
使用COPY命令
- 快速导入数据
- 需要手动处理约束

Q8: 如何测试约束的性能影响？

A8: 测试约束性能影响的方法：

使用EXPLAIN ANALYZE
- 分析插入/更新操作的执行计划
- 比较有无约束时的执行时间
基准测试
- 使用pgbench进行基准测试
- 比较有无约束时的吞吐量
- 测试高并发场景下的性能
监控系统指标
- 监控CPU使用率
- 监控磁盘I/O
- 监控锁等待情况
生产环境监控
- 监控写操作延迟
- 监控约束冲突率
- 监控索引维护开销

PostgreSQL 唯一约束与检查约束 ​

约束的定义与作用 ​

约束的类型 ​

约束的优缺点 ​

唯一约束（UNIQUE） ​

唯一约束的定义 ​

唯一约束的创建 ​

唯一约束的使用场景 ​

唯一约束的性能影响 ​

唯一约束与NULL值 ​

检查约束（CHECK） ​

检查约束的定义 ​

检查约束的创建 ​

检查约束的使用场景 ​

检查约束的性能影响 ​

检查约束与触发器 ​

约束管理 ​

查看约束 ​

修改约束 ​

删除约束 ​

约束验证 ​

最佳实践 ​

唯一约束最佳实践 ​

检查约束最佳实践 ​

约束设计的一般原则 ​

不同版本的差异 ​

PostgreSQL 9.x ​

PostgreSQL 10.x ​

PostgreSQL 12.x ​

PostgreSQL 14.x ​

常见问题与解决方案 ​

Q1: 唯一约束冲突如何处理？ ​

Q2: 如何处理检查约束验证失败？ ​

Q3: 如何优化唯一约束的性能？ ​

Q4: 如何处理复杂业务规则？ ​

常见问题（FAQ） ​

Q1: 唯一约束和主键约束有什么区别？ ​

Q2: 检查约束和触发器有什么区别？ ​

Q3: 如何禁用唯一约束？ ​

Q4: 检查约束中可以使用用户定义函数吗？ ​

Q5: 如何处理约束冲突的错误消息？ ​

Q6: 约束会影响备份和恢复吗？ ​

Q7: 如何迁移带有约束的表？ ​

Q8: 如何测试约束的性能影响？ ​