mysql约束条件之外键详解(建立表与表之间的关系)

外键

在介绍外键之前呢我们先来点 前戏

1、定义一个员工表
    id	 name	  age 	      dep_name	        dep_desc
    1	jason	  18		外交部		搞外交
    2	kevin	  28		教学部		教书育人
    3	tony	  38		教学部		教书育人
    4	oscar	  48		安保部		保家卫国
    5	jackson	  58		财务部		发工资
    
我们看一下这个表有没有什么不足之处(不清晰地方)：
1. 表内部数据混乱：这个表是以员工信息为主呢还是以部门分配为主呢
2. 反复的录入重复的数据 ：多个部分可对应多个员工(录入数据的话很繁琐)
3. 修改数据太过繁琐 浪费磁盘空间
4. 极大地影响了操作数据的效率


#  如何解决这一问题呢：
我们将上述表拆分成两张表 (员工表)  (部门表)
    id	name	age 
    1	jason	18
    2	kevin	28
    3	tony	38
    4	oscar	48
    5	jackson	58
    
    id	dep_name	dep_desc
    1	外交部		搞外交
    2	教学部		教书育人
    3	安保部		保家卫国
    4	财务部		发工资
    
这样就解决了上述是个缺陷
那个怎么让这两个表产生关系呢。

所以有了'外键' : 用来记录表与表之间的关系

如何查找表关系

表与表主键的关系只有四种：
1.多对一  (一对多就是多对一)
2.多对多
3.一对一
4.没有关系

查找表关系：换位思考即可

解释：

1、多对一
书籍表与出版社表之间的关系：
	1、站在书籍表的角度
    	问：一本相同内容的书能否对应多个出版社
        答：不可以
     2、站在出版社的角度
    	问：一个出版社是否可以对应多个书籍
        答：可以
        
#  结论 ：一个可以一个不可以 表关系即为:‘多对一’

实例：在写外键字段时，先普通字段再考虑外键字段这样不容易出错

外键关键字：foreign key(本表的外键字段) references 被关联表表明(被关联表表的id字段)
这样写：本表的外键字段只能写另一张表的id字段(这样才能产生关系)

# 创建表：
书籍(关联表)：
create table book(
    id int primary key auto_increment,
    title varchar(32),
    price int comment '价格',
    pub_id int comment '外键字段',
    foreign key(pub_id) references publish(id)
);

出版社(被关联表)：
create table publish(
    id int primary key auto_increment,
    name varchar(32)
);


# 存储数据：一定要先存储被关联表数据
关联表：
	insert into publish(name) values('东方出版社'),('西方出版社');
	
被关联表：
	insert into book(title,price,pub_id) values('python',20000,1),('linux',30000,2),('java',22000,1),('php',120000,2);

外键约束条件：

1. 在创建表的时候需要先创建被关联表(没有外键的表)
	解释：因为没有被关联表 在创建表的时候无法识别到被关联表 就会报错

2. 在写入数据的时候也需要先录入被关联表(没有外键的表)
	解释：因为在存储信息的时候 如果被关联表没有外键字段也是无法关联的 就会出现报错

3. 被关联表里面的数据无法直接删除和修改关联字段的操作
	解释：被关联表删除了 那关联表中的外键字段指向谁就不知道了 导致数据错乱 

	# 如果想修改或者删除被关联表的字段那么关联表的外键字段也同时进行修改或者删除才合理
	这里就有一个固定的方法：级联更新 级联删除
	关键字：
		on update cascade
		on delete cascade

实例：演示 on updata cascade 与 on delete cascade

# 创建表：必须先创建被关联表
关联表：
create table emp(
    id int primary key auto_increment,
    name varchar(32),
    age int,
    dep_id int comment '部门编号',
    foreign key(dep_id) references dep(id)
    on update cascade
    on delete cascade
);
#  注：on updata cascade与on delete cascade不要加逗号因同属于一条命令

被关联表：
create table dep(
    id int primary key auto_increment,
    name varchar(32)
);


# 存储数据(一定要先存储被关联表数据)
insert into dep(name) values('外交部'),('安保部'),('财务部');

