Mysql-外键关联-外键与表的关系(一对多, 一对一,多对多)

多表的设计

一对多

在多方表中,创建一个新的字段,作为当前表的外键,指向一方表的主键
注意点: 一对多, 外键设置在多方

一张表关联多张表,通过 foreign key (字段名) references 关联的表名(字段)

模拟一对多表

一对多表的结构设计,两张表
主表(一方) 从表(多方)
建表原则,在多方表中添加字段,把该字段作为外键指向一方表的主键

一个部门有许多员工,所以外键设置在多方 ,所以是员工表
部门表
create table department(
id int primary key auto_increment,
name varchar(20) not null
);


员工表
create table emp(
eid int primary key auto_increment,
ename varchar(20),
dno int, # 添加一个字段,把该字段设置为外键关联
foreign key (dno) references department(id)
);

添加数据:
往表中添加时,
主表必须要有数据,从表才能添加数据
并且从表关联的外键值, 主表必须要有对应的
否则添加不上,删除同理

外键默认的约束

创建外键关联时,可以添加下面两行代码,指定约束级联,为空等
表示删除,更新后怎么处理
比如:删除从表的数据,主表同意否?
如果是 casccade 是级联 表示不同意你删除数据

  on update cascade
  on delete  cascade 

create table emp(
eid int primary key auto_increment,
ename varchar(20),
dno int, # 添加一个字段,把该字段设置为外键关联
foreign key (dno) references department(id),
on update cascade
on delete  cascade 
);

---

一对一

例如: 一个人在一个部门,没来之前不存在,来了之后,就是员工
而这个部门对应的这个人是唯一的,不可能有两个一模一样的人
这就是一对一,一个人对应一个部门
例如: 一张身份证对应一个人,
一定要保证外键的字段是唯一的 unique

create table card(
id int primary key auto_increment,
phone char(18) not null,
name char(20) not null
);

create table people(
id int primary key auto_increment,
name varchar(20) not null,
c_id int unique, # 该字段一定要唯一
foreign key(c_id) references card(id) # 外键字段一定要保证unique

);

多对多

中间要添加数据时,要看两张主表有没有数据

先创建一张中间表,中间表中至少包含2个字段
2个字段作为当前中间表的外键,指向原来多对多表的主键

多对多:用户与角色, 一个用户拥有多个角色,一个角色可以被多个用户所拥有

建立三张表
首先 建立 用户表 角色表
在建立中间表

用户表:
create table user(
id int primary key auto_increment,
name varchar(20)
);


角色表
create table role(
id int primary key auto_increment,
name varchar(20)
);

建立第三方表来处理多对多的关联
# 创建两个外键关联,分别关联两个不同的表
create table user_role(
id int primary key auto_increment,
user_id int,
role_id int,
foreign key(user_id) references user(id),
foreign key(role_id) references role(id)
);