数据完整性和触发器

钻仰弥坚

已于 2023-08-05 11:30:59 修改

阅读量2.2k

点赞数 10

分类专栏：数据库实验文章标签：数据库

于 2022-12-25 10:05:43 首次发布

本文链接：https://blog.csdn.net/capture3333/article/details/128433306

版权

数据库实验专栏收录该内容

9 篇文章 24 订阅

订阅专栏

一、实验目的

1、熟悉和掌握SQL SERVER 的实体完整性、参照完整性及用户定义完整性。

2、熟悉和掌握使用PRIMARY KEY、CHECK、FOREIGN KEY……REFERENCES、NOT NULL、UNIQUE等关键词验证SQL SERVER 的实体完整性、参照完整性及用户定义完整性。

3、熟悉触发器的定义和使用

二、实验内容

1、利用SQL查询分析器用PRIMARY KEY子句保证实体完整性

运行后插入、更改数据并观察结果(SNO为空，重复值)；

2、利用SQL查询分析器用FOREIGN KEY……REFERENCES子句保证参照完整性

对SC表运行插入、更改数据并观察结果，对STU表运行删除、更改数据并观察结果；

3、利用SQL查询分析器用短语NOT NULL、UNIQUE、CHECK保证用户定义完整性。

运行后插入、更改数据并观察结果；

4、系（系编号，系名称）

教工（教工号，姓名，性别，职称，工资，系编号）

学生（学号，姓名，性别，出生年月，专业，家庭地址，系编号）

课程（课程编号，课程名称，学时，系编号）

成绩（学号，课程编号，分数）

在上面5张表里面操作。

（1）在课程表里插入（insert into 课程 values('c108','自动化',106,106)

（2）把教工表的教工号2302修改为 2203

（3）成绩表插入values('991029','c106',67)

（4）删除s表中学号992124数据

（5）命令实现以下完整性约束操作：限定教工表中教师的工资必须大于4000。

（6）命令实现以下完整性约束操作：学生表设置性别的默认值“男”，必须为男或女，姓名唯一。

运行后插入、更改数据并观察结果.

5、触发器。

触发器可以看成是一类特殊的存储过程，在满足某个特定条件时自动触发执行，是提高数据库服务器性能的有力工具。触发器分为三类，更新触发器、插入触发器和删除触发器。能够定义触发器的用户有：表的所有者；系统管理员；拥有创建触发器的权限，且拥有对操作对象的相应的操作权限的用户。

（1）创建学生表上的触发器，实现删除学生表中元组时自动删除成绩表中引用该元组的相关数据。

1）首先查看学生表中的“管理关系”的设置，确保关系FK_学生_成绩对“INSERT”和“update”不发生作用

2）测试删除学生表中的 “991022”

3） CREATE TRIGGER del_stu ON学生 FOR DELETE

AS DELETE学生 FROM 学生 a,deleted b

WHERE a.学号=b.学号

4）在企业管理器中查看触发器，展开syy数据库，单击“表”，选择“学生” →“所有任务” →“管理触发器”命令，弹出“触发器属性”对话框，在名称下拉框选择del_stu，显示该触发器的定义，进而可以修改触发器的定义和删除触发器。

5）验证触发器del_stu的有效性，执行

三、实验平台

操作系统：Windows x64

DBMS：SQL Server 2016

四：程序清单、调试和测试结果及分析

程序清单：

1、利用SQL查询分析器用PRIMARY KEY子句保证实体完整性

运行后插入、更改数据并观察结果(SNO为空，重复值)；

CREATE TABLE STU

(SNO CHAR(5),

SNAME CHAR(8),

SSEX CHAR(2),

SAGE INT,

SDEPT CHAR(20),

CONSTRAINT  PK_Student  PRIMARY KEY(SNO))

/*设置主键只需要primary key(属性名)，使用constraint 前缀，是为了给约束加个名字，

方便后续管理（删除）。*/

插入：

insert into STU values('99102','田平平','女',18,'计算机')

insert into STU values('99212 ','郭黎明','男',19,'计算机')

insert into STU values('99402 ','何明慧','女',20,'计算机')

insert into STU values('99122 ','姜明明','男',17,'计算机')

