foreign key(table_field) references table_name(table_field) 外键关联的另一张表的字段必须唯一

通常一张表的外键是关联另一张表的主键id, 要求外键与主键数据类型一致,并保证外键的值必须是主键中已存在的值

重要！！！在有争议的地方 -_-
能用多对多就尽量不用多对一 (中间表很全能 能表达多对一和一对一的关系),
能用多对一就尽量不用一对一 (一对一也就是在多对一的基础上加个unique)
n:n > n:1> 1:1 能改但是麻烦(查询数据的代码也要跟着改) 要留有余地！！！

内心os:
干脆就不加外键约束嘛 又不影响多表查询啥的...
但加外键确实带来了很多方便,级联删除更新啊 添加时外键约束必须是主键有的值
就是这玩意儿强耦合 表有先后顺序 ╮(╯▽╰)╭

---

如何找出两表的关系？!

思考: emp & dep

先将专门的数据放到专门的表里.

step1: 左表的多条记录能否对应右表的一条记录
        多名员工能否 属于 一个部门？若成立 emp:dep = n:1
step2: 右表的多条记录能否对应左表的一条记录
        多个部门能否 指向 一个员工？若成立 dep:emp = n:1

若只有step1成立, 左表(多的那方)建立外键关系
若只有step2成立, 右表(多的那方)建立外键关系

若step1和step2同时成立, 双向多对一关系就是多对多的关系.需要第三张表.

若step1和step2都不成立,那就是1对1的关系

---

员工信息表

id	name	age	department	comment
1	Egon	18	IT	搞技术
2	tom	19	sale	销售
3	lili	28	sale	销售
4	jack	38	IT	搞技术
5	lxx	78	HR	招聘

[ 分析 ] 若像上方这样设计,将所有的数据放到一张表中,有几大弊端.
        <1> 重复: Egon和jack的部门和描述信息是重复的,占空间会导致查询效率降低.
        <2> 扩展性差: 修改IT部门的描述信息 牵一发而动全身.

---

多对一的关系(一对多)

多对一: 需要两张表来存储信息, 一张表的一行数据关联另一张表的多行数据.
        eg: dep部门表的id与emp员工表的dep_id.
        一个部门关联多个员工(一对多);也可以说多个员工关联一个部门(多对一)

emp员工表

id	name	age	dep_id
1	Egon	18	1
2	tom	19	2
3	lili	28	2
4	jack	38	1
5	lxx	78	3

dep部门表

id	name	comment
1	IT	搞技术
2	sale	销售
3	HR	招聘

注意: 被关联字段必须是唯一的 被关联字段可以是unique key或者primary key

-- 有foreign key 需要先建立被关联表dep表,再创建关联表emp表
create table dep(
  id int primary key auto_increment,
  name varchar(20),
  comment varchar(50)
);

create table emp(
	id int primary key auto_increment,
  name varchar(16),
  age int,
  dep_id int,
  -- foreign key(dep_id) 标识了dep_id为该表的外键
  foreign key(dep_id) references dep(id)
  on delete cascade  -- 级联删除 dep表中的IT部门没了 那么emp表中对应的员工也被删除了
  on update cascade  -- 级联更新 dep表中的IT部门的id变成了111 那么emp表中对应的dep_id也会变成111
);

-- 有foreign key 插入数据时,应该先往dep中插入数据,再往emp中插入数据

insert into dep(name,comment)
values
('IT','搞东西'),
('sale','销售'),
('HR','招聘');

insert into emp(name,age,dep_id)
values
('Egon',18,1),
('tom',19,2),
('lili',28,2),
('jack',38,1),
('lxx',78,3);

-- foreign key的约束 插入dep不存在的id 直接报错
-- insert emp(name,age,dep_id) values('xxx','24',4);

-- 两表的关联字段自动同步更新
update dep set id=333 where name='HR';
-- 级联删除
delete from dep where id=2;
select * from dep;
select * from emp;

-- foreign key也不全是好处,它将两张表在底层强耦合在了一起,建表删表和插入数据会有先后,被关联的dep表优先！
-- dep表但凡有一点风吹草动都会影响与它关联的emp表.(如果主表修改或者删除数据, 从表会跟着一起受影响)
-- 有时候,不会使用foreign key,只是从逻辑层面建立好两表的关系,提高扩展性！

如果表结构已创建好了, 额外想要增加外键：

alter table emp 
add 
constraint fk_emp_dep foreign key(dep_id) references dep(id)
on delete cascade
on update cascade;

删除外键：

alter table emp drop foreign key fk_emp_dep;

---

多对多的关系

多对多: 需要三张表来存储信息，两张单表 + 关系表
        A表的一行数据关联B表的多行数据,B表的一行数据也可以关联A表的多行数据

多个作者出版同一本书 多本书指向同一个作者. ^_^
author表增加外键字段book_id; book表新增外键字段author_id;
那问题来啦, 这双向的多对一 建表谁先谁后呢？矛盾了！需要专门用一张表来放两张表的关联关系！

扩展应用场景:(单表) 用户表 + 相亲关系表 + 用户表

author表

id	name
1	egon
2	jason
3	lxx
4	hxx

book表

id	name
1	九阳神功
2	降龙十八掌
3	易筋经
4	九阴真经
5	葵花宝典

authorTobook表 小知识: 关联表不是说只能有表中的三个字段哦 还可以根据需求定义其它字段哦！

id	author_id	book_id
1	1	1
2	1	2
3	1	3
4	1	4
5	1	5
6	2	1
7	2	2

-- 外键名 看个人习惯诺:fk_当前表名字_被关联表名字 constraint fk_info_depart foreign key...
create table authorTobook(
  id int not null unique auto_increment,
  author_id int not null,
  book_id int not null,
  foreign key(author_id) references author(id)
  on delete cascade
  on update cascade,
  foreign key(book_id) references book(id)
  on delete cascade
  on update cascade,
  primary key(author_id,book_id) -- 中间存放关系的表,对外关联的字段可以联合唯一
);

如果表结构已创建好了, 额外想要增加外键:

alter table authorTobook add constraint fk_authorTobook_author
foreign key(author_id) references author(id)
on delete cascade
on update cascade;

alter table authorTobook add constraint fk_authorTobook_book
foreign key(book_id) references book(id)
on delete cascade
on update cascade,

删除外键:

alter table authorTobook drop foreign key fk_authorTobook_author;
alter table authorTobook drop foreign key fk_authorTobook_book;

---

一对一的关系

student & customer
每一个学生都有可能成为销售的客户(成功被忽悠购买商品).
每成功忽悠一个学生就往客户表添加这个学生的信息.

若student表设置customer_id字段,被关联表customer表(客户表)就应该先创建,先往里面插入数据.不符合我们前面阐述的逻辑. 所以应该在customer表中添加student_id字段.

foreign key本身表达的是多对一的关系.
理论上被标识的外键字段的值是可以重复的！(多对多的情况下不成立)
若给该外键字段unique的约束,那就是一对一的关系啦！比如: id为4和9的学生对应id为1和2的客户.

-- 学生表
create table student(
  id int primary key auto_increment,
  name varchar(20) not null,
	qq varchar(10) not null,
	phone char(16) not null,
);

-- 客户表
create table customer(
	id int primary key auto_increment,
  student_id int unique, -- 该字段一定要是唯一的
  foreign key(student_id) references student(id) -- 一对一的外键字段student_id一定要保证unique
  on delete cascade
  on update cascade
);

其它应用场景：
        一个用户只有一个博客. blog表添加外键字段user_id.
        一个管理员唯一对应一个用户. admin表添加外键字段user_id.

---