详细介绍MySQL 约束与进阶

在 MySQL 中，约束（Constraint）是用于限制表中数据的规则，确保数据的完整性、一致性和准确性。通过约束，可以防止无效数据进入表中，保证数据库中数据的可靠性。

以下是 MySQL 中常用的约束类型及用法：

1. 主键约束（PRIMARY KEY）

• 作用：唯一标识表中的每条记录，确保每行数据的唯一性，且不允许为 NULL。
• 特点：一个表只能有一个主键，可由单个字段或多个字段组合（复合主键）构成。
• 示例：

  -- 单字段主键
  CREATE TABLE users (
      id INT PRIMARY KEY,  -- id 作为主键
      name VARCHAR(50) NOT NULL
  );
  
  -- 复合主键（多字段组合唯一）
  CREATE TABLE student_course (
      student_id INT,
      course_id INT,
      score INT,
      PRIMARY KEY (student_id, course_id)  -- 学生ID+课程ID组合唯一
  );
  

2. 非空约束（NOT NULL）

• 作用：限制字段的值不能为 NULL，必须填写具体数据。
• 示例：

  CREATE TABLE users (
      id INT PRIMARY KEY,
      name VARCHAR(50) NOT NULL,  -- 姓名不能为空
      email VARCHAR(100) NOT NULL  -- 邮箱不能为空
  );
  

3. 唯一约束（UNIQUE）

• 作用：确保字段的值在表中是唯一的（允许为 NULL，但 NULL 只允许出现一次）。
• 与主键的区别：主键不能为 NULL 且一个表只能有一个，唯一约束可多个且允许 NULL。
• 示例：

  CREATE TABLE users (
      id INT PRIMARY KEY,
      name VARCHAR(50) NOT NULL,
      email VARCHAR(100) UNIQUE,  -- 邮箱必须唯一（防止重复注册）
      phone VARCHAR(20) UNIQUE    -- 手机号必须唯一
  );
  

4. 外键约束（FOREIGN KEY）

• 作用：建立两个表之间的关联关系，确保从表（子表）的字段值必须在主表（父表）的对应字段中存在，维护数据的参照完整性。
• 术语：
  • 主表：被引用的表（如 users 表）。
  • 从表：引用主表的表（如 orders 表）。
  • 外键：从表中用于关联主表的字段（如 orders.user_id）。
• 示例：

  -- 主表：用户表
  CREATE TABLE users (
      id INT PRIMARY KEY,
      name VARCHAR(50) NOT NULL
  );
  
  -- 从表：订单表（通过 user_id 关联用户表）
  CREATE TABLE orders (
      id INT PRIMARY KEY,
      order_no VARCHAR(20) UNIQUE NOT NULL,
      user_id INT,
      -- 外键约束：user_id 必须在 users.id 中存在
      FOREIGN KEY (user_id) REFERENCES users(id)
          ON DELETE CASCADE  -- 主表记录删除时，从表关联记录也删除
          ON UPDATE CASCADE  -- 主表主键更新时，从表关联字段也更新
  );
  

  • ON DELETE CASCADE：主表记录删除时，从表关联记录自动删除（避免孤儿数据）。
  • ON UPDATE CASCADE：主表主键更新时，从表关联字段自动更新。
  • 其他常用选项：ON DELETE SET NULL（主表记录删除，从表外键设为 NULL）、ON DELETE RESTRICT（禁止删除主表有关联的记录）。

5. 默认值约束（DEFAULT）

• 作用：为字段设置默认值，当插入数据时未指定该字段值，自动使用默认值。
• 示例：

  CREATE TABLE products (
      id INT PRIMARY KEY,
      name VARCHAR(100) NOT NULL,
      price DECIMAL(10,2) NOT NULL,
      stock INT DEFAULT 0,  -- 库存默认值为 0
      create_time DATETIME DEFAULT CURRENT_TIMESTAMP  -- 默认值为当前时间
  );
  

6. 检查约束（CHECK）

• 作用：限制字段值必须满足指定的条件（MySQL 8.0 及以上版本支持）。
• 示例：

  CREATE TABLE users (
      id INT PRIMARY KEY,
      name VARCHAR(50) NOT NULL,
      age INT CHECK (age >= 0 AND age <= 150),  -- 年龄必须在 0-150 之间
      salary DECIMAL(10,2) CHECK (salary >= 0)  -- 工资不能为负数
  );
  

7. 自增约束（AUTO_INCREMENT）

