数据库系统之实体完整性约束

数据库完整性

今天做了一道数据库的上机题,其中的一些知识觉得挺有用的,因此打算整理出来一篇博客来与大家分享一下。这里的语句针对的都是创建表后所需要的语句。

一、题目

1.练习定义三类完整性:
(1)为student表添加主码约束
(2)为course表添加主码和外码约束
(3)为sc表添加主码和外码约束
(4)为course表添加cname取值唯一的约束
(5)为course表添加credit默认值为4的default约束
(6)为student表添加check约束,ssex取值只能为男或女。
2.验证上面添加的约束
(1) 将student表中学号为201910122的学号修改为201215123,验证主码约束机制。
(2) 将course表中课程号为6的先行课号修改为9,验证外码约束机制。
(3) 向sc表中添加一条记录,(’666666666’,’1’,80),验证外码约束机制。
(4) 修改student表中学号为201215122的学生记录,将学号修改为‘11111’验证外码约束机制。
(5) 删除student表中学号为201215122的学生记录,验证外码约束机制。
(6) 将course表中课程号为2号的课程名称修改为数据库,验证唯一约束机制。
3.删除上面添加的约束
(1)删除student表的主码约束
(2)删除course表的外码约束
(3)删除course表中cname取值唯一的约束

二、题目准备

此次上机需要的各个数据库各个字段的数据类型为:

1.student表

2.course表

3.sc表

此次上机需要的各个数据库数据为:

1.student表

2.course表

3.sc表

三、题目分析

1.练习定义三类完整性:

(1)为student表添加主码约束

添加主码约束的MySQL语句是:alter table 表名 add primary key(列名);

因为主码必须需要唯一并且数据不能重复,发现在student表中只有sno(学号)是每个人唯一标识。

因此这里只需输入这个语句即可:alter table student add primary key(sno);

最后检验是否成功,输入desc student;

(2)为course表添加主码和外码约束

1.添加主码约束

根据第一步我们可以知道cno(课程号)是唯一标识

因此添加主码约束的语句是:alter table course add primary key(cno);

最后检验是否成功,输入desc course;

2.添加外码约束

添加外码约束的MySQL语句是:alter table 表名 add constraint 约束名 foreign key(关联字段) references 主表(关联字段)

其中constraint 约束名可以省略,即也可以写成:alter table 表名 add foreign key(关联字段) references 主表(关联字段)

首先先尝试一下使用course表的cpno来参照sc表的cpno:

alter table course add foreign key(cpno) references sc(cpno);

,发现报错

这是因为在参照时候,参照的和被参照的都需要是同一种数据类型和长度,比如tinyint类型的只能参照tinyint类型的,不能参照char(9)类型的。同样的,数据长度不一样也不能进行参照,char(9)类型的也不能参照char(4)的

因此我们把cpno的数据类型更改一下再试一次

先更改cpno字段的数据类型:alter table sc modify cpno tinyint;

然后再次输入上面的语句。

成功了

(3)为sc表添加主码和外码约束

1.添加主码约束

根据第一步我们可以知道cpno是唯一标识

因此添加主码约束的语句是:alter table sc primary key(cpno);

最后检验是否成功,输入desc sc;

2.添加外码约束

使用sc表的sno参照student的sno:alter table sc add foreign key(sno) references student(sno);

(4)为course表添加cname取值唯一的约束

添加取值唯一约束的MySQL的语句是:alter table 表名 add unique(列名);

因此我们输入:alter table course add unique(cname);

使用desc course;验证一下

其中PRI表示主键

UNI表示取值唯一约束

MUL表示外键

(5)为course表添加credit默认值为4的default约束

添加default约束的MySQL语句是:alter table 表名 alter 列名 set default '默认值';

因此输入:alter table course alter credit set default '4';

最后检验是否成功,输入desc course;

(6)为student表添加check约束,ssex取值只能为男或女。

添加check约束的MySQL语句是:alter table 表名 add check(约束条件);

因此这条语句是:alter table student add check(ssex="男" or ssex="女");

2.验证上面添加的约束

(1) 将student表中学号为201910122的学号修改为201910123,验证主码约束机制。

