MySQL 外键约束和表关系相关总结
作者:程序媛小庄 发布时间:2024-01-17 00:09:26
目录
外键(Foreign Key)
如何确定表关系
如何建立表关系
一对多关系 - 员工表和部门表
多对多
一对一
表关系总结
外键(Foreign Key)
按照上述所说,一张表存储员工信息会极大的浪费资源,重复数据太多,这个问题就类似于将所有的代码都写在了一个py文件中,因此我们可以将一个表拆成不同的表,在这不同的表之间建立关联,而建立关联就需要使用外键foreign key。外键也属于约束条件的一种。
如何确定表关系
表与表之间的关系有三种一对多、多对多、一对一。那么如何确定表与表之间的关系呢?
在确定表与表之间的关系时建议换位思考,什么意思呢?就是分别站在两张表的角度去考虑,比如员工表和部门表的关系:
先站在员工表的角度:员工表中一个员工能否属于多个部门呢?答案是不能
再站在部门表的角度:部门表中一个部门能否有多个员工呢?答案是可以
因此员工表与部门表是单向的一对多,那么员工表和部门表就是一对多的关系。
如何建立表关系
在建立表关系时,表与表之间的关联通常以主键id作为关联字段。
一对多关系 - 员工表和部门表
在MySQL的关系在没有多对一的概念,一对多和多对一都是一对多。在创建一对多表关系时需要遵循以下几点:
第一,外键字段建立在多的一方,即员工表
第二,在创建表的时候,一定要先创建被关联一方,即部门表
第三,在录入数据的时候也必须先录入被关联表的数据,即部门表的数据
第四,当不同的表建立关系时,需要进行级联更新和删除也可以称为同步更新同步删除,如果不建立级联更新和删除的话,无法对被关联表中被关联的数据进行删除或者修改id的操作,因为两张表是相互关联的。
-- 创建被关联表,部门表
mysql> create table bm(
id int primary key auto_increment,
bm_name varchar(10),
bm_desc char(64)
);
Query OK, 0 rows affected (0.01 sec)
mysql> desc bm;
+---------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| bm_name | varchar(10) | YES | | NULL | |
| bm_desc | char(64) | YES | | NULL | |
+---------+-------------+------+-----+---------+----------------+
3 rows in set (0.01 sec)
-- 创建外键所在的表,员工表
mysql> create table yg(
id int primary key auto_increment,
yg_name varchar(6),
bm_id int,
foreign key(bm_id) references bm(id) -- 表示bm_id是外键字段,关联到bm表中的id字段
on update cascade # 级联更新
on delete cascade # 级联删除
);
Query OK, 0 rows affected (0.10 sec)
mysql> desc yg;
+---------+------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------+------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| yg_name | varchar(6) | YES | | NULL | |
| bm_id | int(11) | YES | MUL | NULL | |
+---------+------------+------+-----+---------+----------------+
3 rows in set (0.01 sec)
-- 插入数据
mysql> insert into bm (bm_name, bm_desc) values ('python', '人生苦短'),('go', 'let us go');
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select * from bm;
+----+---------+--------------+
| id | bm_name | bm_desc |
+----+---------+--------------+
| 1 | python | 人生苦短 |
| 2 | go | let us go |
+----+---------+--------------+
2 rows in set (0.00 sec)
mysql> insert into yg (yg_name, bm_id) values ('xu', 1), ('zhuang', 2), ('lili', 1);
Query OK, 3 rows affected (0.09 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select * from yg;
+----+---------+-------+
| id | yg_name | bm_id |
+----+---------+-------+
| 2 | xu | 1 |
| 3 | zhuang | 2 |
| 4 | lili | 1 |
+----+---------+-------+
3 rows in set (0.00 sec)
-- 外键关联的数据必须在被关联表中存在否则会报错哦~
mysql> insert into yg (yg_name, bm_id) values ('xu', 3);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`book_manage`.`yg`, CONSTRAINT `yg_ibfk_1` FOREIGN KEY (`bm_id`) REFERENCES `bm` (`id`))
-- 如果不使用级联更新和删除的话会出现下面的错误,下述的SQL语句后面的文章都会介绍。。。
mysql> update bm set id=5 where id=2; -- 将bm表中id=2的记录改为id=5
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`book_manage`.`yg`, CONSTRAINT `yg_ibfk_1` FOREIGN KEY (`bm_id`) REFERENCES `bm` (`id`))
mysql> delete from bm where id =2; -- 删除bm表中id为2的那条记录
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`book_manage`.`yg`, CONSTRAINT `yg_ibfk_1` FOREIGN KEY (`bm_id`) REFERENCES `bm` (`id`))
多对多
多对多的关系以书籍和作者为例进行详细介绍,为什么书籍和作者属于多对多的关系呢?
先站在书籍表的角度:一本书是否可以有多个作者?答案是可以
再站在作者表的角度:一个作者是否可以写多本书?答案是可以
书籍表和作者表是双向的一对多那么这两张表的关系就是多对多。
我们先来创建两张表:
-- 创建书籍表
create table book(
id int primary key auto_increment,
title varchar(32),
price int,
author_id int,
foreign key(author_id) references author(id)
on update cascade
on delete cascade
);
-- 创建作者表
create table author(
id int primary key auto_increment,
name varchar(32),
age int,
book_id int,
foreign key(book_id) references book(id)
on update cascade
on delete cascade
);
如果按照上述方式创建表的话肯定是不可能成功的,在创建一对多的表关系时我我们说要先创建被关联表,也就是没有外键的表,可是多对多关系是双向的一对多,每张表中都会有外键的存在,怎么办呢?解决方案就是创建第三张表,这第三张表用来专门存储多对多关系的两张表的关联。
-- 创建书籍表
mysql> create table book(
id int primary key auto_increment,
name varchar(10),
price int
);
Query OK, 0 rows affected (0.01 sec)
-- 创建作者表
mysql> create table author(
id int primary key auto_increment,
name varchar(6),
age int
);
Query OK, 0 rows affected (0.01 sec)
-- 创建第三章表,存储book和author表的关联关系
mysql> create table book2author(
id int primary key auto_increment,
author_id int,
book_id int,
foreign key(author_id) references author(id)
on update cascade
on delete cascade,
foreign key(book_id) references book(id)
on update cascade
on delete cascade);
Query OK, 0 rows affected (0.02 sec)
一对一
如果一张表的字段特别多,每次查询数据时又不是所有的字段都能用的到,我们就可以将表一一分为二,比如说用户信息表,用户的信息包括用户名 密码 用户的年龄 用户的性别 地址 电话等等,可能经常用的只有用户的用户名和密码,这种情况我们就可以将一张用户信息表拆分成用户基本信息表和用户详细信息表,同样判断这两张表的关系还是通过换位思考:
首先看用户基本信息表:一个用户能否有多个详细信息?答案是不可以;
再看用户详情表:一个用户详情能否属于多个用户?答案是不可以;
单向的一对多都不成立,那么两者之间的表关系就是一对一或者没有关系。
使用SQL语句建立一对一的外键关系时,外键建在任意一方都可以,但是推荐将外键建在查询频率较高的表中,同样的,在创建表时还是先创建被关联表。
-- 创建用户详情表
create table authordetail(
id int primary key auto_increment,
phone int,
addr varchar(64)
);
-- 用户基本信息表
create table author(
id int primary key auto_increment,
name varchar(32),
age int,
authordetali_id int,
foreign key(authordetali_id) references authordetali(id)
on update cascade
on delete cascade
);
表关系总结
表关系建立需要使用外键foreign key,判断表与表之间的关系通过换位思考的方式。
一对多表关系:外键建在多的一方
一对一表关系:外键建在任意一方都可以,推荐建在查询频率高的一方
多对多表关系:需要单独创建第三张表存储两张表的关联关系
来源:https://juejin.cn/post/6974940551934115877
猜你喜欢
- 表一、运算符与特殊字符 运算符描述/选择子元素,返回左侧元素的直接子元素;如果"/"位于最左侧表示选择根结点的直接子元素
- 在读文件时常常得到一些\n和引号之类的符号,可以使用字符串的成员函数strip()来去除。1.去除首尾不需要的字符a= '"
- 本文实例讲述了Go语言实现的web爬虫方法。分享给大家供大家参考。具体分析如下:这里使用 Go 的并发特性来并行执行 web 爬虫。修改 C
- 本文实例讲述了Python实现获取邮箱内容并解析的方法。分享给大家供大家参考,具体如下:# -*- coding: utf-8 -*-fro
- 前言首先线程和线程池不管在哪个语言里面,理论都是通用的。对于开发来说,解决高并发问题离不开对多个线程处理。我们先从线程到线程池,从每个线程的
- 如下所示:from numpy import *import numpy as npimport matplotlib.pyplot as
- Pytest和Unittest测试框架的区别?如何区分这两者,很简单unittest作为官方的测试框架,在测试方面更加基础,并且可以再次基础
- Phar是什么在百度中得到介绍是这样的:在软件中,PHAR(PHP归档)文件是一种打包格式,通过将许多PHP代码文件和其他资源(例如图像,样
- 图片的宽度和高度是未知的,没有一个固定的尺寸,在这个前提下要使图片在一个固定了宽度和高度的容器中垂直居中,想想感觉还是挺麻烦的,由于最近的项
- 1:strip()方法去除字符串开头或者结尾的空格>>> a = " a b c ">>&
- 本文实例为大家分享了python类支持比较运算的具体代码,供大家参考,具体内容如下案例:有时我们希望自定义的类,实例间可以使用比较运算符进行
- 复数数据结构在 cpython 当中对于复数的数据结构实现如下所示:typedef struct { double
- 存储过程的优缺点: 存储过程优点: 1.由于应用程序随着时间推移会不断更改,增删功能,T-SQL过程代码会变得更复杂,StoredProce
- RNN(recurrent neural Network)循环神经网络主要用于自然语言处理(nature language processi
- 很多小伙伴对于slice参数的概念理解停留在概念上,切片的参数有三个,分别是step 、start 、stop 。因为参数的值也是多变的,所
- 一、获取二叉树的深度就是二叉树最后的层次,如下图:实现代码:def getheight(self): &n
- 今早打开 腾讯ISD的博客 ,看到一篇新的文章,《迷你屋视觉规范简介》,赶紧看了来学习。不过给我抓到问题咯,臭鱼不介意我在这说下吧:这套规范
- 实例076:做函数题目:编写一个函数,输入n为偶数时,调用函数求1/2+1/4+...+1/n,当输入n为奇数时,调用函数1/1+1/3+.
- IIS设置首先打开IIS就不用说了巴第2步右建默认网站属性第3步主目录选项卡点击第3步的配置按钮弹出窗口的设置,至此IIS设置完毕任意编辑器
- 数据库镜像是将数据库事务处理从一个数据库移动到不同环境中的另一个数据库中。镜像的拷贝是一个备用的拷贝,不能直接访问,它只用在错误恢复的情况下