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

数据库完整性

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

一、题目

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;