mysql外键使用记录

Navicat为mysql表设置外键的时候，删除时和更新时两列有四个值可以选择:

• CASCADE：父表delete、update的时候，子表会delete、update掉关联记录；
• SET NULL：父表delete、update的时候，子表会将关联记录的外键字段所在列设为null，所以注意在设计子表时外键不能设为not null；
• RESTRICT：如果想要删除父表的记录时，而在子表中有关联该父表的记录，则不允许删除父表中的记录；
• NO ACTION：同 RESTRICT，也是首先先检查外键；如果子表中有匹配的记录,则不允许对父表对应候选键进行update/delete操作.

下面的示例测试，针对CASCADE方式。

需求

现在需要存储学校和学生信息，建立学校表school，学生表student. 字段如下

表名	字段	类型
school	school_id	varchar
	school_name	varchar
student	stu_id	int
	stu_name	varchar
	school_id	varchar

需要达到的效果：

• 1、school_id字段约束:
  student表字段school_id值，在school表中必须存在
• 2、更新:
  school表中school_id更新时，student表中school字段也要更新
• 3、删除:
  school表中删除某个school_id时，student表中该school_id下的学生数据都删除

建表语句

• school

CREATE TABLE `school` (
  `school_id` varchar(255) NOT NULL,
  `school_name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`school_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

• student
  这里为student表创建一个外键，关联school表school_id字段.

CREATE TABLE `student` (
  `stu_id` int(10) NOT NULL AUTO_INCREMENT,
  `stu_name` varchar(255) DEFAULT NULL,
  `school_id` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`stu_id`),
  KEY `school_id` (`school_id`),
  CONSTRAINT `student_ibfk_1` FOREIGN KEY (`school_id`) REFERENCES `school` (`school_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8

测试

需求效果1: school_id字段约束

• 插入school表数据

INSERT INTO `school`(`school_id`, `school_name`) VALUES ('xiwang_xiaoxue', '希望小学');

• 插入student表数据

INSERT INTO `student`(`stu_id`, `stu_name`, `school_id`) VALUES (1, '小明', 'test_xiaoxue');

报错，因为值为test_xiaoxue的school_id在school中不存在，达到需求中效果1要求。

1452 - Cannot add or update a child row: a foreign key constraint fails (`szw`.`student`, CONSTRAINT `student_ibfk_1` FOREIGN KEY (`school_id`) REFERENCES `school` (`school_id`) ON DELETE CASCADE ON UPDATE CASCADE)

需求效果2: 更新

*student表插入两条数据

INSERT INTO `student`(`stu_id`, `stu_name`, `school_id`) VALUES (1, '小明', 'xiwang_xiaoxue');
INSERT INTO `student`(`stu_id`, `stu_name`, `school_id`) VALUES (2, '小华', 'xiwang_xiaoxue');

+----------+------------+-------------------+
|   stu_id | stu_name   | school_id         |
|----------+------------+-------------------|
|        1 | 小明       | xiwang_xiaoxue |
|        2 | 小华       | xiwang_xiaoxue |
+----------+------------+-------------------+

• school表中school_id='xiwang_xiaoxue'，改为yangguang_xiaoxue

UPDATE school SET school_id='yangguang_xiaoxue' where school_id='xiwang_xiaoxue';

• 查看student表, school_id字段已经随着school更新而更新，效果2达成。

+----------+------------+-------------------+
|   stu_id | stu_name   | school_id         |
|----------+------------+-------------------|
|        1 | 小明       | yangguang_xiaoxue |
|        2 | 小华       | yangguang_xiaoxue |
+----------+------------+-------------------+

需求效果3: 删除

• 删除school表中，school_id='yangguang_xiaoxue'的数据

DELETE FROM school WHERE school_id='yangguang_xiaoxue';

• 查询student表，表中数据已随之删除，达成效果三

> SELECT * from student;
+----------+------------+-------------+
| stu_id   | stu_name   | school_id   |
|----------+------------+-------------|
+----------+------------+-------------+
0 rows in set

总结

以上是外键的简单应用示例。
删除表时注意：
此时，直接删除school表会报错误。需要先删除student表，才能删除school表

1217 - Cannot delete or update a parent row: a foreign key constraint fails