insert into STU values('99301 ','何漓江','男',21,'软件工程')

insert into STU values('99210 ','康纪平','女',23,'软件工程')

insert into STU values('99412 ','康嘉家','男',24,'软件工程')

insert into STU values('99113 ','包立琪','女',23,'通信工程')

insert into STU values('99411 ','王海洋','男',19,'通信工程')

insert into STU values('99135 ','王立平','女',20,'网络工程')

更改数据（空值和重复值)：

UPDATE STU SET Sno=' ' WHERE sdept='计算机'; /*空值*/（失败,实体完整性缺失）

UPDATE STU SET Sno='99411' WHERE  sname='何漓江'; /*重复值*/（失败,实体完整性缺失）

注：primary key的意思是主键约束,包含唯一约束和非空约束。

UPDATE STU SET Sno='99415' WHERE sdept='计算机';/*重复值*/（失败,实体完整性缺失）

UPDATE STU SET Sno='99418' WHERE sname='何漓江'; （成功）

2、利用SQL查询分析器用FOREIGN KEY……REFERENCES子句保证参照完整性

对SC表运行插入、更改数据并观察结果，对STU表运行删除、更改数据并观察结果；

CREATE TABLE SC

(SNO CHAR(5) CONSTRAINT FK_SC FOREIGN KEY

REFERENCES STU(SNO),

CNO CHAR(5) UNIQUE NOT NULL,/*课程名,非空,不可重复*/

GRADE INT)

插入：

INSERT INTO SC values('99212','S101',90)

INSERT INTO SC values('99212','S102',91)

INSERT INTO SC values('99212','S103',75)

INSERT INTO SC values('99212','S104',76)

INSERT INTO SC values('99411','S105',86)

INSERT INTO SC values('99411','S106',89)

INSERT INTO SC values('99411','S107',98)

INSERT INTO SC values('99411','S108',98)

INSERT INTO SC values('99418','S109',88)

INSERT INTO SC values('99418','S110',84)（成功）

更改SC：

UPDATE SC SET GRADE='' WHERE CNO='S104'; /*空值*/（成功）

UPDATE SC SET CNO='S103' WHERE  GRADE='86'; /*重复值*/（失败，违反用户定义的完整性，列值唯一UNIQUE）

UPDATE SC SET CNO=NULL WHERE GRADE='84';/*空值*/（失败，违反NOT NULL约束）

注意：如果写CNO=’’是可以插入的。

UPDATE SC SET GRADE='99' WHERE CNO='S104';（成功）

UPDATE SC SET CNO='S111' WHERE GRADE='89';（成功）

更改STU：

UPDATE STU SET SNO='99419' WHERE SNAME='何漓江';/*学号在SC表上*/

（失败，违反用户定义的完整性，列值唯一UNIQUE）

UPDATE STU SET SNO='99210' WHERE SDEPT='通信工程';/*学号不在SC表上*/

（失败，违反实体完整性）

删除STU：

DELETE FROM STU WHERE SNO='99212'/*学号在SC表上*/（失败，违反参照完整性）

DELETE FROM STU WHERE SNO='99102'/*学号不在SC表上*/（成功）

3、利用SQL查询分析器用短语NOT NULL、UNIQUE、CHECK保证用户定义完整性。

运行后插入、更改数据并观察结果；

CREATE TABLE ST (SNO  CHAR(5),

                 SNAME CHAR(8) ,

                 SSEX CHAR(2) ,

SAGE  INT  CONSTRAINT U2  CHECK (SAGE<=28),

SDEPT  CHAR(20),

CONSTRAINT  PK_Student1  PRIMARY KEY(SNO))

插入：

INSERT  INTO  ST (SNO,SSEX,SDEPT,SAGE)

VALUES ('95020','男','1S',38);（失败，违反CHECK约束）

INSERT  INTO  ST VALUES ('95820','微微','男',25,'2S');

INSERT  INTO  ST VALUES ('95420','微微','男',8,'2S');

INSERT  INTO  ST VALUES ('95090','微微','男',18,'3S');

INSERT  INTO  ST VALUES ('95022','微微','男',20,'3S');（成功）

更改：

UPDATE ST SET SAGE=35 WHERE SNO='95820';/*SAGE大于28*/（失败，违反CHECK约束）

