外键
如果一张表中有一个非主键的字段指向了另一张表中的主键,就将该字段叫做外键,一张表中外键可以有多个,不同字段指向了不同表中的主键。存在外键的表成为从表,被指向主键ID的表成为主播。
为了演示外键,创建一个学生分数表studentscore 的从表.
mysql> CREATE table studentscore(id int unsigned primary key auto_increment,course varchar(20),score char(255),studentID int unsigned);
并使该学生分数表的studentID 作为外键,指向personinfo 表的主键ID
alter table studentscore add foreign key(studentID) references personinfo(id);
首先看下personinfo 人员表(主表)所有人的信息
mysql> select * from personinfo;
+----+-----------+------+--------+--------+--------+--------------+--------------------+--------+
| id | name | age | gender | height | city | addr | personId | proabb |
+----+-----------+------+--------+--------+--------+--------------+--------------------+--------+
| 1 | xiaozhang | 20 | 男 | 172.00 | 成都 | 成都高新 | 510131198605136412 | 川 |
| 2 | xiaozhang | 20 | 男 | 172.00 | 成都 | 成都高新 | 510131198605136413 | 川 |
| 3 | xiaowang | 22 | 男 | 172.00 | 成都 | 成都高新 | 510131198605136414 | 川 |
| 4 | xiaowang | 30 | 男 | 170.00 | 成都 | 成都高新 | 510131198605136415 | 川 |
| 5 | xiaozhao | 25 | 女 | 165.00 | 重庆 | 重庆高新 | 510131198605136416 | 渝 |
| 6 | xiaozhao | 26 | 女 | 165.00 | 重庆 | 重庆高新 | 510131198605136417 | 渝 |
| 7 | xiaoxu | 28 | 男 | 155.00 | 贵阳 | 贵阳南城 | 510131199204105870 | 贵 |
| 9 | xiaozhou | 29 | 女 | 160.00 | 桂林 | 桂林北城 | 510131199204105815 | 云 |
+----+-----------+------+--------+--------+--------+--------------+--------------------+--------+
现在给studentscore 添加一条分数信息,对应的人员是personinfo 表中ID为1
mysql> insert into studentscore(course,score,studentID) value('语文',97,1);
mysql> insert into studentscore(course,score,studentID) values('数学',90,1),('英语','95',1);
1. 若删除 personinfo主表中ID为1 的人员信息
mysql> delete from personinfo where id=1;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`personInfo`.`studentscore`, CONSTRAINT `studentscore_ibfk_1` FOREIGN KEY (`studentID`) REFERENCES `personinfo` (`id`))
删除失败:原因是从表studentscore 学生分数表中,存在在studentID 为1 的学生分数,所以无法删除。必须首先将从表studentscore 学生分数表 中studentID 为1 的 数据全部删除掉,才能删除主表personinfo主表中ID为1 的人员信息。
2. 若在从表studentscore 学生分数表 添加 studentID 为10 的分数信息
mysql> insert into studentscore(course,score,studentID) value('语文',100,10);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`personInfo`.`studentscore`, CONSTRAINT `studentscore_ibfk_1` FOREIGN KEY (`studentID`) REFERENCES `personinfo` (`id`))
删除失败:因为主表personinfo中 并不存在 ID为10的人员.
外键作用,保留数据的一致性和完整性.
- 从表插入新行,其外键值不是主表的主键值便阻止插入;
- 从表修改外键值,新值不是主表的主键值便阻止修改;
- 主表删除行,其主键值在从表里存在便阻止删除(要想删除,必须先删除从表的相关行);
- 主表修改主键值,旧值在从表里存在便阻止修改(要想修改,必须先删除从表的相关行)。