分享程序员开发的那些事...
更新时间：2022-04-20 17:05:37
1. 背景
* MySQL有两种常用的引擎类型MyISAM和InnoDB。目前只有InnoDB引擎类型支持外键约束。
* 本表的列必须与外键类型相同, 外键必须是外表的唯一键(或主键)。
* 设置外建的列不能设置 NO NULL 字段属性。
2. 外建作用
* 使两张表形成关联,外键只能引用外表中的列的值
* 保持数据一致性，完整性，控制存储在外键表中的数据
3. 外键实验 [ 员工 --> 部门 ]
* 创建外键依赖的外表 departments
|
1
2
3
4
5
|
mysql> CREATE TABLE departments( -> id BIGINT PRIMARY KEY NOT NULL AUTO_INCREMENT,
-> name VARCHAR(64) NOT NULL
-> )ENGINE=INNODB CHARSET=utf8mb4;
Query OK, 0 rows affected (0.05 sec) |
* 创建员工表 empoyees, 并建立外键
指定外键关键字： FOREIGN KEY(列名)
引用外键关键字： REFERENCES <外键表名>(外键列名)
事件触发限制: [默认] no action
ON DELETE SET NULL 当外表字段删除时，本表设置为NULL(空值) [ 不推荐设置此荐 ]
ON UPDATE CASCADE 当外表字段更新时，本表外键级联更新
ON DELETE/UPDATE SET DEFAULT 当有事件触发时，设置为默认值
ON DELETE/UPDATE RESTRICT 当有事件触发时，限制外表中的外键改动
|
1
2
3
4
5
6
7
8
9
10
11
|
mysql> CREATE TABLE empoyees( -> id BIGINT PRIMARY KEY NOT NULL AUTO_INCREMENT,
-> sex ENUM('M', 'F') NOT NULL,
-> age INT NOT NULL,
-> department BIGINT,
-> FOREIGN KEY(department)
-> REFERENCES departments(id)
-> ON DELETE SET NULL
-> ON UPDATE CASCADE
-> )ENGINE=INNODB CHARSET=utf8mb4;
Query OK, 0 rows affected (0.12 sec) |
* 对部门表 departments 插入数据
|
1
2
3
4
5
6
7
8
9
10
11
|
mysql> INSERT INTO departments SELECT NULL, 'dev';
Query OK, 1 row affected (0.01 sec)Records: 1 Duplicates: 0 Warnings: 0mysql> INSERT INTO departments SELECT NULL, 'test';
Query OK, 1 row affected (0.01 sec)Records: 1 Duplicates: 0 Warnings: 0mysql> INSERT INTO departments SELECT NULL, 'ops';
Query OK, 1 row affected (0.01 sec)Records: 1 Duplicates: 0 Warnings: 0 |
* 查看部门表数据
|
1
2
3
4
5
6
7
8
9
|
mysql> SELECT * FROM departments;+----+------+| id | name |
+----+------+| 1 | dev || 2 | test |
| 3 | ops |+----+------+3 rows in set (0.00 sec)
|
* 插入正常数据 （部门编号存在于部门表中）
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
mysql> INSERT INTO empoyees SELECT NULL, 'M', 22, 2;
Query OK, 1 row affected (0.01 sec)Records: 1 Duplicates: 0 Warnings: 0mysql> SELECT * FROM empyees;ERROR 1146 (42S02): Table 'mytest.empyees' doesn't exist
mysql> SELECT * FROM empoyees;+----+-----+-----+------------+| id | sex | age | department |
+----+-----+-----+------------+| 1 | M | 22 | 2 |+----+-----+-----+------------+1 row in set (0.01 sec)
|
* 插入非正常数据 (部门编号不存在于部门表中) [ 部门表中不存在id为4的列 ]
|
1
2
|
mysql> INSERT INTO empoyees SELECT NULL, 'M', 22, 4;
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`mytest`.`empoyees`, CONSTRAINT `empoyees_ibfk_1` FOREIGN KEY (`department`) REFERENCES `departments` (`id`) ON DELETE SET NULL ON UPDATE CASCADE)
|
* 修改部门表数据 [ 员工表中已有数据关联部门表中id为2 ]
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
|
mysql> SELECT * FROM empoyees;
+----+-----+-----+------------+
| id | sex | age | department |+----+-----+-----+------------+
| 1 | M | 22 | 2 |+----+-----+-----+------------+
1 row in set (0.01 sec)
mysql> UPDATE departments SET id = 4 WHERE id=2;
Query OK, 1 row affected (0.04 sec)Rows matched: 1 Changed: 1 Warnings: 0
mysql> SELECT * FROM empoyees;
+----+-----+-----+------------+
| id | sex | age | department |+----+-----+-----+------------+
| 1 | M | 22 | 4 |+----+-----+-----+------------+
1 row in set (0.01 sec)
|
* 删除部门表数据 [ 员工表中已有数据关联部门表中id为4 ]
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
|
mysql> SELECT * FROM empoyees;
+----+-----+-----+------------+
| id | sex | age | department |+----+-----+-----+------------+
| 1 | M | 22 | 2 |+----+-----+-----+------------+
1 row in set (0.01 sec)
mysql> DELETE FROM departments WHERE id = 4;
Query OK, 1 row affected (0.01 sec)mysql> SELECT * FROM empoyees;
+----+-----+-----+------------+
| id | sex | age | department |+----+-----+-----+------------+
| 1 | M | 22 | NULL |
+----+-----+-----+------------+
1 row in set (0.00 sec)
|
4. 总结
以需求驱动技术，技术本身没有优略之分，只有业务之分。
本文转自asd1123509133 51CTO博客，原文链接：http://blog.51cto.com/lisea/1943689，如需转载请自行联系原作者
