mysql数据库基本操作（六）
外键约束
创建外键
前面讲的表单查询都是一张表，但项目中表与表之间是有关联的，比如我们创建的学生表，他们可能在不同班级，不同班级有不同的班主任，他们之间的关系大概是这样的：每一个班主任会对应多个学生 , 而每个学生只能对应一个班主任。所以创建一张班主任的表，这张表应该是主表，学生的那张表是子表，需要添加班主任信息：
CREATE TABLE Classhost( id TINYINT PRIMARY KEY auto_increment, name VARCHAR (20), age INT , is_marriged boolean );
INSERT INTO Classhost (name,age,is_marriged) VALUES ("丽丽",22,0), ("莉莉",24,0), ("李丽",22,0), ("李莉",20,0); CREATE TABLE student( id INT PRIMARY KEY auto_increment, name VARCHAR (20), host_id TINYINT )ENGINE=INNODB; INSERT INTO Student(name,host_id) VALUES ("pengfy",2), ("pyq",4), ("xiaojiang",1), ("pyq2",3), ("pengfy2",1), ("pyq3",3), ("lily",2);
创建好了两张表如下:
现在假设丽丽辞职了，我们试一下删除名字叫丽丽的班主任：
delete from classhost where name="丽丽";
还真的删掉了，那现在看看学生表有没有什么变化：
发现xiaojaing和pengfy2的班主任id还是1，这显然是不合适的，一个班主任走了，学生信息也要变化，要么分到别的班，要么一起走（特别喜欢这个班主任），大家也可是试一下在删除班主任后，在学生表里面插入一条学生信息，host_id设为1，也是OK的。这两种情况显示不合理，那么这个时候就要引入外键这个概念了，现在给学生的表加上一个外键，首先要找到两张表关联的地方，就是学生表里面host_id和老师表里面的id是关联的，那我们新建一下学生表2：
CREATE TABLE student2( id INT PRIMARY KEY auto_increment, name VARCHAR (20), host_id TINYINT, --切记:作为外键一定要和关联主键的数据类型保持一致
FOREIGN KEY(host_id) REFERENCES classhost(id) )ENGINE=INNODB;
INSERT INTO Student2(name,host_id) VALUES ("pengfy",2),
("pyq",4),
("xiaojiang",4),
("pyq2",3),
("pengfy2",4),
("pyq3",3),
("lily",2);
这里新的学生信息也要改一下，把id为1的要改成其他的，不然会报错。现在再试一下，删掉id为2的班主任：
delete from classhost where id=2;
报错了，不让删除了，因为外键约束了，报错信息如下：
Cannot delete or update a parent row: a foreign key constraint fails (`test`.`student2`, CONSTRAINT `student2_ibfk_1` FOREIGN KEY (`host_id`) REFERENCES `classhost` (`id`))
再试一下给id=1的老师加一个学生，看是否能添加成功：
insert into student2(name,host_id) values ('lucy',1); 报错信息： Cannot add or update a child row: a foreign key constraint fails (`test`.`student2`, CONSTRAINT `student2_ibfk_1` FOREIGN KEY (`host_id`) REFERENCES `classhost` (`id`))
同样报错了 ，看一下报错信息里面有个东西，CONSTRAINT `student2_ibfk_1`这个东西我没有写过，后面的FOREIGN KEY (`host_id`) REFERENCES `classhost` (`id`))才是我写的，那这个是什么呢?这个是外键名称，你不自定义的时候，会默认给你加上一个，所以给一个表增加一个外键完整的写法是这样的：
ALTER TABLE student ADD CONSTRAINT anyname FOREIGN KEY(host_id) REFERENCES classhost(id);
试着给我们学生表1增加一个外键：
Cannot add or update a child row: a foreign key constraint fails (`test`.`#sql-1110_2`, CONSTRAINT `anyname` FOREIGN KEY (`host_id`) REFERENCES `classhost` (`id`))
也报错了，这是为什么呢？因为老师里面已经没有id=1的了，而学生表还存在，看来要修改一下学生表才行：
第三个和第五个改成4好了，
update student set host_id=4 where id=3 or id =5;
再试一下增加外键，看一下表结构：
添加成功，那么能增加就能删除嘛，试一下删除这个外键：
ALTER TABLE student DROP FOREIGN KEY anyname;
查看是否删除成功：
已经找不到外键了，删除成功喽。那现在的问题就是主表怎么正常删除数据，并且把子表的数据也一并删除。这就涉及下面要讲的INNODB支持的ON语句。
INNODB支持的ON语句
外键约束对子表的含义: 如果在父表中找不到候选键,则不允许在子表上进行insert/update
外键约束对父表的含义: 在父表上进行update/delete以更新或删除在子表中有一条或多条对应匹配行的候选键时,父表的行为取决于：在定义子表的外键时指定的on update/on delete子句
innodb支持的四种方式
1.cascade方式
在父表上update/delete记录时，同步update/delete掉子表的匹配记录，外键的级联删除：如果父表中的记录被删除，则子表中对应的记录自动被删除，比如刚才在删除班主任的同事，也把学生删除，就应该在主键中加上一段ON DELETE CASCADE：
FOREIGN KEY (charger_id) REFERENCES ClassCharger(id) ON DELETE CASCADE
我们再给student加上这么一个外键：
ALTER TABLE student ADD CONSTRAINT anyname FOREIGN KEY(host_id) REFERENCES classhost(id) ON DELETE CASCADE;
同时我们也要把student2这张表删除，因为主表也和这个表绑定了，外键没改到时候删除主表还是会报错的，删掉之后，试一下删除classhost表里面的一个内容：
成功了，没有报错啊，看看子表里面，原先我们的子表id=1和id=7都是host_id=2的：
现在子表里面绑定了host_id=2的信息也都一起删除了，除了这种，还有以下三种情况。
2.set null方式
在父表上update/delete记录时，将子表上匹配记录的列设为null，要注意子表的外键列不能为not null
FOREIGN KEY (charger_id) REFERENCES ClassCharger(id) ON DELETE SET NULL
有兴趣可以试一下这种方式，就不演示了。
3.Restrict方式
拒绝对父表进行删除更新操作，这个很少用，相当于不让sql报错，了解一下就行了。
4.No action方式
在mysql中同Restrict,如果子表中有匹配的记录,则不允许对父表对应候选键进行update/delete操作，这个也作一个了解就行。
