- 创建父表
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 |
+------------+---------+---------------------+
- 创建子表
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（无动作，默认的）
---------------------------------------------------
- 测试案例( 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 |
+---------+-----------+------------+---------------------+
网友评论