UPDATE ST SET SAGE=23 WHERE SNO='95820';/*SAGE不大于28*/（成功）

4、系（系编号，系名称）

教工（教工号，姓名，性别，职称，工资，系编号）

学生（学号，姓名，性别，出生年月，专业，家庭地址，系编号）

课程（课程编号，课程名称，学时，系编号）

成绩（学号，课程编号，分数）

在上面5张表里面操作。

这些表在实验二，三已经建立并插入数据，为节省篇幅代码不再写。

（1）在课程表里插入

insert into 课程 values('c108','自动化',106,106)

（失败，出现INSERT 语句与 FOREIGN KEY 约束"FK_SC"冲突错误。原因：插入的时外键值的数据（从表数据）不存在，这里是系编号没有106，违反参照完整性。）

(2)把教工表的教工号2302修改为 2203

UPDATE 教工 SET 教工号='2203' WHERE 教工号='2302';

（失败，出现违反了 PRIMARY KEY 约束“PK__教工__2863DAAF95B79E2F”错误。因为主键值是不允许重复的。教工表（如下）已经有老师的教工号是2203了，这样会违反实体完整性。）

(3)成绩表插入values('991029','c106',67)

INSERT INTO 成绩 VALUES('991029','c106',67)

（失败，出现INSERT 语句与 FOREIGN KEY 约束"FK__成绩__学号__2E1BDC42"冲突错误。原因：插入的时外键值的数据（从表数据）不存在，学号没有991029（学生表见下方），违反参照完整性。）

(4)删除学生表中学号992124数据

看具体操作，这是可以成功的。

DELETE FROM 学生 WHERE 学号='992124'

原因：失败，出现DELETE 语句与 REFERENCE 约束"FK__成绩__学号__2E1BDC42"冲突。我们可以设置外键约束，设置级联删除。

可以利用SQL SERVER配置的级联删除来删除代码：

ALTER TABLE 成绩

ADD CONSTRAINT FK__成绩__学生

FOREIGN KEY(学号)

REFERENCES 学生(学号)

ON DELETE CASCADE

再删除：

DELETE FROM 学生 WHERE 学号='992124'

这次成功。

（5）命令实现以下完整性约束操作：限定教工表中教师的工资必须大于4000。

注：必须让表中的教师工资都要大于4000才能成功。否则会出现ALTER TABLE 语句与 CHECK 约束"YS2"冲突，原因就是表里面有不符合约束的数据。

update 教工 set 工资=工资+2000

ALTER TABLE 教工

ADD CONSTRAINT YS CHECK(工资>4000)

/*插入数据*/

insert into 教工 values('2609','葛小','女','教授',8880.00,101)/*工资大于4000*/

insert into 教工 values('2299','李长','男','副教授',3290.00,102)/*工资小于4000*/

（失败，违反CHECK约束）

insert into 教工 values('8995','姜立','男','副教授',null,104)/*工资为空*/

/*更改数据*/

UPDATE 教工 SET 工资='3555.99' WHERE 姓名='姜立伟';/*工资小于4000*/

（失败，违反CHECK约束）

UPDATE 教工 SET 性别=null WHERE 教工号='2109';/*工资为空*/

UPDATE 教工 SET 工资='9999.99' WHERE 姓名='葛小';/*工资大于4000*/

（6）命令实现以下完整性约束操作：学生表设置性别的默认值“男”，必须为男或女，姓名唯一。

ALTER TABLE 学生

ADD CONSTRAINT SN1 UNIQUE(姓名)

ALTER TABLE 学生

ADD CONSTRAINT SN2 DEFAULT '男' FOR 性别;

ALTER TABLE 学生

ADD CONSTRAINT SN3 CHECK(性别 IN('男','女'))

运行后插入、更改数据并观察结果.

/*插入数据*/

insert into 学生 values('995575 ','黄海','男','10/05/1979',103)/*重名*/

insert into 学生 values('978899 ','丽丽','女','03/04/1981',102)/*可插入数据*/

insert into 学生 values('997777 ','康嘉','','07/05/1980',104)/*性别为空*/

insert into 学生 values('992323 ','包立','无','03/14/1981',101)/*性别不是男不女*/

