MySQL笔记——外键约束和表关系（一对一，多对一，多对多）_多对多外键约束关系

相关文章推荐

买醉的铁链 · 四川大学就业指导中心· 1 月前 ·

健壮的山寨机 · 高分屏显示模糊修复工具 - 苍 - 博客园· 4 月前 ·

睿智的小蝌蚪 · 锈湖根源第十八关攻略 Rusty ...· 4 月前 ·

飘逸的领结 · 竞逐“双机场”，谁将起飞？· 6 月前 ·

有胆有识的灌汤包 · 任艳红_百度百科· 9 月前 ·

本文详细介绍了数据库设计中的外键约束，包括如何创建、删除外键，以及表关系的三种类型：一对一、一对多和多对多。通过实例展示了如何在员工信息表和部门信息表中建立外键实现一对多关系，以及在订单表、商品表和中间表中实现多对多关系。同时，还探讨了一对一关系的实现，如用户表和用户详情表的关联。摘要由CSDN通过智能技术生成 -- 建完表后添加外键约束 ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段名称) REFERENCE 主表名称(主表列名称) （2）删除约束 ALTER TABLE 表名 DROP FOREIGN KEY 外键名称;

首先创建员工信息表和部门信息表，并在员工表中添加外键。（把员工表当作从表，部门表当作主表），员工表和部门表就是一个多对一的案例
-- 员工表
CREATE TABLE emp(
	id int PRIMARY KEY auto_increment, -- id
	name VARCHAR(20),-- 姓名
	age int, -- 年龄
	dep_id int, -- 部门
	-- 添加外键dep_id，关联dept表的id主键
	CONSTRAINT fk_emp_dept FOREIGN KEY(dep_id) REFERENCES dept(id)
-- 部门表
CREATE TABLE dept(
	id int PRIMARY KEY auto_increment, -- 部id
	dep_name VARCHAR(20), -- 部门名称
	addr VARCHAR(20) -- 地址
INSERT INTO emp(NAME,age,dep_id) VALUES
('张三',20,1),
('李四',20,1),
('王五',20,1),
('赵六',20,2),
('田七',22,2),
('周八',18,2);
INSERT INTO dept(dep_name,addr) VALUES
('研发部','广州'),
('销售部','深圳');
-- 删除外键
ALTER TABLE emp DROP FOREIGN KEY fk_emp_dept;
按照如下图所示顺序执行sql语句：部门表–>员工表–>部门信息–>员工信息
 
 
 
  
执行完毕后如下图点击对象选中这两个表，点击鼠标右键，选中‘逆向表到模型’。
可以看到这两个表的模型中间有一条线，这条线代表外键。
此时我们执行删除外键的语句
然后再次执行上述‘逆向表到模型’ 可以看到线消失了，表示我们删除成功。
二、表关系之一对多
 
1、一对多（多对一）
	如：部门和员工，一个部门对应多个员工，一个员工对应一个部门
2、实现方式：在多的一方建立外键，指向一的一方的主键
3、具体实现参照上例，部门和员工。
三、表关系之多对多
 
	1、多对多
		如：商品和订单，一个商品对应多个订单，一个订单包含多个商品
	2、实现方式
		建立第三张中间表，中间表至少包含两个外键，分别关联两方主键
	3、实现案例：
-- 订单表
CREATE TABLE tb_order(
	id int PRIMARY KEY auto_increment, -- id
	payment double(10,2),
	payment_type TINYINT,
	status TINYINT
-- 商品表
CREATE TABLE tb_goods(
	id int PRIMARY KEY auto_increment,
	title VARCHAR(100),
	price DOUBLE(10,2)
-- 订单商品中间表
CREATE TABLE tb_order_goods(
	id int PRIMARY KEY auto_increment,
	order_id int,
	goods_id int,
	count int
-- 添加外键，向订单商品中间表添加两个外键，关联商品表和订单表的主键。
ALTER TABLE tb_order_goods ADD CONSTRAINT fk_order_id FOREIGN KEY(order_id) REFERENCES tb_order(id);
ALTER TABLE tb_order_goods ADD CONSTRAINT fk_goods_id FOREIGN KEY(goods_id) REFERENCES tb_goods(id);
如图执行创建三个表
执行添加外键的语句
再次执行上述‘逆向表到模型’  具体对应关系如图标注。
四、表关系之一对一
 
	1、一对一
		如：用户信息和用户详情信息
		一对一关系多用于表拆分，将一个实体中经常使用的字段放一张表，不经常使用的字段放另一张表，用于提升查询能力
	2、实现方式：在任意一方加入外键，关联另一方主键，并且设置外键为唯一（UNIQUE）
	3、实现案例
-- 创建用户表
CREATE TABLE tb_user(
	id int PRIMARY KEY auto_increment,
	photo VARCHAR(30),
	nickname VARCHAR(20),
	age int,
	sex char(2),
	desc_id int NOT NULL UNIQUE -- 设置关联的用户详情表主键不为空且唯一
-- 创建用户详情表
CREATE TABLE tb_user_desc(
	id int PRIMARY KEY auto_increment,
	city VARCHAR(10),
	edu VARCHAR(10),
	income DOUBLE(7,2),
	status VARCHAR(10)
-- 添加用户
INSERT INTO TABLE tb_user(photo,nickname,age,sex,desc_id) VALUES
('a.jpg','一场梦',23,'女',1),
('b.png','风清扬',35,'男',2),
('c.jpg','赵云',41,'男',3),
-- 添加用户详情信息
INSERT INTO TABLE tb_user_desc(city,edu,income,status) VALUES
('广州','硕士',300000,'单身'),
('湖北','本科',100000,'离异'),
('河南','本科',200000,'单身'),
-- 添加外键
ALTER TABLE tb_user ADD CONSTRAINT fk_user_userdesc FOREIGN KEY(desc_id) REFERENCES tb_user_desc(id);
如图执行语句创建用户表和用户详情表
往两张表中添加数据
再次执行上述‘逆向表到模型’  具体对应关系如图。
				业务需求：用户表r_user保存用户名等信息。现需要给每个用户设置工作基地，一个用户可以有多个工作基地，多个用户也可以有一个工作基地，即多对多关系。（外键，若有两个表A，B，C是A的主键，而B中也有C字段，则C就是表B的外键，外键约束主要用来维护两个表之间数据的一致性）
设计方案：
方案一：建立一张用户基地表，与r_user与用户基地表，保持一对多的关系，如图所示，r_user的主键id做为r...




    

				外键其实就是用于通知数据库两张表数据之间对应关系的这样的一个列，这样数据库就会帮我们维护两张表中数据之间的关系
如果不指定外键，数据库不知道两张表之间的对应关系
（1） 创建表的同时约束外键
create table emp(
id int,
name varchar(50),
dept_id int,
foreign key (dept_id) references dept (id) 
常见的表关系分为三种
一对多、一对一、多对多。
一对多：对于一对多来说可以在多的一方添加列保存一的一方
alter table tf1 modify x char(4) default '';
alter table tf1 change y m char(4) default '';
mysql...
在数据库中，单表的操作是最简单的，但是在实际业务中最少也有十几张表，并且表与表之间常常相互间联系；
一对多、一对一、多对多是表与表之间的常见的关系，初学时在多表连接时会纠结如何写对应的Sql语句，下面就分享一些小技巧；
一对多是最基础的表间关系，意思是一张表A中的一条记录可以对应另一张表B中的多条记录，另一张表B中的一条记录只能对应一张表A中的一条记录
举个一对多的例子：
有两张表，
表A：学生表student（子表）
id     name   class_id（外键非空：班级id）
				假设有两个数据表A和B，A表中有一个字段a_id，B表中有一个字段b_id，需要通过a_id和b_id建立外键关联。
首先，在A表中创建一个名为a_id的主键，表示a_id是A表的主键。在B表中创建一个名为b_id的主键，表示b_id是B表的主键。
然后，在B表中创建一个名为a_id的外键，表示a_id在B表中是一个外键，它与A表中的a_id字段建立关联。具体语法如下：
ALTER TABLE B ADD CONSTRAINT fk_a_id FOREIGN KEY (a_id) REFERENCES A(a_id);
这个语句的含义是在B表中添加一个名为fk_a_id的外键，它的外键字段是a_id，它与A表中的a_id字段建立关联。
完成以上步骤后，A表和B表之间的外键关联就建立完成了。现在，当向B表中插入数据时，如果a_id字段的值不存在于A表中，就会出现外键约束错误。同样，如果从A表中删除一个a_id值，也会出现外键约束错误。这种机制可以保证数据的完整性和一致性。
				SpringMVC遇到的问题——GET http://localhost/spring_mvc_war_exploded/js/jquery-3.3.1.js net::ERR_ABORTED 404
				基于Java+SpringBoot+Vue+HTML5人口老龄化社区服务与管理平台(源码+LW+调试文档+讲解等)/老龄化社区服务/老龄化人口管理/社区服务平台/社区服务管理/人口老龄化解决方案
				基于Java+SpringBoot+Vue+HTML5高校办公室行政事务管理系统(源码+LW+调试文档+讲解等)/高校行政事务管理系统/办公室事务管理软件/高校办公室管理解决方案/行政事务系统