外键
定义
让当前字段的值在另一个表的范围内选择
foreign key(参考字段名)
references 主表(被参考字段名)
on delete 级联动作
on update 级联动作
使用规则
- 主表,从表字段数据类型一致
- 主表被参考字段:KEY的一种,一般为主键
示例:
数据库准备stu_info作为主表被参考,teacher_info作为从表,从表的sid字段引用主表的id字段:
mysql> create database db_info; //创建数据库
Query OK, 1 row affected (0.01 sec)
mysql> use db_info; //切换数据库
Database changed
mysql> create table stu_info( //创建数据表
-> id int primary key auto_increment,
-> name varchar(30),
-> class char(7),
-> money decimal(6,2))engine=innodb charset=utf8mb4;
Query OK, 0 rows affected (0.06 sec)
mysql> insert into stu_info (name,class,money) values //向数据表插入数据
-> ('张三','BLU0001',300),
-> ('李四','BLU0001',400),
-> ('王五','BLU0001',500);
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 00);
mysql> create table teacher_info(
mysql> tid int primary key auto_increment,
mysql> sid int, name varchar(30),
mysql> foreign key(sid) references stu_info(id) on delete cascade on update cascade)
mysql> engine=innodb charset=utf8mb4;
Query OK, 0 rows affected (0.05 sec)
测试向从表插入数据:
从表中sid的取值只能从主表id存在的值中选取,如果超出范围,则报错
mysql> insert into teacher_info (sid,name) values
-> (1,'李雷'),
-> (2,'韩梅梅'),
-> (3,'Lucy');
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> insert into teacher_info (sid,name) values (4,'Lily');
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`db_info`.`teacher_info`, CONSTRAINT `teacher_info_ibfk_1` FOREIGN KEY (`sid`) REFERENCES `stu_info` (`id`) ON DELETE CASCADE ON UPDATE CASCADE)
级联动作:
- cascade:数据级联删除,更新(参考字段)
- restrict(默认):从表有相关联级联,不允许主表操作
- set null:主表删除,更新,从表相关联级联字段值为NULL
示例:
1) cascade
mysql> select * from teacher_info;
+-----+------+-----------+
| tid | sid | name |
+-----+------+-----------+
| 1 | 1 | 李雷 |
| 2 | 2 | 韩梅梅 |
| 3 | 3 | Lucy |
+-----+------+-----------+
3 rows in set (0.00 sec)
mysql> delete from stu_info where id=1;
Query OK, 1 row affected (0.00 sec)
mysql> select * from teacher_info;
+-----+------+-----------+
| tid | sid | name |
+-----+------+-----------+
| 2 | 2 | 韩梅梅 |
| 3 | 3 | Lucy |
+-----+------+-----------+
2 rows in set (0.00 sec)
2) restrict
mysql> create table teacher_info2(
-> tid int primary key auto_increment,
-> sid int,
-> name varchar(30),
-> foreign key(sid) references stu_info(id))
-> engine=innodb charset=utf8mb4;
Query OK, 0 rows affected (0.05 sec)
mysql> insert into teacher_info2 (sid,name) values(2,'韩梅梅');
Query OK, 1 row affected (0.01 sec)
mysql> delete from stu_info where id=2;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`db_info`.`teacher_info2`, CONSTRAINT `teacher_info2_ibfk_1` FOREIGN KEY (`sid`) REFERENCES `stu_info` (`id`))
3) set null
mysql> create table teacher3_info(
-> tid int primary key auto_increment,
-> sid int,
-> name varchar(30),
-> foreign key(sid) references stu_info(id) on delete set null)
-> engine=innodb charset=utf8mb4;
Query OK, 0 rows affected (0.06 sec)
mysql> insert into teacher3_info(sid,name) values(3,'李磊');
Query OK, 1 row affected (0.01 sec)
mysql> select * from teacher3_info;
+-----+------+--------+
| tid | sid | name |
+-----+------+--------+
| 1 | 3 | 李磊 |
+-----+------+--------+
1 row in set (0.00 sec)
mysql> delete from stu_info where id=3;
Query OK, 1 row affected (0.01 sec)
mysql> select * from teacher3_info;
+-----+------+--------+
| tid | sid | name |
+-----+------+--------+
| 1 | NULL | 李磊 |
+-----+------+--------+
1 row in set (0.00 sec)
删除外键
alter table 表名 drop foreign key 外键名;
查看表中外键名:show create table 表名;
mysql> show create table teacher3_info\G //查看表中的外键名称
*************************** 1. row ***************************
Table: teacher3_info
Create Table: CREATE TABLE `teacher3_info` (
`tid` int NOT NULL AUTO_INCREMENT,
`sid` int DEFAULT NULL,
`name` varchar(30) DEFAULT NULL,
PRIMARY KEY (`tid`),
KEY `sid` (`sid`),
CONSTRAINT `teacher3_info_ibfk_1` FOREIGN KEY (`sid`) REFERENCES `stu_info` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)
mysql> alter table teacher3_info drop foreign key teacher3_info_ibfk_1;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
已有表添加外键
用法:alter table 表名 add foreigin key(参考字段) references 主表(被参考字段) [on delete 级联动作 on update 级联动作]
示例:
mysql> show create table teacher3_info\G
*************************** 1. row ***************************
Table: teacher3_info
Create Table: CREATE TABLE `teacher3_info` (
`tid` int NOT NULL AUTO_INCREMENT,
`sid` int DEFAULT NULL,
`name` varchar(30) DEFAULT NULL,
PRIMARY KEY (`tid`),
KEY `sid` (`sid`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai _ci
1 row in set (0.00 sec)
mysql> alter table teacher3_info add foreign key(sid) references stu_info(id) on delete set null on update cascade;
Query OK, 1 row affected (0.08 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> show create table teacher3_info\G
*************************** 1. row ***************************
Table: teacher3_info
Create Table: CREATE TABLE `teacher3_info` (
`tid` int NOT NULL AUTO_INCREMENT,
`sid` int DEFAULT NULL,
`name` varchar(30) DEFAULT NULL,
PRIMARY KEY (`tid`),
KEY `sid` (`sid`),
CONSTRAINT `teacher3_info_ibfk_1` FOREIGN KEY (`sid`) REFERENCES `stu_info` (`id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)