MySql:多表设计---外键

来源：互联网发布：人工智能发展的看法编辑：程序博客网时间：2024/06/05 20:34

在实际开发过程中，一张表显然是不能满足我们的需求，通常我们需要多张表来存储数据。那么下面就举个栗子~

一家公司有多个部门，而每个部门有多个员工，现在我们来新建一个部门表：dept 以及一个员工表：emp。

建表的操作这里不再演示，如有需要请参考：

《MySql:操作表的语句以及常用的字段类型》http://blog.csdn.net/javy_codercoder/article/details/49099271

以下为dept表的结构：

+-------+-------------+------+-----+---------+----------------+

+-------+-------------+------+-----+---------+----------------+

| id | int(11) | NO | PRI | NULL | auto_increment |

+-------+-------------+------+-----+---------+----------------+

以下为emp表的结构：

+--------+-------------+------+-----+---------+----------------+

+--------+-------------+------+-----+---------+----------------+

| id | int(11) | NO | PRI | NULL | auto_increment |

+--------+-------------+------+-----+---------+----------------+

接下来就插入数据，插入数据也不再多说，有需要的可以看：

《MySql:增删改查(CRUD)以及乱码编码解决（1）》http://blog.csdn.net/javy_codercoder/article/details/49121459

以下为dept表的数据：

+----+--------+

| id | name |

+----+--------+

| 1 | 人事部 |

| 2 | 后勤部 |

| 3 | 财务部 |

| 4 | 行政部 |

+----+--------+

假设有两位员工分别叫做：小明和小芳分别属于人事部和后勤部。

有一天老板发现后勤部没什么用然后就把后勤部砍掉了，对于数据库来说就是dept表把id为2的这一行数据删掉。

到了发工资的日期，小芳来到财务部领工资，那么财务部的妹子在电脑上发现没有后勤部啊。这个时候小芳就蒙蔽了~~我人还在部门就没了~~~

显然这种多表设计是可行的，但是是不完善的，不严谨的。

为了能够无时无刻维持着两个表的关系，我们需要引入一种叫做外键约束的技术。

我们在创建表时候可以声明表和表之间的关系，命令数据库帮我们维持这种关系。

如果有外键约束的话就不会发生以上案例的事情。在表dept删除id为2的数据时候会发生错误，因为在emp表还有一个小芳对应着dept表中id为2的数据。

显然上面的emp表是要重新建表了，因为在开始建表的时候没有加入外键约束，

那么我们来看看emp表的正确建表语句是怎样的：

create table emp(

id int primary key auto_increment,

name varchar(20),

dept_id int,

salary double,

foreign key(dept_id ) references dept (id)

);

新建的emp表：

+---------+-------------+------+-----+---------+----------------+

+---------+-------------+------+-----+---------+----------------+

| id | int(11) | NO | PRI | NULL | auto_increment |

| dept_id | int(11) | YES | MUL | NULL | |

+---------+-------------+------+-----+---------+----------------+

接下来我们插入两条数据：

insert into emp values(null,'曹操',1,2000);

insert into emp values(null,'荀彧',2,2000);

现在表里面的数据是这样的：

+----+------+---------+--------+

+----+------+---------+--------+

| 1 | 曹操 | 1 | 2000 |

| 2 | 荀彧 | 2 | 2000 |

+----+------+---------+--------+

我们依旧要删除掉二号部门：

delete from dept where id=2;

然后就会发现抛出一下错误：

ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constrai

nt fails (`mydb`.`emp`, CONSTRAINT `emp_ibfk_1` FOREIGN KEY (`dept_id`) REFERENC

ES `dept` (`id`))

显然不然我们删除。这就是外键约束，能够避免人还在部门就没了的事情，那么外键约束能否避免无中生有的事情发生呢？

也就是我们尝试插入一个隶属于5号部门的员工到emp表中，注意这个时候dept表只有四个部门而已。

我们试试：

insert into emp values(null,'郭嘉',5,2000);

会发现也会抛出错误：

ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint f

ails (`mydb`.`emp`, CONSTRAINT `emp_ibfk_1` FOREIGN KEY (`dept_id`) REFERENCES `

dept` (`id`))

要删除2号部门必须要确保emp表没有属于2号部门的员工，要插入属于5号部门的员工必须确保dept表有5号部门。

多表设计：

要懂得多表设计就要先明白什么是多对一，多对多，一对一这三种关系。而且要清楚什么情况是多对一，多对多，一对一。

这里就不再叙述，可以百度一下~

多对一：

在多对一的情况下则在“多”的一方那个表参照”一“的那个表，也就是说在”多“的那个表设计一个外键关联”一“的那个表。

一对一：

而一对一的这种情况，是随意的~那个表参照哪个表都是没有所谓的。

多对多：

多对多这种情况就比较复杂，显然无论哪一方保存另一方的id都不合适，一个比较经典的案例就是老师和学生之间的关系，要保存哪个老师教过哪些学生怎么保存呢？一个学生可能被多个老师教过，而一个老师也不可能只教一个学生，那么对于这种情况怎么解决呢？

这个时候就需要新建多一张表出来，用于分别保存学生和老师的id作为外键：

id stu_id teach_id

1 1 6

2 2 3

3 3 2

4 3 1

5 3 3

6 6 1

如上表所示，如果需要查询3号学生被哪些老师教过~那么就查stu_id=3的~就能查出1，2，3这三位老师都教过3号学生

如果想查1号老师教过哪些学生那么只需查teach_id=1的数据就能查出3，6学生被1号老师教过~

0 0