zabbix 表关系 表与表的关联关系

摘要：在设计表时需要考虑表和表之间的一些关系，表与表之间的关系分为三种，分别为一对一、一对多（多对一）、多对多。下面用例子介绍了这三种关系，还重点介绍了几种不同情况下对外键的创建、删除以及查看的操作，并进行了代码实现。

一、一对一

一对一：一张表（a表）的一条记录对应另一张表（b表）的一条记录，反过来，另一张表（b表）的一条记录只能对应该表（a表）的一条记录。

1、简介

例如：

• 人的信息和身份证信息，一个人只有一个身份证号，一个身份证号只能被一个人使用。

建表原则：

• 主表的主键和从表的外键形成主外键关系，外键必须唯一，也就是说从表的外键必须是主键。

但实际应用不多，因为这种情况完全可以建在一张表里。

编号	姓名	身份证外键
1	李四	1
2	王五	2

身份证编号	身份证号
1	12345
2	54321

像这样就根本没有必要，可以合成一张表

编号	姓名	身份证
1	李四	12345
2	王五	54321

二、一对多(多对一)

一对多（多对一）：一张表中（a表）的一条记录对应另一张表（b表）的多条记录，但反过来，另一张表（b表）的一条记录只能对应该表（a表）的一条记录。

1、简介

例如：

• 分类和商品：一个分类对应多个商品，一个商品只能属于一个分类。
• 部门和员工：一个部门可以有多个员工，一个员工只能属于一个部门。
• 班级和学生：一个班级可以有多个学生，一个学生只能属于一个班级。

建表原则：

• 在多的一方创建一个字段作为外键指向一的一方的主键，也就是说多的一方是从表，一的一方是主表。
• 建表时，一般先建主表，再建从表。举个例子：先建部门表，再建员工表，员工表中有一个部门编号(外键)指向部门的主键。

学生编号	学生姓名	所属班级
1	张三	1班
2	李四	2班
3	王五	1班
4	赵六	2班

由于学生会越来越多，保存的数据也越来越多，所属班级的重复率也会越来越高，可以分为两张表：班级表和学生表
班级表（主表）

班级编号	班级名称
1	1班
2	2班

学生表（从表）

学生编号	学生姓名	班级编号(外键)
1	张三	1
2	李四	2
3	王五	1
4	赵六	2

学生表中有一列专门存储的班级表的主键值,那么这一列对于学生表来说叫做外键列，包含外键的表叫做外键表，外键对应的表的主键所在的表我们叫做主键表。这里使用数字代替了原来的汉字，因此存储空间也能节约不少。

2、代码实现

• 准备工作：

1. 创建班级表（主表）

-- 创建主表
create table class(
	cno int primary key auto_increment,
	cname varchar(32)
);

2. 创建学生表（从表）

-- 创建从表
create table student(
	sno int primary key auto_increment,
	sname varchar(32),
	s_cno int not null
);

3. 在学生表（从表）的s_cno（外键字段）添加外键约束，依赖于班级表（主表）的cno（主键）

-- 在从表上的一个字段(外键字段)添加外键约束，依赖于主表的主键(班级编号)
alter table student add constraint fk_class_student foreign key(s_cno) references class(cno);

• 验证外键约束

1. 向班级表（主表）中添加数据

-- 向主表添加数据
insert into class values(null, '1班'),(null, '2班');

2. 向学生表（从表）中添加数据

-- 向从表添加数据
insert into student values(null, '张三', 1),(null, '李四', 2);

3. 向学生表（从表）中添加班级表（主表）不存在的班级号（被依赖的字段）

-- 向从表中添加主表不存在的班级号 会报错，因为违反了外键约束
insert into student values(null, '王五', 3),(null, '赵六', 4);

由于学生表添加的学生所在的班级在班级表中找不到，因此无法添加。

解决方案1：添加学生信息时，修改预添加学生的班级号

解决方案2：添加学生信息前，班级表中添加需要的班级号

解决方案3：删除外键约束

4. 删除班级表（主表）中指定的记录（学生表在使用的班级号）

-- 删除主键中已存在的班级 会报错
delete from class where cno = 2;

因为该班级正被学生表的学生引用，因此不能删除。

解决方案1：删除该班级前，删除引用该班级的学生

解决方案2：删除外键约束

• 解除外键约束

1. 查询外键名

方法一：在表的创建信息中可以找到外键名；

-- 查询外键
show create table student;

方法二：在报错信息中也能找到外键名；

方法三：在mysql客户端中该表的设计表的外键选项卡中可以找到外键名

方法四：通过INFORMATION_SCHEMA.KEY_COLUMN_USAGE.CONSTRAINT_NAME字段找到外键名

