完整性实验(new)

1、数据完整性实验(SQL SERVER版)实验目的：加深对数据完整性的理解。实验内容：数据库的完整性设置。实验步骤：可视化界面的操作方法：一、实体完整性1将student表的“sno”字段设为主键：在表设计界面中，单击左边的行选定块，选定“sno”字段，单击工具按钮 设置主键。如图6-1所示：图6-12 将“sc”表的“sno”和“cno”设置为主键：在表设计界面中，单击并拖动左边的行选定块，选定sno和cno字段，单击工具按钮 设置主键。如图6-2所示：图6-2二、域完整性3. 将“ssex”字段设置为只能取“男”，“女”两值：在表设计界面，点击图43箭头所指按钮，出现属性(Property)

2、对话框，选择新建（New）按钮，然后在约束表达式（Constraint expression）框中输入“ ssex in ('男','女') ”。如图63所示。图63三、参照完整性4. 将“student”表和“sc”表中的“sno”字段设为参照: 打开“sc”表的设计界面, 点击工具栏按钮 , 在弹出的属性(properties)对话框中点击“新建”按钮，在“主键表(Primary key table)”下拉框中选择“student”表，在其下的字段选择框中选择“sno”，在“外键表(Foreign key table)”下拉框中选择“sc”表，在其下的字段选

3、择框中选择“sno”，单击关闭即可。见图6.4。命令方式操作方法：一、实体完整性1将“student”表的“sno”字段设为主键：当“student”表已存在则执行：alter table student add constraint pk_sno primary key (sno)当“student”表不存在则执行: Create table student(sno CHAR(5) primary key , sname CHAR(10),ssex CHAR(2),sage int,sdept CHAR(4)注:可用命令“drop table student”删除“student”表2. 添

4、加一身份证号字段,设置其惟一性.(注: 操作前应删除表中的所有记录)Alter table student add id char(18) unique (id)3. 将“sc”表的“sno”和“cno”设置为主键：当“sc”表已存在则执行：alter table sc add constraint PK_SnoCno primary key (sno,cno)当“sc”表不存在则执行：Create table sc(sno CHAR(5),cno CHAR(2),grade INT NULL,constraint PK_SnoCno primary key (sno,cno)二、域完整性4.

5、 将“ssex”字段设置为只能取“男”，“女”两值：当“student”表已存在则执行：alter table student add constraint CK_Sex check (ssex in ('男' ,'女')当“student”表不存在则执行：Create table student(sno CHAR(5) primary key , sname CHAR(10),ssex CHAR(2) check (ssex in ('男' ,'女') ,sage int, sdept CHAR(4)5. 设置学号字段只能输入数

6、字:alter table student add constraint CK_Sno_Format check (sno like '0-90-90-90-90-9')6. 设置身份证号的输入格式:alter table student add constraint CK_ID_Format check (id like '0-90-90-90-90-90-91-20-90-90-90-10-90-30-90-90-90-9_') OR (id like '0-90-90-90-90-90-90-90-90-10-90-30-90-90-90-9

7、9;)7. 设置18位身份证号的第7位到第10位为合法的年份(1900-2050)alter table student add constraint CK_ID_Format2 check ( not len(id)=18 or ( (convert(smallint,substring(id,7,4) )>=1900) and(convert(smallint,substring(id,7,4) )<=2050) )三、参照完整性9. 设置男生的年龄必须大于22, 女生的年龄必须大于20.Alter table student add constraint CK_age che

8、ck (sex='男' and sage>=22 or sex='女' and sage>=20 )10. 将“student”表和“sc”表中的“sno”字段设为参照:当“sc”表已存在则执行：alter table sc add constraint FP_sno foreign key (sno) references student(sno )当“sc”表不存在则执行：Create table sc(sno CHAR(5) constraint FP_sno foreign key references student(sno),cno CH

9、AR(2),grade INT NULL,constraint PK_SnoCno primary key (sno,cno) )四、完整性验证1 实体完整性： 在“student”表数据浏览可视化界面中输入学号相同的两条记录将会出现错误如下图所示：或者在命令窗口输入下面两条命令也会出现错误提示：insert into student values('95001','张三','男',20,'CS')insert into student values('95001','李四','女'

10、,18,'CS')下面的语句用来验证“sc”表中的实体完整性：insert into sc values('95002', '10',65)insert into sc values('95002', '10',90) 2. 域完整性:使用下面的语句验证“ssex”字段的域完整性：insert into student values('95009','张匀','大',20,'CS')3参照完整性：使用下面的语句“验证”sc表中的“sno”字段的域完整性

11、(假设student表中没有学号为“95998”的学生记录)：insert into sc values('98998', '10',98)思考: 1建立课程的实体完整性, 和课程号cno的参照完整性;2建立年龄的域完整性, 约束条件为“年龄在15到30岁之间”3*. 在学生表中添加“出生日期”和“身份证号”字段，设置一完整性规则，确保身份证号中的关于出生日期的数字与“出生日期”字段的值相匹配。CREATE TRIGGER Insert_Sal ON Teacher instead of INSERT /*触发事件是插入或更新操作*/ AS BEGIN /*定义触发动作体，是PL/SQL过程块*/declare job char(10),sal intset job=(select job from inserted)set sal=(select sal from insert