约束

MySQL 约束详解

约束（Constraint）用于限制表中数据的规则，确保数据的完整性和一致性。MySQL 支持以下几种常用约束。

一、PRIMARY KEY（主键）

定义：唯一标识表中每一行记录的列或列组合。主键值必须唯一且非空。一张表只能有一个主键。

语法：

-- 列级定义
CREATE TABLE table_name (
    id INT PRIMARY KEY,
    name VARCHAR(50)
);

-- 表级定义（适用于复合主键）
CREATE TABLE table_name (
    id INT,
    name VARCHAR(50),
    PRIMARY KEY (id, name)
);

使用场景：

• 每个表都应有一个主键（业务主键或逻辑主键，如自增 ID）。
• 用于快速定位一行数据，也是其他表外键引用的目标。

特性：

• 自动创建唯一索引。
• 复合主键中所有列的组合必须唯一，且每列都不能为 NULL。
• 主键列通常使用 AUTO_INCREMENT 自动生成值。

二、FOREIGN KEY（外键）

定义：用于建立表和表之间的关联。外键列的值必须引用另一张表的主键或唯一键，或为 NULL。

语法：

CREATE TABLE child_table (
    child_id INT PRIMARY KEY,
    parent_id INT,
    FOREIGN KEY (parent_id) REFERENCES parent_table(parent_id)
    -- 可指定 ON DELETE 和 ON UPDATE 行为
    -- ON DELETE CASCADE | SET NULL | RESTRICT | NO ACTION
);

使用场景：

• 维护数据参照完整性，例如订单表的 user_id 必须指向已存在的用户。
• 防止孤立记录，确保关联数据一致性。

注意事项：

• 关联的两列数据类型必须一致。
• 父表的被引用列必须是主键或具有 UNIQUE 约束。
• 外键会带来一定的性能开销（插入/更新/删除时检查），在大数据量或高并发场景下，有时会在应用层保证一致性，而避免使用数据库外键。
• MySQL 中只有 InnoDB 存储引擎支持外键。

级联操作选项：

• ON DELETE CASCADE：父表删除时，子表相关行自动删除。
• ON DELETE SET NULL：父表删除时，子表外键列设为 NULL。
• ON DELETE RESTRICT / NO ACTION：拒绝删除父表中的被引用行（默认行为）。

三、UNIQUE（唯一约束）

定义：保证列（或列组合）中的所有值各不相同。一张表可以有多个唯一约束。

语法：

-- 单列唯一
CREATE TABLE users (
    id INT PRIMARY KEY,
    email VARCHAR(100) UNIQUE
);

-- 复合唯一（组合值唯一）
CREATE TABLE course_selection (
    student_id INT,
    course_id INT,
    UNIQUE KEY (student_id, course_id)
);

使用场景：

• 确保字段不重复，如身份证号、邮箱、用户名。
• 复合唯一用于防止重复组合（如防止同一学生重复选同一门课）。

特性：

• 自动创建唯一索引。
• 可以为 NULL（且可以有多个 NULL，因为 NULL 不等于任何值，但具体行为取决于数据库实现；MySQL 中唯一约束允许有多个 NULL）。

四、NOT NULL（非空约束）

定义：强制列不能存储 NULL 值。

语法：

CREATE TABLE products (
    id INT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    price DECIMAL(10,2)
);

使用场景：

• 对必须有值的字段设置，例如商品名称、用户密码。
• 有助于数据库设计和查询优化（索引 NOT NULL 列通常更高效）。

注意：

• NULL 表示“未知”或“缺失”，不等于空字符串或 0。
• 添加 NOT NULL 约束前，需确保现有数据没有 NULL。

五、DEFAULT（默认值约束）

定义：为列指定默认值。如果插入数据时未提供该列的值，则自动使用默认值。

语法：

CREATE TABLE orders (
    id INT PRIMARY KEY,
    status VARCHAR(20) DEFAULT 'pending',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

使用场景：

• 为常用列提供默认值，简化插入语句。
• 例如：状态默认为 'active'，创建时间默认为当前时间戳。

注意：

• 默认值可以是常量、表达式（如 CURRENT_TIMESTAMP）或函数。
• 如果列定义为 NOT NULL 且没有默认值，插入时必须显式提供值。

六、CHECK（检查约束）

定义：确保列中的值满足指定的条件。

语法：

CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    age INT CHECK (age >= 18),
    salary DECIMAL(10,2) CHECK (salary > 0)
);

使用场景：

• 限制数据范围，如年龄不能为负数、价格必须大于 0。
• 实现简单的业务规则。

MySQL 兼容性：

• MySQL 8.0.16+ 开始完全支持 CHECK 约束，会实际验证数据。
• 在早期版本（5.7及以下）中，CHECK 语法被解析但不生效（被忽略）。建议使用触发器或应用层校验作为替代。

表级 CHECK：

CREATE TABLE user (
    id INT,
    start_date DATE,
    end_date DATE,
    CHECK (end_date > start_date)
);

七、AUTO_INCREMENT（自动递增）

定义：用于整数列，插入数据时自动生成递增的唯一值。通常搭配 PRIMARY KEY 使用。

语法：

CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50)
);

使用场景：

• 生成无业务含义的逻辑主键，简单且高效。
• 适合作为代理键（Surrogate Key）。

特性：

• 初始值默认为 1，每插入一行自动加 1。
• 可通过 ALTER TABLE table_name AUTO_INCREMENT = 100 设置起始值。
• 即使删除行，自增值不会回退（保持递增）。
• 插入时显式指定值或 NULL 或 DEFAULT 均会触发自动生成。

注意事项：

• 只能用于整数类型（TINYINT、SMALLINT、INT、BIGINT）。
• 每个表最多一个 AUTO_INCREMENT 列，且必须被索引（通常是主键）。
• 在分布式系统中，自增可能产生冲突或性能问题，可改用雪花算法等分布式 ID。

八、约束管理（添加/删除）

-- 添加唯一约束
ALTER TABLE table_name ADD UNIQUE (column_name);

-- 删除主键约束
ALTER TABLE table_name DROP PRIMARY KEY;

-- 删除外键约束（需知道外键名）
ALTER TABLE child_table DROP FOREIGN KEY fk_name;

-- 添加 CHECK 约束（MySQL 8.0+）
ALTER TABLE table_name ADD CONSTRAINT chk_name CHECK (条件);

九、总结表

合理使用约束，可以在数据库层面保证数据的正确性和一致性，减少应用层的校验负担。

参考文献