insert into emp(name,age,dep_id) values('jaosn',12,1),('tom',13,2),('kaka',14,3),('huhu',15,2);

存储好信息后我们来验证一下修改和删除被关联表id字段，验证关联表是否跟着改变

修改被关联表id字段：
	update dep set id=20 where id=2;
	
# 如果一个公司部门编号改动的话可以使用到

多对多

以书籍表与作者表为例
    1.先站在书籍表的基础之上
    	问:一本书能否对应多个作者
        答:可以(很多书籍都是由多个作者编写而成得)
    2.在站在作者表的基础之上
    	问:一个作者能否对应多本书
         答:可以
    结论:两个都可以那么表关系就是"多对多"
        这时就需要第三个表：外键字段建在第三张关系表中

实例：

如果只有两个表：

create table book1(
    id int primary key auto_increment,
    title varchar(32),
    author_id int,
    foreign key(author_id) references author1(id) 
    on update cascade 
    on delete cascade
);
create table author1(
    id int primary key auto_increment,
    name varchar(32),
    book_id int,
    foreign key(book_id) references book1(id) 
    on update cascade 
    on delete cascade
);


这样创建外键建立彼此之间的联系的话是不行得，
因为在创建第一个表关联第二个表的时候没有被关联得表二
在创建第二个表关联第一个表的时候没有被关联表一
# 所以这是错误得多对多创建方式

正确的创建方式：引入第三个表

# 创建表

书籍表：
create table book1(
    id int primary key auto_increment,
    title varchar(32)
);

作者表：
create table author1(
    id int primary key auto_increment,
    name varchar(32)
);

关系表：
create table book2author(
	id int primary key auto_increment,
    author_id int,
    foreign key(author_id) references author1(id)
    on update cascade
    on delete cascade,	
    book_id int,
    foreign key(book_id) references book1(id)
    on update cascade
    on delete cascade
);


# 存储数据
 书籍表：
 insert into book1(title) values('python'),('java'),('mysql');
 
 作者表：
 insert into author1(name) values ('tom'),('gary');
 
 关系表:
 insert into book2author(author_id,book_id) values(1,1),(1,2);
 insert into book2author(author_id,book_id) values(2,2),(2,3);

表关系之一对一

引子

我们以qq来举例一个场景：
	我们在录入完扣扣信息的时候，每次显示得只是我们得基本信息数据，比如我们在点击别人得头像看他的信息时，直显示一个扣扣号，个性签名，昵称等基本信息。如果想要查看他的详情信息是不是还要点击右上角得三个点，查看详情等相关按钮才可以看到他的，手机号呀 邮箱 出生年月日等等。

那么扣扣在做的时候就是将这两个数据拆分成两个表 ： 一个用户表 一个用户详情表

那么用户在查看基本信息得时候只给用户表的信息。
如果用户点击查看详情，那么才会把用户得详情表内信息给到客户。

那么这两种表是什么关系呢？
以用户表与用户详情表为例：
	问：一个用户是否可以对应多条用户详情信息？
	答：不可以
	
	问：一个用户详情能否对应多个用户？
	答：不可以
	
我们上述分析过：一个可以一个不可以即为多对一，那么两边都不可以是什么呢？
那么两边都不可以换位思考后，表关系只有两种
	1.没有关系
	2.一对一表关系

那么一对一表关系得外键字段建立在哪里呢？
	理论上一对一外键字段建立在任何一个表里都可以，但是推荐建立在查询频率较高的表中

实例SQL语句：

创建表：
提示 ：外键关键字：foreign key(本表的外键字段) references 被关联表表明(被关联表的id字段)

用户表：
create table user(
    id int primary key auto_increment,
    name varchar(32),
    detail_id int unique,  # 这里记得添加约束条件unique因为这是一对一关系不能出现重复外键
    foreign key(detail_id) references user_detail(id)   # 建立外键
    on update cascade
    on delete cascade
);

用户详情表：(记得先创建这个被关联表)
create table user_detail(
    id int primary key auto_increment,
    addr varchar(32),  # 地址
    phone bigint  # 手机号
);

结论：其实一对一关系表就是添加了一个约束条件unique