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操作,这个也作一个了解就行。

 

posted @ 2019-05-27 18:17  彭方炎QAQ  阅读(367)  评论(0编辑  收藏  举报