mysql+ddl+外键_MySQL DDL操作--------外键最佳实战

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. 总结

以需求驱动技术，技术本身没有优略之分，只有业务之分。