MySQL

MySQL 外键介绍

外键概念以及使用方法

外键介绍

以学生为例，使用数据库 student：

1
2

CREATE DATABASE IF NOT EXISTS `student` CHARACTER SET utf8mb4;
USE `student`;

现在 student 数据库中已经有了一张表 info ，用于统计学生信息，表结构如下：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13

DROP TABLE IF EXISTS `info`;
CREATE TABLE `info`
(
    id          BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    number      INTEGER UNSIGNED NOT NULL UNIQUE COMMENT 'student id',
    name        VARCHAR(255)     NOT NULL,
    gender      BOOLEAN          NOT NULL COMMENT 'true MAN && false WOMAN',
    birth       DATE             NOT NULL,
    description VARCHAR(255),
    create_time DATETIME         NOT NULL DEFAULT CURRENT_TIMESTAMP,
    update_time DATETIME         NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE = InnoDB
  CHARSET = utf8mb4;

此时，需要登记当前学期（第一学期）每位同学的语数英成绩，可以创建下表 first_term_score：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

DROP TABLE IF EXISTS `first_term_score`;
CREATE TABLE `first_term_score`
(
    id          INTEGER UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    number      INTEGER UNSIGNED NOT NULL UNIQUE COMMENT 'student id',
    chinese     INTEGER UNSIGNED NOT NULL,
    math        INTEGER UNSIGNED NOT NULL,
    english     INTEGER UNSIGNED NOT NULL,
    create_time DATETIME         NOT NULL DEFAULT CURRENT_TIMESTAMP,
    update_time DATETIME         NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE = InnoDB
  CHARSET = utf8mb4;

看上去表 first_term_score 没多大问题，但是这张表与上表 info 息息相关：first_term_score 表中的 number 字段依赖于 info 表中的 number 字段：即学号（学生）可以没有成绩，但成绩不能没有学号。

实际上，在数据库中这个概念称为 外键(foreign key)，即 表 info 中 number 字段是表 first_term_score 的外键。info 称为主表，first_term_score 称为外表；表 info 中的 number 列称为参照列，表 first_term_score 中的 number 列称为外键列。外键有以下特性：

  • 参照列在主表中具有唯一性 (PRIMARY KEY 或者 UNIQUE)
  • 虽然主表中的参照列和从表中的外键列都可以为空，但强烈建议主表中参照列设置为 NO NULL
  • 主表参照列和从表外键列必须具有相同的数据类型，长度和精度
  • 在删除主表中一条数据时，必须确保这条数据的外键值没有在从表中被使用（此为默认设置：ON DELETE NO ACTION 或者 ON DELETE RESTRICT）
  • 当使用 ON DELETE CASCADE 时，删除主表中的一条数据会自动删除从表中关联该数据外键值的数据（请谨慎使用）
  • 当使用 ON DELETE SET NULL 时，删除主表中的一条数据会设置从表中外键相关联的外键值为 NULL（请谨慎使用）
  • 当使用 ON UPDATE CASCADE 时，更新主表的外键值会自动更新从表中使用此外键的外键值
  • 外键列在从表中可以重复（即主表外键关联多条从表数据）
  • 主从表可以建立双向外键关系，以此来确保数据在不同表之间的一致性和完整性
  • 主表的参照列可以关联多张从表的外键列
  • 表既可以有参照列，也可以有外键列
  • 主表可以有多个参照列，从表可以有多个外键列
  • 可以设置外键的外键，即从表中的外键列可以设置为另一张表（从表的从表）的外键。这被称为嵌套（nested）外键关系，或者也可以称之为级联（cascading）外键关系
  • 外键列既可以是单列，也可以是多列联合，即联合唯一索引
  • 如果表太多，外键关系太复杂，或许可以思考下换种方式来实现

外键使用

可以使用下面的命令为从表 first_term_score 添加外键约束：

  • 设置主表参照列为主键

    1

    ALTER TABLE `info` ADD PRIMARY KEY number (number);

    或者设置为唯一索引

    1

    ALTER TABLE `info` ADD UNIQUE number (number);

    或者变更字段属性（添加非空约束）

    1
2
3

    ALTER TABLE `info`
CHANGE COLUMN number number
INTEGER UNSIGNED NOT NULL UNIQUE;

  • 为从表外键列建立索引

    1

    ALTER TABLE `first_term_score` ADD INDEX number (number);

    或者建立唯一索引

    1

    ALTER TABLE `first_term_score` ADD INDEX number (number);

  • 设置从表外键约束

    1
2
3
4

    ALTER TABLE `first_term_score`
ADD CONSTRAINT number
FOREIGN KEY (number)
REFERENCES `info` (number) ON DELETE CASCADE ON UPDATE CASCADE;
comments powered by Disqus