MySQL约束

MySQL 约束详解：保障数据完整性的六大机制

约束（Constraint）是 MySQL 中用于限制表中数据的规则，通过强制数据满足特定条件，确保数据库的完整性、一致性和准确性。MySQL 支持六大类约束，虽然部分约束在实现上有局限，但仍是设计可靠数据表的核心工具。

六大约束类型及作用

约束类型	作用描述	MySQL 支持情况
NOT NULL	限制字段值不能为 NULL（必须填写）。	完全支持
DEFAULT	为字段设置默认值，当插入数据时未指定该字段则使用默认值。	完全支持
PRIMARY KEY	主键约束，唯一标识表中的每行记录，要求字段值唯一且非空。	完全支持（单字段或复合主键）
UNIQUE	唯一约束，保证字段值在表中唯一，但允许为 NULL（NULL 可重复）。	完全支持
CHECK	检查约束，限制字段值必须满足指定条件（如 age > 0）。	MySQL 8.0.16+ 支持，低版本仅语法兼容但不生效
FOREIGN KEY	外键约束，限制两个表的关系，确保从表字段值必须来自主表的关联字段。	支持，但需存储引擎为 InnoDB（MyISAM 不支持）

约束的使用方法

1. NOT NULL（非空约束）

确保字段必须有值，禁止插入 NULL。

CREATE TABLE student (
  id INT,
  name VARCHAR(50) NOT NULL,  -- 姓名不能为空
  age INT NOT NULL DEFAULT 0  -- 年龄不能为空，默认值为 0
);

-- 插入数据时未指定 name 会报错
INSERT INTO student (id) VALUES (1);  -- 错误：Column 'name' cannot be null

2. DEFAULT（默认约束）

为字段设置默认值，简化插入操作（未指定字段时自动填充）。

CREATE TABLE employee (
  id INT,
  name VARCHAR(50) NOT NULL,
  department VARCHAR(30) DEFAULT '未知部门',  -- 默认部门
  hire_date DATE DEFAULT CURRENT_DATE()      -- 默认值为当前日期
);

-- 插入时未指定 department 和 hire_date，将使用默认值
INSERT INTO employee (id, name) VALUES (1, '张三');
-- 结果：department 为 '未知部门'，hire_date 为插入当天日期

3. PRIMARY KEY（主键约束）

主键是表的 “唯一标识符”，具有以下特性：

• 一个表只能有一个主键（可由多个字段组成复合主键）。
• 主键字段值必须唯一且非空。
• 主键会自动创建索引，提升查询效率。

示例 1：单字段主键

CREATE TABLE user (
  id INT PRIMARY KEY,  -- 主键约束（id 唯一且非空）
  username VARCHAR(50) NOT NULL
);

示例 2：复合主键（多字段联合唯一）

CREATE TABLE student_course (
  student_id INT,
  course_id INT,
  score INT,
  -- 复合主键：学生ID+课程ID唯一标识一条选课记录
  PRIMARY KEY (student_id, course_id)
);

4. UNIQUE（唯一约束）

确保字段值唯一，但允许 NULL（多个 NULL 不冲突）。

CREATE TABLE teacher (
  id INT PRIMARY KEY,
  email VARCHAR(100) UNIQUE,  -- 邮箱唯一（不允许重复）
  phone VARCHAR(20) UNIQUE    -- 手机号唯一
);

-- 正确：email 唯一
INSERT INTO teacher (id, email) VALUES (1, 'a@school.com');

-- 错误：email 重复
INSERT INTO teacher (id, email) VALUES (2, 'a@school.com');  -- 错误：Duplicate entry

-- 允许：phone 为 NULL 可重复
INSERT INTO teacher (id, email, phone) VALUES (3, 'b@school.com', NULL);
INSERT INTO teacher (id, email, phone) VALUES (4, 'c@school.com', NULL);  -- 成功

5. CHECK（检查约束）

限制字段值必须满足指定条件（如范围、格式等）。注意：MySQL 8.0.16 之前仅支持语法，不实际生效。

CREATE TABLE product (
  id INT PRIMARY KEY,
  name VARCHAR(50) NOT NULL,
  price DECIMAL(10,2) CHECK (price > 0),  -- 价格必须大于 0
  stock INT CHECK (stock >= 0)            -- 库存不能为负数
);

-- 错误：price 不满足 CHECK 条件
INSERT INTO product (id, name, price) VALUES (1, '手机', -1000);  -- 错误

6. FOREIGN KEY（外键约束）

用于关联两个表（主表和从表），确保从表的字段值必须在主表的关联字段中存在，维护表间数据一致性。

示例：学生表（从表）关联班级表（主表）

-- 主表：班级表
CREATE TABLE class (
  class_id INT PRIMARY KEY,
  class_name VARCHAR(50) NOT NULL
);

-- 从表：学生表（添加外键关联班级表）
CREATE TABLE student (
  student_id INT PRIMARY KEY,
  name VARCHAR(50) NOT NULL,
  class_id INT,
  -- 外键约束：student.class_id 必须来自 class.class_id
  FOREIGN KEY (class_id) REFERENCES class(class_id)
    ON DELETE CASCADE  -- 主表记录删除时，从表关联记录也删除
    ON UPDATE CASCADE  -- 主表主键更新时，从表关联字段也更新
);

外键约束的关联动作（可选）：

• ON DELETE CASCADE：主表记录删除，从表关联记录自动删除。
• ON DELETE SET NULL：主表记录删除，从表关联字段设为 NULL（需从表字段允许 NULL）。
• ON UPDATE CASCADE：主表主键更新，从表关联字段自动更新。

约束的添加与删除

1. 新建表时添加约束

如上述示例，在 CREATE TABLE 语句中直接定义约束。

2. 已有表添加约束

-- 添加非空约束
ALTER TABLE student MODIFY name VARCHAR(50) NOT NULL;

-- 添加唯一约束
ALTER TABLE teacher ADD UNIQUE (email);

-- 添加主键约束
ALTER TABLE user ADD PRIMARY KEY (id);

-- 添加外键约束
ALTER TABLE student 
ADD FOREIGN KEY (class_id) 
REFERENCES class(class_id);

3. 删除约束

-- 删除非空约束（需指定允许 NULL）
ALTER TABLE student MODIFY name VARCHAR(50) NULL;

-- 删除唯一约束（需知道约束名，可通过 SHOW CREATE TABLE 查看）
ALTER TABLE teacher DROP INDEX email;

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

-- 删除外键约束（需知道约束名）
ALTER TABLE student DROP FOREIGN KEY student_ibfk_1;  -- 约束名通过 SHOW CREATE TABLE 查看

约束的设计原则

1. 最小必要原则：只添加必需的约束，避免过度约束导致插入 / 更新困难。
2. 主键必设原则：几乎所有表都应设置主键（便于唯一标识记录和关联其他表）。
3. 外键慎用原则：外键会降低写入性能，且可能导致表结构耦合。实际项目中，常通过业务逻辑维护表间关系，而非依赖外键约束。
4. CHECK 替代方案：对于 MySQL 低版本，可用触发器（Trigger）或应用层验证替代 CHECK 约束。