简单小例子验证mysql中外键的作用

创建测试表1

mysql> create table subject(
    ->  id INT NOT NULL AUTO_INCREMENT,
    ->  subjectname varchar(20) not null,
    ->  PRIMARY KEY (id)
    -> );
Query OK, 0 rows affected (0.02 sec)

插入两条数据

mysql> insert into subject(subjectname) values ('yuwen');
Query OK, 1 row affected (0.01 sec)

mysql> insert into subject(subjectname) values ('shuxue');
Query OK, 1 row affected (0.00 sec)

mysql> select * from subject;
+----+-------------+
| id | subjectname |
+----+-------------+
|  1 | yuwen       |
|  2 | shuxue      |
+----+-------------+
2 rows in set (0.00 sec)

创建测试表2

mysql> create table student(
    ->  id INT NOT NULL AUTO_INCREMENT,
    ->  subjectid int not null,
    ->  name varchar(20) not null,
    ->  PRIMARY KEY (id),
    ->  CONSTRAINT FK_student
    ->     FOREIGN KEY (subjectid)
    ->     REFERENCES subject (id)
    ->     ON DELETE NO ACTION
    ->     ON UPDATE NO ACTION
    -> );
Query OK, 0 rows affected (0.01 sec)

student表中subjectid字段为外键，关联subject表中的id。
插入第一条数据，成功。

mysql> insert into student(subjectid,name) values(1,'zhangsan');
Query OK, 1 row affected (0.01 sec)

插入第二条数据

mysql> insert into student(subjectid,name) values(3,'zhangsan3');
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`fktest`.`student`, CONSTRAINT `FK_student` FOREIGN KEY (`subjectid`) REFERENCES `subject` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION)

上述语句失败，原因是设置主键后，subjectid为3的记录在subject表中不存在。

删除表1数据

删除表1中id为1的数据

mysql> delete from subject where id=1;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`fktest`.`student`, CONSTRAINT `FK_student` FOREIGN KEY (`subjectid`) REFERENCES `subject` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION)

上述语句执行失败，原因是假如你把这条语句删除了，那么表2中的subjectid为1的那条记录不完整了，成了一个残缺的记录，这就是外键的作用。

正确的删除顺序是先将表2中所有subjectid为1的都删除之后，再去删除从表中的记录。

总结

外键的作用，就是保证两个表外键关联后，主表中任意一条数据的完整性，这就是约束。
你不能任性的插入一条外键值并不存在的数据，因为mysql在从表中找不到对应的信息给主表做信息补充。