源本科技 | 码上会

logo
源本科技 码上会
一站式数字化转型技能学习平台

MySQL 数据约束

2026/03/14
40
0

为什么需要约束

想象一下，如果公司的员工表中出现了两条完全相同的“张三”，或者某个员工的部门 ID 指向了一个不存在的部门，甚至工资字段是空的，这会给业务带来多大的灾难？

约束就是数据库的“守门员”。它们是施加在表数据上的规则，用于：

  1. 防止无效数据：阻止不符合逻辑的数据进入数据库（如负数的年龄）。

  2. 保证数据一致性：确保关联数据之间的逻辑关系正确（如外键）。

  3. 简化应用开发：将数据校验逻辑下沉到数据库层，减少代码重复。

数据库三大范式

在添加约束之前，良好的表结构设计是基础。范式 是设计关系型数据库表的指导原则，旨在减少数据冗余并提高数据一致性。

第一范式 (1NF)：原子性

  • 定义：表中的每一列都是不可再分的基本数据项。

  • 核心：属性不可分割。

  • 反例：一个 address 字段存储了 "广东省广州市天河区"，如果需要单独查询“城市”，这就违反了 1NF。应拆分为 province, city, district。

第二范式 (2NF)：完全依赖

  • 前提：满足 1NF。

  • 定义：非主键列必须完全依赖于整个主键，消除部分依赖。

  • 核心：每张表只描述一件事情。

  • 场景：主要针对联合主键。

    • 错误示例：订单详情表 (订单ID, 商品ID) 为主键。如果表中包含 商品名称，它只依赖于 商品ID，而不依赖于 订单ID，这就是部分依赖。应将商品信息拆分到独立的商品表中。

第三范式 (3NF)：消除传递依赖

  • 前提：满足 2NF。

  • 定义：非主键列之间不存在传递依赖，必须直接依赖于主键。

  • 核心：列与列之间不能“隔山打牛”。

    • 错误示例：员工表 (员工ID, 姓名, 部门ID, 部门名称)。部门名称 依赖于 部门ID，而 部门ID 依赖于 员工ID。这就是传递依赖。应将部门信息拆分到部门表中。

遵循范式的优点：减少冗余、避免更新异常、插入异常和删除异常。

💡 进阶：反范式

在实际高并发场景（如电商大促、报表分析）中，严格的范式会导致大量的表连接（JOIN），严重影响查询性能。此时我们会采用反范式策略：

  • 空间换时间：故意在表中增加冗余字段（如在订单表中直接存储 商品名称 而不是只存 商品ID）。

  • 合并表：将频繁连接的表合并为大宽表。

  • 权衡：牺牲一定的存储空间和维护成本（更新时需同步多处），换取查询速度的大幅提升。

六大核心约束

MySQL 提供了多种约束机制，强制执行业务规则。

1. 非空约束 (NOT NULL)

  • 作用：确保列值不能为 NULL。

  • 场景：姓名、身份证号、入职日期等必填项。

  • 语法：column_name TYPE NOT NULL

2. 唯一约束 (UNIQUE)

  • 作用：确保列值在表中唯一，不允许重复（允许有多个 NULL，具体视数据库版本而定，MySQL InnoDB 中多个 NULL 是允许的）。

  • 场景：用户名、邮箱、手机号。

  • 语法：column_name TYPE UNIQUE

3. 主键约束 (PRIMARY KEY)

  • 作用：非空 + 唯一 的组合。它是表中每一行数据的唯一标识符。

  • 特点：一张表只能有一个主键。

  • 语法：column_name TYPE PRIMARY KEY

  • 扩展：支持复合主键（多列组合唯一）。

4. 默认约束 (DEFAULT)

  • 作用：当插入数据未指定该列值时，自动填入预设值。

  • 场景：性别（默认为 '男'）、状态（默认为 '正常'）、创建时间。

  • 注意：如果显式插入 NULL，则值为 NULL，不会触发默认值。

  • 语法：column_name TYPE DEFAULT value

5. 自动增长 (AUTO_INCREMENT)

  • 作用：通常与主键配合，每次插入新记录时，该列值自动加 1。

  • 场景：自增 ID。

  • 注意：必须是索引列（通常是主键），且一张表只能有一个自增列。

6. 外键约束 (FOREIGN KEY)

  • 作用：建立两个表之间的链接，保证引用完整性。子表的外键值必须存在于父表的主键中。

  • 场景：员工表中的 dept_id 必须存在于部门表中。

  • 语法：

    CONSTRAINT fk_name FOREIGN KEY (col) REFERENCES parent_table(parent_col)

  • 级联操作（可选）：

    • ON UPDATE CASCADE：父表主键更新，子表自动更新。

    • ON DELETE CASCADE：父表记录删除，子表对应记录自动删除。

综合练习

我们将通过两个阶段来验证约束的威力。

阶段一：单表约束

建表与约束定义

我们创建一个员工表，集成主键、非空、唯一、默认和自增约束。

DROP TABLE IF EXISTS emp;

