唯一约束与检查约束设计

一、约束概述

数据库约束是确保数据完整性和一致性的重要机制。除了主键约束和外键约束外，唯一约束和检查约束也是常用的约束类型。唯一约束确保列或列组合的值唯一，检查约束确保列的值满足特定条件。

二、唯一约束（UNIQUE Constraint）

2.1 定义

唯一约束确保表中某一列或多列的组合值在整个表中是唯一的。与主键约束不同，唯一约束允许NULL值（但只能有一个NULL）。

2.2 创建唯一约束

单列唯一约束

CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    email VARCHAR(100) UNIQUE,
    username VARCHAR(50)
);

多列唯一约束（复合唯一约束）

CREATE TABLE orders (
    id INT PRIMARY KEY AUTO_INCREMENT,
    order_no VARCHAR(50),
    user_id INT,
    UNIQUE KEY uk_order_user (order_no, user_id)
);

命名唯一约束

CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    email VARCHAR(100),
    CONSTRAINT uk_users_email UNIQUE (email)
);

2.3 唯一约束与主键约束的区别

2.4 唯一约束应用场景

• 邮箱唯一：确保用户邮箱不重复
• 手机号唯一：确保用户手机号不重复
• 订单号唯一：确保订单号不重复
• 复合唯一：确保组合条件唯一，如用户+角色

三、检查约束（CHECK Constraint）

3.1 定义

检查约束确保列的值满足特定的条件。检查约束可以使用任何返回布尔值的表达式。

3.2 创建检查约束

基础检查约束

CREATE TABLE products (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    price DECIMAL(10, 2) CHECK (price > 0),
    stock INT CHECK (stock >= 0)
);

命名检查约束

CREATE TABLE orders (
    id INT PRIMARY KEY AUTO_INCREMENT,
    status VARCHAR(20),
    CONSTRAINT chk_order_status CHECK (status IN ('pending', 'paid', 'shipped', 'completed'))
);

多条件检查约束

CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    age INT,
    email VARCHAR(100),
    CONSTRAINT chk_user CHECK (age >= 18 AND email LIKE '%@%.%')
);

3.3 检查约束支持的数据库

3.4 检查约束应用场景

• 范围检查：确保数值在合理范围内
• 枚举检查：确保值在预定义列表中
• 格式检查：确保数据格式正确
• 逻辑检查：确保数据满足业务逻辑

四、约束管理

4.1 添加约束

ALTER TABLE users
ADD CONSTRAINT uk_users_email UNIQUE (email);

ALTER TABLE products
ADD CONSTRAINT chk_price CHECK (price > 0);

4.2 删除约束

ALTER TABLE users
DROP INDEX uk_users_email;

ALTER TABLE products
DROP CONSTRAINT chk_price;

4.3 查看约束

-- MySQL查看约束
SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE 
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS 
WHERE TABLE_NAME = 'users';

-- PostgreSQL查看约束
SELECT conname, contype 
FROM pg_constraint 
WHERE conrelid = 'users'::regclass;

五、约束与性能

5.1 插入性能

插入记录时，数据库需要检查约束条件，会产生额外开销。

5.2 更新性能

更新记录时，如果涉及约束列，需要重新检查约束。

5.3 查询性能

唯一约束会自动创建索引，提高查询性能。检查约束不会创建索引。

5.4 批量操作优化

大批量操作时，可以临时禁用约束：

-- PostgreSQL临时禁用约束
ALTER TABLE users DISABLE TRIGGER ALL;
-- 执行批量操作
ALTER TABLE users ENABLE TRIGGER ALL;

六、约束设计最佳实践

6.1 命名规范

使用统一的命名规范，便于管理和维护：

-- 唯一约束: uk_表名_列名
CONSTRAINT uk_users_email UNIQUE (email)

-- 检查约束: chk_表名_列名
CONSTRAINT chk_products_price CHECK (price > 0)

6.2 约束粒度

约束应尽可能精细，确保数据质量：

-- 好的设计
CHECK (status IN ('pending', 'paid', 'shipped', 'completed'))

-- 不好的设计
CHECK (status IS NOT NULL)

6.3 应用层与数据库层约束

重要的约束应在数据库层和应用层同时实现：

6.4 避免过度约束

过多的约束会降低性能，应在数据完整性和性能之间权衡。

6.5 约束测试

在部署前充分测试约束的有效性和性能影响。

七、约束与业务逻辑

7.1 业务规则实现

检查约束可以实现简单的业务规则：

CREATE TABLE promotions (
    id INT PRIMARY KEY AUTO_INCREMENT,
    start_date DATE,
    end_date DATE,
    discount DECIMAL(5, 2),
    CONSTRAINT chk_dates CHECK (end_date > start_date),
    CONSTRAINT chk_discount CHECK (discount BETWEEN 0 AND 1)
);

7.2 复杂业务规则

对于复杂的业务规则，应使用触发器或存储过程：

CREATE TRIGGER trg_check_order_total
BEFORE INSERT ON orders
FOR EACH ROW
BEGIN
    IF NEW.total_amount <= 0 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '订单金额必须大于0';
    END IF;
END;

八、约束与数据迁移

8.1 数据导入

导入历史数据时，可能需要临时禁用约束：

SET FOREIGN_KEY_CHECKS = 0;
-- 导入数据
SET FOREIGN_KEY_CHECKS = 1;

8.2 数据修复

修复不符合约束的数据后，重新启用约束。

九、约束与数据库设计模式

9.1 防御性编程

使用约束进行防御性编程，防止非法数据进入数据库。

9.2 契约式设计

约束定义了数据的契约，所有操作必须遵守这些契约。

9.3 约束层次

十、总结

唯一约束和检查约束是维护数据完整性的重要工具，能够在数据库层面确保数据的质量和一致性。合理使用这些约束能够提高数据可靠性，减少应用层的负担。在实际应用中，应根据业务需求和性能要求设计合适的约束方案。