• 作用：通常与主键配合使用，使字段值自动递增（每次插入新记录时自动+1），简化主键赋值。
• 特点：一个表只能有一个自增字段，且该字段必须是主键或唯一索引。
• 示例：

  CREATE TABLE users (
      id INT PRIMARY KEY AUTO_INCREMENT,  -- id 自动递增（1,2,3...）
      name VARCHAR(50) NOT NULL
  );
  
  -- 插入数据时无需指定 id，会自动生成
  INSERT INTO users (name) VALUES ('张三'), ('李四');
  

8、约束的添加

除了创建表时定义约束，还可以通过 ALTER TABLE 语句添加或删除约束：

1）添加约束

-- 为已存在的表添加主键
ALTER TABLE users ADD PRIMARY KEY (id);

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

-- 添加外键约束
ALTER TABLE orders ADD FOREIGN KEY (user_id) REFERENCES users(id);

9、删除约束

在 MySQL 中删除约束需要根据不同的约束类型使用特定的语法，因为每种约束的删除方式略有不同。以下是删除各种常见约束的方法：

1）删除主键约束（PRIMARY KEY）

主键约束约束的删除需要先确保该字段没有自增属性（AUTO_INCREMENT），否则需要先移除自增属性。

步骤：

1. （若有自增）移除字段的 AUTO_INCREMENT 属性
2. 删除主键约束

示例：

-- 假设表名为 users，主键为 id 且有自增属性
-- 1. 先移除自增属性
ALTER TABLE users MODIFY id INT;

-- 2. 删除主键约束
ALTER TABLE users DROP PRIMARY KEY;

2） 删除唯一约束（UNIQUE）

唯一约束删除时需要指定约束对应的索引名（唯一约束会自动创建同名索引）。若不知道索引名，可先查询。

步骤：

1. （可选）查询表中的唯一索引名：SHOW INDEX FROM 表名;
2. 通过索引名删除唯一约束

示例：

-- 假设表 users 有一个唯一约束在 email 字段上
-- 1. 查看索引名（通常与字段名相同）
SHOW INDEX FROM users;

-- 2. 删除唯一约束（假设索引名为 email）
ALTER TABLE users DROP INDEX email;

3）删除外键约束（FOREIGN KEY）

外键约束删除时需要指定外键的约束名（而非字段名）。约束名可通过 SHOW CREATE TABLE 命令查询。

步骤：

1. 查询外键约束名：SHOW CREATE TABLE 表名;
2. 通过约束名删除外键

示例：

-- 假设从表 orders 有外键关联主表 users
-- 1. 查看外键约束名（如 orders_ibfk_1）
SHOW CREATE TABLE orders;

-- 2. 删除外键约束（使用查询到的约束名）
ALTER TABLE orders DROP FOREIGN KEY orders_ibfk_1;

4）删除非空约束（NOT NULL）

非空约束的删除本质是修改字段允许为 NULL，通过 ALTER TABLE ... MODIFY 实现。

示例：

-- 允许 users 表的 name 字段为 NULL（即删除非空约束）
ALTER TABLE users MODIFY name VARCHAR(50) NULL;

5）删除默认值约束（DEFAULT）

默认值约束的删除是将字段的默认值设为 NULL（即不指定默认值）。

示例：

-- 移除 products 表 stock 字段的默认值（原默认值为 0）
ALTER TABLE products MODIFY stock INT;  -- 不指定 DEFAULT 即删除默认值

6）删除检查约束（CHECK）

检查约束需要通过约束名删除，可先通过 SHOW CREATE TABLE 查看约束名。

示例：

-- 假设 users 表有一个检查年龄的约束（约束名为 age_check）
-- 1. 查看检查约束名
SHOW CREATE TABLE users;

-- 2. 删除检查约束
ALTER TABLE users DROP CHECK age_check;

删除约束的核心是：

• 主键、外键、检查约束需要知道约束名（可通过 SHOW CREATE TABLE 表名 查询）
• 唯一约束需要知道索引名（可通过 SHOW INDEX FROM 表名 查询）
• 非空和默认值约束通过 ALTER TABLE ... MODIFY 重新定义字段属性来删除

操作前建议先备份数据，避免误删重要约束导致数据完整性问题。

10、总结

约束是 MySQL 保证数据质量的核心机制，合理使用约束可以：

• 防止重复数据（主键、唯一约束）。
• 确保数据不为空（非空约束）。
• 维护表之间的关联关系（外键约束）。
• 限制数据范围（检查约束）。
• 简化数据插入（默认值、自增约束）。

在设计表结构时，应根据业务需求选择合适的约束，平衡数据完整性和操作灵活性。