CREATE TABLE emp (
    id INT PRIMARY KEY AUTO_INCREMENT COMMENT '员工ID，主键自增',
    ename VARCHAR(50) NOT NULL UNIQUE COMMENT '姓名，非空且唯一',
    joindate DATE NOT NULL COMMENT '入职日期，非空',
    salary DOUBLE(7, 2) NOT NULL COMMENT '工资，非空',
    bonus DOUBLE(7, 2) DEFAULT 0 COMMENT '奖金，默认0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

约束行为测试

✅ 测试 A：正常插入

INSERT INTO emp (ename, joindate, salary, bonus) VALUES ('张三', '1999-11-11', 8800.00, 5000.00);
-- 结果：成功。id 自动生成为 1。

❌ 测试 B：违反主键约束 (非空 + 唯一)
尝试插入 id 为 NULL (自增列通常忽略此值，但若强制指定重复值)：

-- 场景 1：指定重复的主键值
INSERT INTO emp (id, ename, joindate, salary) VALUES (1, '李四', '2020-01-01', 9000);
-- 报错：Duplicate entry '1' for key 'PRIMARY' (主键冲突)

❌ 测试 C：违反非空约束

INSERT INTO emp (ename, joindate, salary) VALUES (NULL, '2020-01-01', 9000);
-- 报错：Column 'ename' cannot be null

❌ 测试 D：违反唯一约束

INSERT INTO emp (ename, joindate, salary) VALUES ('张三', '2020-01-01', 9000);
-- 报错：Duplicate entry '张三' for key 'ename' (姓名重复)

✅ 测试 E：默认约束生效
不指定 bonus 字段：

INSERT INTO emp (ename, joindate, salary) VALUES ('王五', '2020-01-01', 9000);
SELECT * FROM emp WHERE ename = '王五';
-- 结果：bonus 字段自动填充为 0.00

注意：若显式插入 NULL，则值为 NULL，不会使用默认值。

阶段二：外键约束

外键是维护多表关系的核心。

创建父表与子表

规则：必须先创建被引用的表（父表），再创建引用表（子表）。

DROP TABLE IF EXISTS emp;
DROP TABLE IF EXISTS dept;

-- 1. 创建部门表 (父表)
CREATE TABLE dept (
    id INT PRIMARY KEY AUTO_INCREMENT,
    dep_name VARCHAR(20) NOT NULL,
    addr VARCHAR(20)
);

-- 2. 创建员工表 (子表)
CREATE TABLE emp (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(20),
    age INT,
    dep_id INT,
    -- 定义外键约束
    CONSTRAINT fk_emp_dept FOREIGN KEY (dep_id) REFERENCES dept(id)
    -- 默认行为：RESTRICT (如果父表有引用，禁止删除/更新)
);

插入数据

-- 先插父表
INSERT INTO dept (dep_name, addr) VALUES ('研发部', '广州'), ('销售部', '深圳');

-- 再插子表
INSERT INTO emp (name, age, dep_id) VALUES 
('张三', 20, 1),  -- 合法：dep_id 1 存在
('李四', 20, 1),
('赵六', 22, 2);  -- 合法：dep_id 2 存在

外键约束验证

❌ 场景 A：插入非法外键
试图将员工分配到一个不存在的部门（如 ID 99）：

INSERT INTO emp (name, age, dep_id) VALUES ('孙七', 18, 99);
-- 报错：Cannot add or update a child row: a foreign key constraint fails
-- 解释：部门表中没有 ID 为 99 的记录，引用失败。

❌ 场景 B：删除被引用的父记录
试图删除“研发部”（ID 1），但该部门下有员工：

DELETE FROM dept WHERE id = 1;
-- 报错：Cannot delete or update a parent row: a foreign key constraint fails
-- 解释：为了保护数据一致性，数据库阻止删除正在被使用的部门。

进阶：级联删除

如果业务需求是：“部门解散了，该部门下的所有员工档案也自动销毁”，我们可以修改外键约束为级联模式。

-- 重建表以演示级联
DROP TABLE IF EXISTS emp;
CREATE TABLE emp (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(20),
    dep_id INT,
    CONSTRAINT fk_emp_dept_cascade 
        FOREIGN KEY (dep_id) REFERENCES dept(id)
        ON DELETE CASCADE  -- 关键：父表删除，子表自动删除
        ON UPDATE CASCADE  -- 关键：父表主键更新，子表自动更新
);

-- 重新插入数据后，再次执行删除：
DELETE FROM dept WHERE id = 1; 
-- 结果：成功！不仅删除了部门 ID 1，emp 表中所有 dep_id=1 的员工记录也被自动删除。

总结

约束类型

关键字

核心作用

适用场景

非空

NOT NULL

拒绝空值

必填项（姓名、密码）

唯一

UNIQUE

拒绝重复

账号、身份证、邮箱

主键

PRIMARY KEY

唯一标识行

每张表必须有一个

默认

DEFAULT

自动填充

状态、时间、性别

自增

AUTO_INCREMENT

自动生成序号

主键 ID

外键

FOREIGN KEY

维护表间关系

关联表（订单 - 用户，员工 - 部门）

最佳实践

  1. 主键必选：每张表都应设计主键，推荐使用无业务意义的自增 ID 或雪花算法 ID。

  2. 外键的取舍：

    • 在传统金融、ERP 系统中，强烈建议使用数据库外键，以确保绝对的数据一致性。

    • 在高并发互联网系统（如淘宝、微信）中，为了性能和分库分表的灵活性，通常不在数据库层设置物理外键，而是通过应用代码（Java/Go/Python）来控制逻辑上的引用关系。

  3. 范式与反范式：设计初期遵循 3NF 减少冗余；在发现性能瓶颈时，针对性地对热点查询进行反范式优化。