MySQL—外键关联操作

作者 | java星星。
来源 | 今日头条

MySQL 的外键约束

注意，MySQL 的 InnoDB 表引擎才支持外键关联，MyISAM 不支持。MySQL 还支持手动打开或关闭外键约束：SET FOREIGN_KEY_CHECKS = 0/1;。

使用外键约束最大的好处在于 MySQL 帮助我们完成数据的一致性检查。当我们使用默认的外键类型 RESTRICT 时，在创建、修改或者删除记录时都会检查引用的合法性。

假设我们的数据库中包含 posts(id, author_id, content) 和 authors(id, name) 两张表，在执行如下所示的操作时都会触发数据库对外键的检查：

向 posts 表中插入数据时，检查 author_id 是否在 authors 表中存在;

修改 posts 表中的数据时，检查 author_id 是否在 authors 表中存在;

删除 authors 表中的数据时，检查 posts 中是否存在引用当前记录的外键;

作为专门用于管理数据的系统，数据库与应用服务相比能够更好地保证完整性，而上述的这些操作都是引入外键带来的额外工作，不过这也是数据库保证数据完整性的必要代价。上述的这些分析都是理论上的定性分析，我们其实可以简单地定量分析一下引入外键对性能的影响。

创建表时定义外键(References，参照)

在 CREATE TABLE 语句中，通过 FOREIGN KEY 关键字来指定外键，具体的语法格式如下：

[CONSTRAINT <外键名>] FOREIGN KEY 字段名 [，字段名2，…] REFERENCES <主表名> 主键列1 [，主键列2，…]

示例：

# 部门表 tb_dept1（主表）
CREATE TABLE tb_dept1
(
	id INT(11) PRIMARY KEY,
	name VARCHAR(22) NOT NULL,
	location VARCHAR(50)
) ENGINE=InnoDB DEFAULT CHARSET=gb2312;

# 员工表 tb_emp6（从表），创建外键约束，让 deptId 作为外键关联到 tb_dept1 的主键 id。
CREATE TABLE tb_emp6
(
	id INT(11) PRIMARY KEY,
	name VARCHAR(25),
	deptId INT(11),
	salary FLOAT,
	CONSTRAINT fk_emp_dept1 FOREIGN KEY(deptId) REFERENCES tb_dept1(id)
) ENGINE=InnoDB DEFAULT CHARSET=gb2312;

NOTE：从表的外键关联的必须是主表的主键，且主键和外键的数据类型必须一致。

以上语句执行成功之后，在表示 tb_emp6 上添加了名称为 fk_emp_dept1 的外键约束，外键名称为 deptId，其依赖于表 tb_dept1 的主键 id。

查看主表的约束信息

MariaDB [test_db]> select * from INFORMATION_SCHEMA.KEY_COLUMN_USAGE where REFERENCED_TABLE_NAME='tb_dept1'\G;
*************************** 1. row ***************************
           CONSTRAINT_CATALOG: def
            CONSTRAINT_SCHEMA: test_db
              CONSTRAINT_NAME: fk_emp_dept1
                TABLE_CATALOG: def
                 TABLE_SCHEMA: test_db
                   TABLE_NAME: tb_emp6
                  COLUMN_NAME: deptId
             ORDINAL_POSITION: 1
POSITION_IN_UNIQUE_CONSTRAINT: 1
      REFERENCED_TABLE_SCHEMA: test_db
        REFERENCED_TABLE_NAME: tb_dept1
       REFERENCED_COLUMN_NAME: id
1 row in set (0.00 sec)

修改原有表的外键约束

外键约束也可以在修改表时添加，但是添加外键约束的前提是：从表中外键列中的数据必须与主表中主键列中的数据一致或者是没有数据。

在修改数据表时添加外键约束的语法格式如下：

ALTER TABLE <数据表名> ADD CONSTRAINT <外键名> FOREIGN KEY(<列名>) REFERENCES <主表名> (<列名>);

示例：修改数据表 tb_emp2，将字段 deptId 设置为外键，与数据表 tb_dept1 的主键 id 进行关联。

# 创建 tb_emp2（从表）
CREATE TABLE tb_emp2
(
	id INT(11) PRIMARY KEY,
	name VARCHAR(25),
	deptId INT(11),
	salary FLOAT
) ENGINE=InnoDB DEFAULT CHARSET=gb2312;

MariaDB [test_db]> desc tb_emp2;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id     | int(11)     | NO   | PRI | NULL    |       |
| name   | varchar(25) | YES  |     | NULL    |       |
| deptId | int(11)     | YES  |     | NULL    |       |
| salary | float       | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+

# 添加外键约束
ALTER TABLE tb_emp2 ADD CONSTRAINT fk_tb_dept1 FOREIGN KEY(deptId) REFERENCES tb_dept1(id);

MariaDB [test_db]> desc tb_emp2;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id     | int(11)     | NO   | PRI | NULL    |       |
| name   | varchar(25) | YES  |     | NULL    |       |
| deptId | int(11)     | YES  | MUL | NULL    |       |
| salary | float       | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+

MariaDB [test_db]> SHOW CREATE TABLE tb_emp2\G
*************************** 1. row ***************************
       Table: tb_emp2
Create Table: CREATE TABLE `tb_emp2` (
  `id` int(11) NOT NULL,
  `name` varchar(25) DEFAULT NULL,
  `deptId` int(11) DEFAULT NULL,
  `salary` float DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_tb_dept1` (`deptId`),
  CONSTRAINT `fk_tb_dept1` FOREIGN KEY (`deptId`) REFERENCES `tb_dept1` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=gb2312

删除外键约束

当一个表中不需要外键约束时，就需要从表中将其删除。外键一旦删除，就会解除主表和从表之间的关联关系。

删除外键约束的语法格式如下所示：

ALTER TABLE <表名> DROP FOREIGN KEY <外键约束名>;

示例：删除数据表 tb_emp2 中的外键约束 fk_tb_dept1。

ALTER TABLE tb_emp2 DROP FOREIGN KEY fk_tb_dept1;

MariaDB [test_db]> SHOW CREATE TABLE tb_emp2\G
*************************** 1. row ***************************
       Table: tb_emp2
Create Table: CREATE TABLE `tb_emp2` (
  `id` int(11) NOT NULL,
  `name` varchar(25) DEFAULT NULL,
  `deptId` int(11) DEFAULT NULL,
  `salary` float DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_tb_dept1` (`deptId`)
) ENGINE=InnoDB DEFAULT CHARSET=gb2312