mysql 外键

smile_pbb

已于 2023-07-26 17:12:54 修改

阅读量247

点赞数

分类专栏： mysql 文章标签： mysql

于 2023-07-25 19:59:05 首次发布

本文链接：https://blog.csdn.net/smile_pbb/article/details/131925263

版权

mysql 专栏收录该内容

5 篇文章 0 订阅

订阅专栏

一、概念

mysql多表指的是多张表之间建立联系。其中有一下几种关系

1.一对一

一张表中的一条记录与另外一张表中有且仅有一条记录有关系

2.一对多

一对多也叫做多对一，含义就是：一张表中的一条记录与另外一张表中的多条记录对应，反过来另外一张表中的多条记录只能对应当前表中的一条记录
一对多的关系在实际生产当中使用非常常见。
一对多的核心解决方案是：如何让记录能够正确的匹配到另外表中的数据

3.对多多

多对多关系在生产中也比较常见
多对多关系是无法在自身表中维护对应表关系的，需要通过第三方表来实现将多对多关系变成多对一的关系
也就是说设计一个中间表：记录两表之间的对应关系
中间表与其他表都是多对一的关系

二、外键约束

1.外键

“foreign key”,在一张表中指向外部表 “主键” 的字段定义成为 “外键”

1.1 语法

外键必须要通过语法指定才能称之为外键.
外键也可以不指定名称，系统会自动生成

[constraint 外键名] foreign key(当前表字段名) references 外部表(主键字段)

1.2 外键构成条件

外键字段必须与对应表的主键字段类型一致
外键字段本身要求是一个索引（创建外键会自动生成一个索引）

1.3 实例

创建班级表
create table class( 
    id int primary key auto_increment, 
    name varchar(50) not null unique 
)charset utf8;

创建学生表
create table students( 
    id int primary key auto_increment,
    name varchar(50) not null,c_id int comment  "指向class表的主键",
    foreign key(c_id) references class(id)
);

查看两张表

mysql> desc class;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| name  | varchar(50) | NO   | UNI | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)


mysql> desc students;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| name  | varchar(50) | NO   |     | NULL    |                |
| c_id  | int(11)     | YES  | MUL | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)


#发现c_id 字段变成了索引
mysql> show create table students\G
*************************** 1. row ***************************
       Table: students
Create Table: CREATE TABLE `students` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  `c_id` int(11) DEFAULT NULL COMMENT '指向class表的主键',
  PRIMARY KEY (`id`),
  KEY `c_id` (`c_id`),
  CONSTRAINT `students_ibfk_1` FOREIGN KEY (`c_id`) REFERENCES `class` (`id`)   #这里mysql自动创建了外键名字，叫做students_ibfk_1
) ENGINE=InnoDB DEFAULT CHARSET=latin1

2.外键约束

当表建立外键之后，外键就会对主表（外键指向的表）和子表（外键所在的表）里的数据产生约束效果。
外键约束的是写操作(默认操作)

2.1 新增

子表插入的数据对应的外键必须在主表存在

mysql> select * from class;
Empty set (0.00 sec)

mysql> select * from students;
Empty set (0.00 sec

#插入数据失败
mysql> insert into students values(NULL,"zhangsan",1);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`huabeidb`.`students`, CONSTRAINT `students_ibfk_1` FOREIGN KEY (`c_id`) REFERENCES `class` (`id`))

2.2 修改

主表的记录如果在子表存在，那么主表的主键不能修改(注意是主键字段，别的字段可以修改)

mysql> insert into class values(NULL,"python");
Query OK, 1 row affected (0.01 sec)

mysql> select * from class;
+----+--------+
| id | name   |
+----+--------+
|  1 | python |
+----+--------+
1 row in set (0.00 sec)

mysql> insert into students values(NULL,"zhangsan",1);
Query OK, 1 row affected (0.01 sec)

mysql> select * from students;
+----+----------+------+
| id | name     | c_id |
+----+----------+------+
|  2 | zhangsan |    1 |
+----+----------+------+
1 row in set (0.00 sec)

