MySql:多表设计---外键

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

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

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

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

以下为dept表的结构：

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

| Field | Type             | Null | Key | Default | Extra                  |

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

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

| name| varchar(20) | NO   |        | NULL     |                            |

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

以下为emp表的结构：

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

| Field  | Type           | Null | Key | Default | Extra                   |

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

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

| dept  | varchar(20) | YES  |       | NULL    |                             |

| name | varchar(20) | YES  |      | NULL    |                             |

| salary | double       | YES  |       | NULL    |                             |

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

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

《MySql:增删改查(CRUD)以及乱码编码解决（1）》 http://blog.youkuaiyun.com/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表：

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

| Field   | Type        | Null | Key | Default | Extra          |

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

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

| name    | varchar(20) | YES  |     | NULL    |                |

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

| salary  | double      | YES  |     | NULL    |                |

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

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

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

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

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

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

| id | name | dept_id | salary |

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

|  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号老师教过~