MYSQL 8 基本操作之08 (外键约束)

1. 创建父表

create table country
(
country_id  int unsigned not null auto_increment comment '自增主键',
country varchar(50) not null comment '国家名称',
last_update datetime not null default current_timestamp on update current_timestamp comment '修改时间',
primary key(country_id)
) engine=innodb auto_increment=1001 default charset=utf8mb4 collate=utf8mb4_0900_ai_ci;

root@127.0.0.1 : testdb【07:24:17】112 SQL->insert into country(country) values('美利坚');

root@127.0.0.1 : testdb【07:25:41】113 SQL->select * from country;
+------------+-----------+---------------------+
| country_id | country   | last_update         |
+------------+-----------+---------------------+
|       1001 | 美利坚    | 2019-07-14 19:25:41 |
+------------+-----------+---------------------+

root@127.0.0.1 : testdb【07:25:47】114 SQL->update country set country='美国' where country_id=1001;

root@127.0.0.1 : testdb【07:26:30】115 SQL->select * from country;
+------------+---------+---------------------+
| country_id | country | last_update         |
+------------+---------+---------------------+
|       1001 | 美国    | 2019-07-14 19:26:30 |
+------------+---------+---------------------+

2. 创建子表

create table city
(
  city_id int unsigned not null  auto_increment comment '自增主键',
  city_name varchar(50) not null,
  country_id int unsigned not null,
  last_update datetime not null default current_timestamp on update current_timestamp comment '修改时间',
  primary key(city_id),
  key idx_fk_country_id(country_id),
  constraint fk_city_country foreign key(country_id) references country(country_id) on delete restrict on update cascade
) engine=innodb auto_increment=1001 default charset=utf8mb4 collate=utf8mb4_0900_ai_ci;

3.创建外键的语法：

#外键的定义语法：
[CONSTRAINT symbol] FOREIGN KEY [id] (index_col_name, ...)
REFERENCES tbl_name (index_col_name, ...)
[ON DELETE {RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT}]
[ON UPDATE {RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT}]

该语法可以在 CREATE TABLE 和 ALTER TABLE 时使用，如果不指定CONSTRAINT symbol，MYSQL会自动生成一个名字。
ON DELETE、ON UPDATE表示事件触发限制，可设参数：
① RESTRICT（限制外表中的外键改动，默认值）--是指限制在子表有关联记录的情况下父表不能更新
② CASCADE（跟随外键改动）--表示父表更新或删除时，更新或删除子表的对应记录
③ SET NULL（设空值）
④ SET DEFAULT（设默认值）
⑤ NO ACTION（无动作，默认的）
---------------------------------------------------

4. 测试案例( on delete restrict on update cascade )

#在子表插入主表不存在的id则报错
root@127.0.0.1 : testdb【07:36:53】153 SQL->insert into city(city_name,country_id) values('旧金山',100);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`testdb`.`city`, CONSTRAINT `fk_city_country` FOREIGN KEY (`country_id`) REFERENCES `country` (`country_id`) ON DELETE RESTRICT ON UPDATE CASCADE)

root@127.0.0.1 : testdb【07:53:36】154 SQL->insert into city(city_name,country_id) values('旧金山',1001);

#删除存在有关联子表的主表中的数据--报错
root@127.0.0.1 : testdb【07:53:52】155 SQL->delete from country where country_id=1001;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`testdb`.`city`, CONSTRAINT `fk_city_country` FOREIGN KEY (`country_id`) REFERENCES `country` (`country_id`) ON DELETE RESTRICT ON UPDATE CASCADE)

#更新有关联子表的主表数据（子表自动同步更新）
root@127.0.0.1 : testdb【07:54:18】156 SQL->update country set country_id=1002 where country_id=1001;

root@127.0.0.1 : testdb【07:54:46】157 SQL->select * from country;
+------------+---------+---------------------+
| country_id | country | last_update         |
+------------+---------+---------------------+
|       1002 | 美国    | 2019-07-14 19:54:46 |
+------------+---------+---------------------+

root@127.0.0.1 : testdb【07:54:59】158 SQL->select * from city;
+---------+-----------+------------+---------------------+
| city_id | city_name | country_id | last_update         |
+---------+-----------+------------+---------------------+
|    1002 | 旧金山    |       1002 | 2019-07-14 19:53:52 |
+---------+-----------+------------+---------------------+