mysql foreign key(即外键)的作用是为了保证数据的一致性。具体看下测试效果:
1、创建一个表TStudent,记录学生的信息;
mysql> create table TStudent (ID int not null auto_increment,name varchar(20),addr varchar(20),primary key(ID));
Query OK, 0 rows affected (0.09 sec)
mysql> desc TStudent;
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| ID | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(20) | YES | | NULL | |
| addr | varchar(20) | YES | | NULL | |
+-------+-------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
2、再建一表TGrade,记录学生成绩,该表ID列为外键,关联TStudent 的ID列;
mysql> create table TGrade (GID int null auto_increment,Chinese float(5,2),English float(5,2),ID int ,<span style="color:#ff0000;">foreign key(ID) references TStudent(ID)</span>,primary key(GID));
Query OK, 0 rows affected (0.08 sec)
mysql> desc TGrade;
+---------+------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------+------------+------+-----+---------+----------------+
| GID | int(11) | NO | PRI | NULL | auto_increment |
| Chinese | float(5,2) | YES | | NULL | |
| English | float(5,2) | YES | | NULL | |
| ID | int(11) | YES | MUL | NULL | |
+---------+------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
3、往表TGrade先添加数据。
mysql> insert into TStudent (name ,addr) values('zhangsan','sichuang'),('lisi','hunan'),('wangwu','zhejiang');
Query OK, 3 rows affected (0.05 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select * from TStudent;
+----+----------+----------+
| ID | name | addr |
+----+----------+----------+
| 1 | zhangsan | sichuang |
| 2 | lisi | hunan |
| 3 | wangwu | zhejiang |
+----+----------+----------+
3 rows in set (0.00 sec)
4、尝试向TGrade表中添加数据,数据中的ID(5)在TStudent中不存在时,报错。
mysql> insert into TGrade (Chinese,English,ID) values(95,90,5);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`mydb1`.`TGrade`, CONSTRAINT `TGrade_ibfk_1` FOREIGN KEY (`ID`) REFERENCES `TStudent` (`ID`))
5、尝试向TGrade表中添加数据,数据中的ID(1)在TStudent中存在时,可成功添加。
mysql> insert into TGrade (Chinese,English,ID) values(95,90,1);
Query OK, 1 row affected (0.04 sec)
mysql> select * from TGrade;
+-----+---------+---------+------+
| GID | Chinese | English | ID |
+-----+---------+---------+------+
| 2 | 95.00 | 90.00 | 1 |
+-----+---------+---------+------+
1 row in set (0.00 sec)
6、尝试 删除TStudent中的数据,该数据的ID被TGrade使用时,删除失败,报错。
mysql> delete from TStudent where name='zhangsan';
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`mydb1`.`TGrade`, CONSTRAINT `TGrade_ibfk_1` FOREIGN KEY (`ID`) REFERENCES `TStudent` (`ID`))
7、尝试更新TStudent中的ID,该ID被TGrade使用时,更新失败,报错。
mysql> update TStudent set ID=10 where name='zhangsan';
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`mydb1`.`TGrade`, CONSTRAINT `TGrade_ibfk_1` FOREIGN KEY (`ID`) REFERENCES `TStudent` (`ID`))
8、如果先删除TGrade中的ID,再删除TSudent中该ID的记录,可删除成功。
mysql> select * from TGrade;
+-----+---------+---------+------+
| GID | Chinese | English | ID |
+-----+---------+---------+------+
| 2 | 95.00 | 90.00 | 1 |
+-----+---------+---------+------+
1 row in set (0.00 sec)
mysql> delete from TGrade where ID=1;
Query OK, 1 row affected (0.04 sec)
mysql> delete from TStudent where name='zhangsan';
Query OK, 1 row affected (0.05 sec)
mysql> select * from TStudent;
+----+--------+----------+
| ID | name | addr |
+----+--------+----------+
| 2 | lisi | hunan |
| 3 | wangwu | zhejiang |
+----+--------+----------+
2 rows in set (0.00 sec)
9、给TGrade ID列增加 on delete cascade on update cascade 参数;
1)先去掉foreign属性
mysql> alter table TGrade drop foreign key TGrade_ibfk_1;
Query OK, 0 rows affected (0.20 sec)
Records: 0 Duplicates: 0 Warnings: 0
2)再加
on
delete
cascade
on
update
cascade属性
mysql> alter table TGrade add foreign key(ID) references TStudent(ID) on delete cascade on update cascade;
Query OK, 0 rows affected (0.24 sec)
Records: 0 Duplicates: 0 Warnings: 0
3)往TGrade中加入数据用来做删除测试。
mysql> insert into TGrade (Chinese ,English,ID) values(90,93,2),(91,93,3);
Query OK, 2 rows affected (0.03 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select * from TGrade;
+-----+---------+---------+------+
| GID | Chinese | English | ID |
+-----+---------+---------+------+
| 4 | 90.00 | 93.00 | 2 |
| 5 | 91.00 | 93.00 | 3 |
+-----+---------+---------+------+
2 rows in set (0.00 sec)
4)从TStudent ID中删除ID为2的数据,发现TGrade表中ID为2为的记录也删除了。
mysql> delete from TStudent where name='lisi';
Query OK, 1 row affected (0.04 sec)
mysql> select * from TGrade;
+-----+---------+---------+------+
| GID | Chinese | English | ID |
+-----+---------+---------+------+
| 5 | 91.00 | 93.00 | 3 |
+-----+---------+---------+------+
1 row in set (0.00 sec)
5)
从TStudent ID中更新ID为2的数据为12,发现TGrade表中ID也跟着变化了。
mysql> update TStudent set ID=12 where name='wangwu';
Query OK, 1 row affected (0.04 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from TGrade;
+-----+---------+---------+------+
| GID | Chinese | English | ID |
+-----+---------+---------+------+
| 5 | 91.00 | 93.00 | 12 |
+-----+---------+---------+------+
1 row in set (0.00 sec)