数据库—9(表数据操作的SQL语句，外键约束)

外键
      如果一张表中有一个非主键的字段指向了另一张表中的主键，就将该字段叫做外键，一张表中外键可以有多个，不同字段指向了不同表中的主键。存在外键的表成为从表，被指向主键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的人员.

 

外键作用，保留数据的一致性和完整性.

• 从表插入新行，其外键值不是主表的主键值便阻止插入；
• 从表修改外键值，新值不是主表的主键值便阻止修改；
• 主表删除行，其主键值在从表里存在便阻止删除(要想删除，必须先删除从表的相关行)；
• 主表修改主键值，旧值在从表里存在便阻止修改(要想修改，必须先删除从表的相关行)。