SELECT     C.TABLE_SCHEMA            拥有者,
           C.REFERENCED_TABLE_NAME  父表名称 ,
           C.REFERENCED_COLUMN_NAME 父表字段 ,
           C.TABLE_NAME             子表名称,
           C.COLUMN_NAME            子表字段,
           C.CONSTRAINT_NAME        约束名,
           T.TABLE_COMMENT          表注释,
           R.UPDATE_RULE            约束更新规则,
           R.DELETE_RULE            约束删除规则
      FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE C
      JOIN INFORMATION_SCHEMA. TABLES T
        ON T.TABLE_NAME = C.TABLE_NAME
      JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS R
        ON R.TABLE_NAME = C.TABLE_NAME
       AND R.CONSTRAINT_NAME = C.CONSTRAINT_NAME
       AND R.REFERENCED_TABLE_NAME = C.REFERENCED_TABLE_NAME
      WHERE C.REFERENCED_TABLE_NAME IS NOT NULL ;

2. 删除外键约束

alter table student drop foreign key fk_class_student;

三、多对多

多对多：一张表（a表）的一条记录能够对应另一张表（b表）的多条记录，同时，另一张表（b表）的一条记录也能对应该表（a表）的多条记录。

1、简介

例如：

• 老师和学生：一个老师能教多个学生，一个学生也能被多个老师教。
• 人和身份：一个人可以有多个身份，一个身份也能被多人使用。

建表规则：

• 需要创建第三张表，中间表（外键表）中至少两个字段，这两个字段分别作为外键指向各自一方的主键。

学生表：

学生编号	学生名称
1	张三
2	李四
3	王五

身份表：

身份编号	身份名称
1	学生
2	课代表
3	班长

中间表：

学生编号(外键)	身份编号(外键)
1	1
1	2
1	3
2	1
2	2
3	1

含义：张三既是学生也是课代表还是个班长；李四既是学生又是课代表；王五只有一个学生身份。

2、代码实现

• 准备工作：

1. 创建学生表（主表）

-- 创建学生表(主表）
-- 指定约束名创建主键
create table student(
	sno int auto_increment,
	sname varchar(32),
	constraint pk_student_sno primary key(sno)
);

2. 创建身份表（主表）

-- 创建身份表(主表)
create table identity(
	ino int auto_increment,
	iname varchar(32),
	constraint pk_identity_ino primary key(ino)
);

3. 创建中间表（从表），并添加外键约束

-- 创建中间表(从表)，并添加外键约束
-- 指定约束名创建外键
create table mid_stu_ide(
	m_sno int not null,
	m_ino int not null,
	constraint fk_student_mid foreign key(m_sno) references student(sno),
	constraint fk_identity_mid foreign key(m_ino) references identity(ino)
);

4. 创建中间表的主键（可以省略）

-- 为中间表创建联合主键(可以省略这步，创建表时忘了，表外创建一波）
alter table mid_stu_ide add constraint pk_mid_msno_mino primary key(m_sno,m_ino);

• 验证外键约束

1. 向学生表中添加数据

-- 向学生表中添加数据
insert into student(sname) values('张三'),('李四'),('王五');

2. 向角色表中添加数据

-- 向角色表中添加数据
insert into identity(iname) values('学生'),('课代表'),('班长');

3. 向中间表中添加数据

-- 向中间表中添加数据
insert into mid_stu_ide values(1,1),(1,2),(1,3),(2,1),(2,2),(3,1);

4. 查看学生和角色的对应关系

-- 查看学生和职务的对应关系
select sname,iname
from student s, identity i, mid_stu_ide m
where s.sno = m.m_sno and i.ino = m.m_ino
order by sname;

5. 删除中间表的数据（取消张三的课代表职务）

-- 删除中间表的数据
-- 取消张三的课代表身份
delete from mid_stu_ide where m_sno = 1 and m_ino=2;

6. 向中间表添加不存在学生或身份的数据，由于外键约束，会报错。

-- 向中间表添加不存在的学生或身份的数据
insert into mid_stu_ide values (100,100);

因为中间表在插入时，会去学生表和职务表中找对应主键，但学生表和职务表中找不到对应的数据，因此会报错。

7. 删除学生表或角色表中的数据，由于外键约束，会报错。

-- 删除学生表或角色表中的数据
delete from student where sno=1;

因为中间表还保存着这主键表的信息，所以不能删除。

• 解除外键约束

-- 解除外键约束
alter table mid_stu_ide drop foreign key fk_student_mid;
alter table mid_stu_ide drop foreign key fk_identity_mid;