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 查看

约束的设计原则

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