数据库原理及应用实验报告-实验9-用户定义完整性

1. 1. 实验目的

学习用户自定义约束，并实践用户自定义完整性，利用SQL语言用短语NOT NULL、UNIQUE、CHECK保证用户自定义完整性。

1. 1. 实验内容（建议将表改成Teachers,相应属性以T开始命名，如Tname）建立Teacher表。

建立表Teacher,注意跟前面所建立的Teacher区分开来，

1. 1. 1. 1. 创建Worker，Teacher表，并自定义2个约束U1以及U2，其中U1规定Wname Tname字段唯一，U2规定Wage, Tage (级别)字段的上限是28。
         2. 在Worker, Teacher表中插入一条合法记录。
         3. 演示插入违反U2约束的例子，U2规定元组的Wage,Tage属性的值必须<=28。
         4. 去除U2约束。
         5. 重新插入(3)中想要插入的数据，由于去除了U2约束，所以插入成功。
         6. 创建规则Rule_sex，规定插入或更新的值只能是M或F，并绑定到Worker Teacher的Wsex Tsex字段。
         7. 演示违反规则Rule_sex的插入操作。
   2. 实验步骤

以系统管理员或sa帐号登录到SSMS，在新建查询窗口下输入如下命令，运行并观察结果。

(1) 在新建查询窗口中输入如下SQL语句:

USE University_Mis
CREATE TABLE Teacher(
Tno CHAR(5),
Tname CHAR(8) CONSTRAINT U1 UNIQUE,
Tsex CHAR(1),
Tage INT CONSTRAINT U2 CHECK (Tage<=28),
Tdept CHAR(20),
CONSTRAINT PK_Teacher PRIMARY KEY(Tno))

结果如下图1所示，成功建表

 图1建表

(2) 在新建查询窗口中输入如下SQL语句

USE University_Mis
INSERT INTO Teacher (Tno, Tname,Tsex, Tage,Tdept) 
VALUES(‘T01’,’李用’,’M’,14,’后勤部’)
SELECT * FROM Teacher

结果如下图2所示

 图2插入符合要求数据

 (3) 在新建查询窗口中输入如下SQL语句

USE University_Mis
INSERT INTO Teacher (Tno, Tname,Tsex, Tage,Tdept) 
Values(‘T02’,’王勇’,’M’,38,’ 后勤部’)
SELECT * FROM Teacher

结果如下图3所示，插入出错。

 

图3报错

报错消息：

消息 547，级别 16，状态 0，第 1 行

INSERT 语句与 CHECK 约束"U2"冲突。该冲突发生于数据库"Xuni_University_Mis"，表"dbo.Teacher", column 'Tage'。

语句已终止。

图3所示，因为U2约束了Tage<=28，插入数据的Tage=38,不符合约束条件。

(4) 在新建查询窗口中输入如下SQL语句

USE University_Mis

ALTER TABLE teacher DROP U2

结果如图4所示

 

图4 drop约束U2

(5) 在新建查询窗口中输入如下SQL语句 

USE University_Mis
INSERT INTO Teacher (Tno, Tname,Tsex, Tage,Tdept) 
VALUES(‘T02’,’王勇’,’M’,38,’ 后勤部’)
SELECT * FROM Teacher

结果如下图5所示，因为删掉了约束，所以这个元组可以正常插入

 

图5 插入成功

(6) 在新建查询窗口中输入如下SQL语句 

USE University_Mis
Go
CREATE RULE Rule_sex AS @Value IN (‘F’,’M’)
Go
EXEC SP_bindrule Rule_sex, ‘Teacher.[Tsex]’;

结果如下图6所示

 图6 规则绑定

(7) 在新建查询窗口中输入如下SQL语句

USE University_Mis
INSERT INTO Teacher VALUES(‘T03’,’黄号’,’1’,’25’,’ 后勤部’)

结果如下图7所示

 图7 插入错误

报错，因为Tsex的值必须为F,M

消息 513，级别 16，状态 0，第 1 行

列的插入或更新与先前的 CREATE RULE 语句所指定的规则发生冲突。该语句已终止。冲突发生于数据库 'Xuni_University_Mis'，表 'dbo.Teacher'，列 'Tsex'。

语句已终止。

1. 1. 实验要求

(1) 实验之前请细细阅读实验总体要求与说明指导书

(2 ) 在SQL Server 2005或2008或2014的SSMS环境中，完成以上实验(1)-(7)步所有SQL数据定义操作，并将其中奇数步骤的操作窗口剪贴到实验报告中。

9.5实验体会

1）实验反思

  无

2）实验收获

学习了用户定义完整性的相关内容，加深了对于这部分知识点的认识

附录：

无