重名的：违反unique约束

性别为空：违反用户自定义约束CHECK

性别不是男和女：违反用户自定义约束CHECK

注：要使用default值必须在表的后面指定一下要加添加的列。

insert into 学生(学号,姓名,出生年月,系编号)

values('997777 ','康嘉','07/05/1980',104)

/*更改数据*/

DELETE FROM 学生 WHERE 学号='998899'/*可删除数据*/

DELETE FROM 学生 WHERE 学号='978899'/*可删除数据*/

UPDATE 学生 SET 性别='无' WHERE 学号='998889';/*性别不是男不女*/（违反CHECK约束）

UPDATE 学生 SET 性别='无' WHERE 学号='998889';/*性别为空*/（违反CHECK约束）

UPDATE 学生 SET 姓名='S12_MMM' WHERE 学号='998889';/*重名*/（违反UNIQUE约束）

5、触发器。

（1）创建学生表上的触发器，实现删除学生表中元组时自动删除成绩表中引用该元组的相关数据。

1）首先查看学生表中的“管理关系”的设置，确保关系FK_学生_成绩(这里有问题，应该是FK__成绩__学号)对“INSERT”和“update”不发生作用。

用代码查看查看学生表中的“管理关系”的设置

EXEC sp_helpconstraint 学生

可以直接删除外键FK__成绩__学号__2E1BDC42（看自己的编号）以及之前添加的级联删除：

ALTER TABLE 成绩

DROP CONSTRAINT FK__成绩__学号__2E1BDC42

ALTER TABLE 成绩

DROP CONSTRAINT FK__成绩__学生

删除即可

2）测试删除学生表中的 “991022”

DELETE FROM 学生 WHERE 学号='991022'（无法级联删除）

3）创建触发器

CREATE TRIGGER del_stu ON 学生 FOR DELETE

AS DELETE 成绩 FROM 学生,成绩

WHERE 成绩.学号 IN(SELECT 学号 FROM DELETED)

5）验证触发器del_stu的有效性，执行

DELETE FROM 学生 WHERE 学号='991022'

SELECT * FROM 学生

SELECT * FROM 成绩（成功级联删除）

常见错误

1.常见错误：违反了 PRIMARY KEY 约束"PK_Student".不能在对象"dbo.STU"中插入重复键.原因：主码不能重复。

2.常见错误：INSERT 语句与 FOREIGN KEY 约束"FK_SC"冲突。该冲突发生于数据库"syy"，表"dbo.STU", column 'SNO'。

原因：插入的时外键值的数据（从表数据）不存在。

3.常见错误：违反了 UNIQUE KEY 约束“UQ__SC__C1FF677A943BB0AE”。不能在对象“dbo.SC”中插入重复键。违反了列值唯一。

4.常见错误：UPDATE 语句与 REFERENCE 约束"FK_SC"冲突。该冲突发生于数据库"syy"，表"dbo.SC", column 'SNO'。是因为存在约束关系FK_SC。

5.常见错误：INSERT 语句与 CHECK 约束"U2"冲突。该冲突发生于数据库"syy"，表"dbo.ST", column 'SAGE'。

6.常见错误：DELETE 语句与 REFERENCE 约束"FK__成绩__学号__2E1BDC42"冲突。原因：外键约束限制了，先要删除外键约束。

7.常见错误：将截断字符串或二进制数据。原因就是添加的限制和数据库中已经存储的数据有了冲突。例如，限制用户ID只能是数字，但是数据库中用户ID里面有数字意外的符号，此时就会报出这种错误。解决办法就是，在数据库中将所有在限制之外的数据全部修改成符合限制的数据，或者调整限制即可。

8.常见错误：ALTER TABLE 语句与 CHECK 约束"YS2"冲突。原因就是表里面有不符合约束的数据。

9.常见错误：从数据类型 varchar 转换为 numeric 时出错。insert的时候存进去的''不会变成null，只是空值，空值和null是不一样的。

钻仰弥坚

关注

10
点赞
踩
19

收藏

觉得还不错? 一键收藏
打赏
0
评论
复制链接

分享到 QQ

分享到新浪微博

扫一扫

专栏目录