# 此时students 中的zhangsan 已经和 class表的 python记录做了对应

#更改class表的主键ID失败
mysql> update class set id = 10 where id = 1;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`huabeidb`.`students`, CONSTRAINT `students_ibfk_1` FOREIGN KEY (`c_id`) REFERENCES `class` (`id`))

2.3 删除

主表的记录如果在子表存在，那么主表的主键不能删除

mysql> delete from  class where id = 1;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`huabeidb`.`students`, CONSTRAINT `students_ibfk_1` FOREIGN KEY (`c_id`) REFERENCES `class` (`id`))

3.外键约束控制

外键可以在定义时控制外键的约束作用

3.1 控制类型

on update: 父表更新时子表的表现
on delete: 父表删除时子表的表现

3.2 控制方式

cascade: 级联操作，父表操作后子表跟随操作
set null: 置空操作，父表操作后，子表关联的外键字段置空
restrict: 严格模式，不允许父表操作（默认）
no action：子表没有动作

3.3 外键约束控制原则

更新级联: on update cascade
删除置空: on delete set null

4.外键管理

4.1 新增外键

alter table 表名 add [constraint `外键名`] foreign key(外键字段) references 主表名(主键) [ on 外键约束]

4.2 删除外键

alter table 表名 drop foreign key 外键名;

4.3 更新外键

先删除后新增

#查看表的外键名称
mysql> show create table students\G
*************************** 1. row ***************************
       Table: students
Create Table: CREATE TABLE `students` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  `c_id` int(11) DEFAULT NULL COMMENT '指向class表的主键',
  PRIMARY KEY (`id`),
  KEY `c_id` (`c_id`),
  CONSTRAINT `students_ibfk_1` FOREIGN KEY (`c_id`) REFERENCES `class` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

#删除外键
mysql> alter table students drop foreign key students_ibfk_1;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0


#在次查看 外键已经没有了
mysql> show create table students\G
*************************** 1. row ***************************
       Table: students
Create Table: CREATE TABLE `students` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  `c_id` int(11) DEFAULT NULL COMMENT '指向class表的主键',
  PRIMARY KEY (`id`),
  KEY `c_id` (`c_id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)


#新增外键 并加上约束控制
mysql> alter table students add foreign key students(c_id) references class(id) on update cascade on delete set null;
Query OK, 1 row affected (0.03 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> show create table students\G
*************************** 1. row ***************************
       Table: students
Create Table: CREATE TABLE `students` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  `c_id` int(11) DEFAULT NULL COMMENT '指向class表的主键',
  PRIMARY KEY (`id`),
  KEY `students` (`c_id`),
  CONSTRAINT `students_ibfk_1` FOREIGN KEY (`c_id`) REFERENCES `class` (`id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

修改验证

mysql> select * from class;
+----+--------+
| id | name   |
+----+--------+
|  1 | python |
+----+--------+
1 row in set (0.00 sec)

mysql> select * from students;
+----+----------+------+
| id | name     | c_id |
+----+----------+------+
|  2 | zhangsan |    1 |
+----+----------+------+
1 row in set (0.00 sec)

修改class表主键

mysql> update class set id = 10 where id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from class;
+----+--------+
| id | name   |
+----+--------+
| 10 | python |
+----+--------+

查看students表的变化，发现也跟着变化了。

mysql> select * from students;
+----+----------+------+
| id | name     | c_id |
+----+----------+------+
|  2 | zhangsan |   10 |
+----+----------+------+
1 row in set (0.00 sec)

删除验证

mysql> select * from students;
+----+----------+------+
| id | name     | c_id |
+----+----------+------+
|  2 | zhangsan |   10 |
+----+----------+------+
1 row in set (0.00 sec)

mysql> delete from class where id = 10;
Query OK, 1 row affected (0.00 sec)

mysql> select * from students;
+----+----------+------+
| id | name     | c_id |
+----+----------+------+
|  2 | zhangsan | NULL |
+----+----------+------+
1 row in set (0.00 sec)