MySQL DDL操作--------外键最佳实战

tonwei139

　　1. 背景
　　* MySQL有两种常用的引擎类型MyISAM和InnoDB。目前只有InnoDB引擎类型支持外键约束。
　　* 本表的列必须与外键类型相同, 外键必须是外表的唯一键(或主键)。
　　* 设置外建的列不能设置 NO NULL 字段属性。
　　2. 外建作用
　　* 使两张表形成关联,外键只能引用外表中的列的值
　　* 保持数据一致性，完整性，控制存储在外键表中的数据

　　3. 外键实验 [员工 --> 部门 ]
　　* 创建外键依赖的外表 departments
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      当有事件触发时，限制外表中的外键改动
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 插入数据
mysql> INSERT INTO departments SELECT NULL, 'dev';　　
Query OK, 1 row affected (0.01 sec)
　　
Records: 1  Duplicates: 0  Warnings: 0
　　

　　
mysql> INSERT INTO departments SELECT NULL, 'test';
　　
Query OK, 1 row affected (0.01 sec)
　　
Records: 1  Duplicates: 0  Warnings: 0
　　

　　
mysql> INSERT INTO departments SELECT NULL, 'ops';
　　
Query OK, 1 row affected (0.01 sec)
　　
Records: 1  Duplicates: 0  Warnings: 0
　　* 查看部门表数据
mysql> SELECT * FROM departments;　　
+----+------+
　　
| id | name |
　　
+----+------+
　　
|  1 | dev  |
　　
|  2 | test |
　　
|  3 | ops  |
　　
+----+------+
　　
3 rows in set (0.00 sec)
　　* 插入正常数据 （部门编号存在于部门表中）
mysql> INSERT INTO empoyees SELECT NULL, 'M', 22, 2;　　
Query OK, 1 row affected (0.01 sec)
　　
Records: 1  Duplicates: 0  Warnings: 0
　　

　　
mysql> 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的列 ]
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 ]
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 ]
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. 总结
　　以需求驱动技术，技术本身没有优略之分，只有业务之分。