修改元组的MySQL语句为:update 表名 set 列名=表达式 where 条件;

实现语句为:update student set sno='201910123' where sno='201910122';

此处表达的意思是因为有外码约束,所以不能修改。

但是如果没有这个外码约束,它也修改不了。

我们知道,主码的每个元组都必须不一样,我们先看下我们的student表

其中sno是我们的主键,其中已经有‘201910123‘这个数据,因此根据主码约束我们也修改不了

(2) 将course表中课程号为3的先行课号修改为4,验证外码约束机制。

根据第一步可以知道,实现语句为:update course set cno=4 where cno=3;

此处是因为cno是course的主键,所以此处报的是主键错误

没有验证的原因是因为我们在设置外码的时候吗,没有让别的外键关联这个表的cno

如果想要实现的话,我们可以再添加一条关联

语句为:alter table sc add foreign key(cno) references course(cno);

然后再验证就会报因为外码约束而错误了。

(3) 向sc表中添加一条记录,(’666666666’,’1’,8,80),验证外码约束机制。

向表中追加元组的MySQL语句是:insert into 表名 values(值,值,···);

实现语句是:insert into sc values('666666666','1',8,80);

这个错误意思是关联强制约束造成的错误,即因为外码约束机制引起的错误。

(4) 修改student表中学号为201910122的学生记录,将学号修改为‘11111’验证外码约束机制。

实现语句为:update student set sno='11111' where sno='201910122';

此处也成功验证了外码约束机制。

(5) 删除student表中学号为201910122的学生记录,验证外码约束机制。

删除元组的MySQL语句是:delete from 表名 where 条件;

因此实现语句是:delete from student where sno='201910122';

成功验证外码约束机制。

(6) 将course表中课程号为2号的课程名称修改为数据库,验证唯一约束机制。

实现语句是:update course set cname="数据库" where cno=2;

此处我们首先查看一下course表的内容:

此处我们发现课程号为1的课程名已经是数据库了,由唯一约束机制可知,cname的数据必须是唯一的,所以报错。

成功验证唯一约束机制。

3.删除上面添加的约束

(1)删除student表的主码约束

首先删除主码约束的MySQL语句是:alter table 表名 drop primary key;

我们先输入一下

此时我们发现一个问题,语句并没有错,但是不能删除。

我们看一下错误内容:不能删除主键:被某个外键所需要

因此我们也就知道了,要是我们想要删除这个主键,必须先让那个外键与这个主键的关联断开才行。

通过查看前面的语句,我们发现,这个外键是由sc表的sno与student表的sno关联

删除外码约束的MySQL语句是:alter ![](https://img2020.cnblogs.com/blog/2309068/202110/2309068-20211015114119542-185235895.png) table 表名 drop foreign key 外码名;

那么这个外码名怎么知道呢,我们之前在创建的时候并没有给它起别名,那么这个外码名就是sno吗?

不,并不是。

我们来查看一下,输入show create table sc;

此时我们就能发现外码名并不是sno,而是系统自动生成的一个名:sc_ibfk_1

因此删除此处实现删除外码的语句是:alter table sc drop foreign key sc_ibfk_1;

删除成功,此时我们再试一下删除student的主码,再次输入:alter table student drop primary key;

student表的主码删除成功。

(2)删除course表的外码约束

根据第一步可知,我们首先应该查找course表的外码名,

输入语句:show create table course;

可以发现外码名为course_ibfk_1

因此实现语句为:alter table course drop foreign key course_ibfk_1;

删除course表的外码成功。

(3)删除course表中cname取值唯一的约束

删除取值唯一约束的MySQL语句是:alter table 表名 drop key 约束名;

输入:show create table course;

可以发现约束名为cname

实现语句为:alter table course drop key cname;

删除成功。

四、参考链接

1.alter table添加表约束 - 流星一箭 - 博客园 (cnblogs.com)

2.MYSQL添加约束的两种方法_keep-CSDN博客_mysql添加约束

posted @ 2021-10-15 13:43  lyl-star  阅读(647)  评论(3编辑  收藏  举报