实验五：数据完整性实验一、实验目的：掌握使用SQL中的PRIMARY KEY、CHECK、FOREIGN KEY……REFERENCES、NOT NULL、UNIQUE等关键字是现实体完整性、参照完整性及用户定义完整性约束定义。
二、实验步骤：（一）验证分析部分1、利用SQL查询分析器用PRIMARY KEY子句保证实体完整性。
在查询分析器窗体下键入如下命令：CREATE TABLE Student(sno CHAR(5)NOT NULL UNIQUE,sname CHAR(8),ssex CHAR(1),sage INT,sdept CHAR(20)，CONSTRAINT PK_Student PRIMARY KEY(sno));UPDATE Student SET sno=’‘WHERE sdept=’CS’;UPDATE Student SET sno=’200215121‘WHERE sname=’王敏’;运行并观察结果。
2、利用SQL查询分析器用FOREIGN KEY……REFERENCES子句保证实体完整性。
在查询分析器窗体下键入如下命令：(1)先删除原来SC表中关于Sno的外键，再将SC的Sno外键修改为：alter TABLE SC add CONSTRAINT FK_Sno FOREIGN KEY(sno)REFERENCES Student(sno)ON update CASCADE;然后执行以下语句Update Student SET sno=’200215128‘WHERE sno=’200215121’;(2)先删除原来SC表中关于Sno的外键，再将SC的Sno外键修改为：alter TABLE SC add CONSTRAINT FK_Sno FOREIGN KEY(sno)REFERENCES Student(sno)ON delete CASCADE;然后执行以下语句delete from student WHERE sno=’200215122’;运行并观察结果。
3、利用SQL查询分析器用短语NOT NULL、UNIQUE、CHECK保证用户定义完整性。
CREATE TABLE Student(sno CHAR(5),sname CHAR(8)CONSTRAINT U1UNIQUE,ssex CHAR(1),sage INT CONSTRAINT U2CHECK FOR sage<=28,sdept CHAR(20)，CONSTRAINT PK_Student PRIMARY KEY(sno))执行以下SQL语句Insert into values(‘2002101’,’li’,’女’,89,’DS’)运行并观察结果。
思考：如果要求sdept输入内容只能为字母该如何约束？（二）设计分析部分在Student数据库中，完成以下完整性约束设置。
1、设置Student表中Ssex的取值只能是‘男’或‘女’；2、设置Student表中Sdept的取值默认为‘CS’；3、删除操作测试（1）设置Course表和SC表之间的关系，实现cascade delete related records。
（2）删除Course表中记录，查看SC表中数据的变化（SC表中Cno有与Course表中Cno对应的记录删除和无对应记录的删除都要测试），有什么结果，为什么？4.修改操作测试（1）在SC表中，将课程号为3的改为33，都有什么结果？为什么？（2）将Course表和sc表的课程号为3的都改为33，请用完整性约束实现？5.插入数据测试（1）在STUDENT表中插入一条新纪录：95006李三男21IS（2）在SC表中插入一条新纪录：95008380插入执行后都有什么结果？为什么？如何完成上述操作？拓展练习（1）创建表worker（number,name,sex,sage,department），并自定义2个约束U1和U2，其中U1规定name字段唯一，U2规定sage字段的上限是28即年龄最大28岁。
（2）在worker表中插入一条合法记录。
（3）演示插入违反U2约束的例子，U2规定元组的sage属性的值必须<=28。
（4）去除U2约束。
（5）重新插入（3）中想要插入的数据，验证是否插入成功。
三、实验学时2学时四、实验设备与环境Windows2003平台+SQL Server2005系统--建立test数据库create database teston(name=student,filename='d:\db\student.MDF',size=10MB,maxsize=50MB,filegrowth=1MB);use test;--建立student表create table student(Sno char(10)primary key,Sname char(10)unique,Ssex char(2),Sage int,Sdept varchar(10));--建立course表create table course(Cno char(4)primary key,Cname char(20),Cpno char(4),Ccredit int,foreign key(Cpno)references course(Cno));--建立SC表create table SC(Sno char(10),Cno char(4),Grade int,primary key(Sno,Cno),foreign key(Sno)references student(Sno),foreign key(Cno)references course(Cno));--添加student表中数据insertinto student(Sno,Sname,Ssex,Sage,Sdept) Select'200215121','李勇','男',20,'CS' Union Select'200215122','刘晨','女',19,'CS' Union Select'200215123','王敏','女',18,'MA' Union Select'200215125','张立','男',19,'IS'; --添加Course表中数据insertinto course(Cno,Cname,Cpno,Ccredit) values('2','数学',NULL,2);insertinto course(Cno,Cname,Cpno,Ccredit) values('6','数据处理',NULL,2);insertinto course(Cno,Cname,Cpno,Ccredit) Select'4','操作系统','6',3Union Select'7','Pascal语言','6',4Union Select'5','数据结构','7',4Union Select'1','数据库','5',4Union Select'3','信息系统','1',4;--添加SC表中数据insertinto SC(Sno,Cno,Grade)Select'200215121','1',92Union Select'200215121','2',85Union Select'200215121','3',88Union Select'200215122','2',90Union Select'200215122','3',80;--（二）设计分析部分--在Student数据库中，完成以下完整性约束设置。
--1、设置Student表中Ssex的取值只能是‘男’或‘女’；alter table Studentaddconstraint CK_Ssex check(Ssex in('男','女'));insertinto studentvalues('20021512','李里','女',21,NULL);--2、设置Student表中Sdept的取值默认为‘CS’；alter table Studentaddconstraint mydefault default'CS'for Sdept;insertinto student(Sno,Sname,Ssex,Sage)values('200215126','李立','张',21);--3、删除操作测试--（1）设置Course表和SC表之间的关系，实现cascade delete related records。
级联删除相关记录。
alter table SCadd constraint Cno_gk foreign key(Cno)references Course(Cno)on delete cascade on update cascade;--（2）删除Course表中记录，查看SC表中数据的变化:--SC表中Cno有与Course表中Cno对应的记录删除，有什么结果，为什么？deletefrom Coursewhere Cno='2';--SC表中Cno有与Course表中Cno无对应记录的删除，有什么结果，为什么？deletefrom Coursewhere Cno='4';--4.修改操作测试--（1）在SC表中，将课程号为3的改为33，都有什么结果？为什么？update SCset Cno='33'where Cno='3';--结果：UPDATE语句与FOREIGN KEY约束"FK__SC__Cno__0425A276"冲突。