mysql -- 外键

外键

定义

让当前字段的值在另一个表的范围内选择

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)