实验十 数据库完整性实验

一、实验目的
1、熟悉通过SQL对数据进行完整性控制。熟练掌握数据库三类完整性约束（实体完整性、用户自定义完整性、参照完整性）
2、了解SQL SERVER 的违反完整性处理措施。
3、了解主键（PRIMARY KEY）约束、外键（FOREIGN KEY）约束、唯一性（UNIQUE）约束、检查（CHECK）约束、DEFAULT 约束、允许空值约束。

二、实验内容及要求
用SQL语句完成下列功能。使用SQL对数据进行完整性控制（三类完整性、CHECK短语、CONSTRAIN字句、触发器）。用实验证实，当操作违反了完整性约束条件时，系统是如何处理的。
（一）、认真学习三类完整性、CHECK短语、CONSTRAINT字句的使用，完成下列内容。
Table1：
列名 中文解释 数据类型 空值 说明
Class_id 班级编号 Varchar(6) 否 主键
Class_name 班级名称 Varchar(20) 否
Director 班主任 Varchar(4)
Monitor 班长 Int 外键，Table2(Stu_id)

Table2：
列名 中文解释 数据类型 空值 说明
Stu_id 学号 Int 否 主键，标识种子(201801，1)
Stu_name 姓名 Varchar(10) 否 唯一性约束
Stu_sex 性别 Varchar(2) 默认值为“女”
Birthdate 出生日期 Smalldatetime
Age 年龄 计算列，计算公式：
year(getdate())-year(birthdate)
Phone 联系电话 Varchar(8) ‘3935’开头，共8位数字
Class_id 班级编号 Varchar(6) 外键，Table1(Class_id)

（1）根据上表，在TEST数据库中建立数据表Table1。

create table Table1
(Class_id varchar(6)  not null,Class_name varchar(20) not null,Director varchar(4),Monitor int constraint pk_classid primary key (Class_id)
)

（2）在TEST数据库中创建Table2，只含各个基本列（包括列名和数据类型，标识种子和计算列）

create table Table2
(Stu_id int identity(201801,1),Stu_name varchar(10),Stu_sex varchar(2),Birthdate smalldatetime,Age as year(getdate()) - year(Birthdate),Phone varchar(8),Class_id varchar(6)
)

（3）Table1中的外键能否在创建Table1表时建立？如不能，该何时、如何创建？请写出相应的SQL代码。

-- 不能，需要Table2建完且设置好主键才可以创建Table1的外键
alter table Table2add constraint pk_id primary key (Stu_id);
alter table Table1add constraint fk_Stu_id foreign key (Monitor) references Table2 (Stu_id);

（4）通过ALTER TABLE语句对Table2进行表定义修改，为其增加各个约束。（注意：空值约束应单独添加）

-- 学号(非空）
alter table Table2 alter column Stu_id int not null;-- 姓名（非空，唯一性约束）
alter table Table2 alter column Stu_name varchar(10) not null;
alter table Table2 add constraint uq_name unique (Stu_name);--性别（默认值为女）
alter table Table2 add constraint de_sex default '女' for Stu_sex;--电话（3935开头，共8位数字）
alter table Table2add constraint ck_phone check (Phone like '3935[0-9][0-9][0-9][0-9]');--班级编号（外键约束，table1(Class_id)
alter table Table2add constraint fk_Class_id foreign key (Class_id) references Table1 (Class_id);

（二）、验证数据库约束
本实验主要是通过对这两张表的操作来验证数据库约束的相关概念。
1、准备好（一）中要求的两个数据表Table1，Tabel2。

2、验证主键（PRIMARY KEY）约束
试运行下面代码：
insert into Table1(class_id,class_name,director)
values(‘0101’, ‘医学智能级班’, ‘J001’)
请再一次insert into Table1(class_id,class_name,director) values(‘0101’,‘医学智能级班’, ‘J001’)， 请问能否顺利执行？如不能，出现什么问题？原因是什么？该如何解决？

答：
不能顺利执行。
问题：报错：违反了 PRIMARY KEY 约束“pk_classid”。不能在对象“dbo.Table1”中插入重复键。重复键值为 (0101)。
原因：主键字段的值必须是唯一的，不能重复。
解决：
–1.先把和有关table2的外键删除
alter table Table2 drop constraint fk_Class_id;
–2. 然后删除Table1的主键约束
alter table Table1 drop constraint pk_classid;

3、验证外键（FOREIGN）约束
试运行下面两段代码：

-- 允许将显式值插入表的标识列中 ON-允许  OFF-不允许
--语法： SET IDENTITY_INSERT [ database.[ owner.] ] { table } { ON | OFF }set identity_insert table2 on
insert into Table2(stu_id,stu_name,stu_sex,birthdate,phone,class_id)
values('2018001', '张三', '男','2000-9-23','39357887','0101')
set identity_insert table2 offset identity_insert table2 on
insert into Table2(stu_id,stu_name,stu_sex,birthdate,phone,class_id)
values('2018002', '李四', '男','2001-4-23','39357887','0102')
set identity_insert table2 off

请问：上述两段代码能否顺利执行？如不能，出现什么问题？原因是什么？该如何解决？

答：
第二段代码不能顺利执行。
报错：INSERT 语句与 FOREIGN KEY 约束"fk_Class_id"冲突。该冲突发生于数据库"TEST"，表"dbo.Table1", column ‘Class_id’。
原因：Table1没有‘0102’这个班级，如果执行成功，意味着学生在一个不存在的班级里面，违反了参照的完整性。
解决办法：加入已经存在的班级或在Table1中添加‘0102’班级的信息。

试运行下面代码：

delete from table1 where class_id='0101'

请问：上述代码能否顺利执行？如不能，出现什么问题？原因是什么？

答：
不能顺利执行。
问题：报错：DELETE 语句与 REFERENCE 约束"fk_Class_id"冲突。该冲突发生于数据库"TEST"，表"dbo.Table2", column ‘Class_id’。
原因：如果删除成功，意味着没有‘0101’这个班级了，可是Table2中‘0101’班的学生无法处理，破坏了参照的完整性。

备注：创建基本表时可指定on delete cascade，则为级联删除。那么，系统认为删除table1记录时，table2中相关的记录也会同时删除
如果不指定on delete cascade时默认为受限删除。

4、验证唯一性（UNIQUE）约束
试运行下面代码：

set identity_insert table2 on
insert into Table2(stu_id,stu_name,stu_sex,birthdate,phone,class_id)
values('2018003', '张三', '男','2000-9-23','39357887','0101')
set identity_insert table2 off

请问：上述代码能否顺利执行？如不能，出现什么问题？原因是什么？

答：
不能顺利执行。
问题：报错：违反了 UNIQUE KEY 约束“uq_name”。不能在对象“dbo.Table2”中插入重复键。重复键值为 (张三)。
原因：因为Table2表的Stu_name属性列有唯一约束，‘张三’与表中数据有冲突。

5、验证检查（CHECK）约束
试运行下面代码：

insert into Table2(stu_name,stu_sex,birthdate,phone,class_id)
values( '张历', '男','2000-9-23','79357887','0101')

请问：上述代码能否顺利执行？如不能，出现什么问题？原因是什么？

答：
上述代码不能顺利执行。
问题：报错：INSERT 语句与 CHECK 约束"ck_phone"冲突。该冲突发生于数据库"TEST"，表"dbo.Table2", column ‘Phone’。
原因：因为表Table2中的电话号码有check用户定义约束，phone是以‘3935’开头的8位电话号码，破坏了用户定义的完整性。

三、实验小结
1．什么是数据库的完整性？
答：
数据库的完整性是指数据的正确性和相容性。
数据的正确性是指数据是符合现实世界语义、反映当前实际状况的；
数据的相容性是指数据库同一对象在不同关系表中的数据是符合逻辑的。

2 . 思考各类完整性约束对于数据库的数据检查有何作用？
答：数据完整性约束指的是为了防止不符合规范的数据进入数据库在用户对数据进行插入、修改、删除等操作时，DBMS自动按照一定的约束条件对数据进行监测，使不符合规范的数据不能进入数据库，以确保数据库中存储的数据正确、有效、相容。

3 . 此次实验中得到的哪些经验教训、疑难问题？有什么心得或总结？
答：总结：理解了SQL的完整性控制机制，掌握了数据库三类完整性约束的应用方法，通过SQL的完整性控制，可以确保数据库中的数据是准确的。在实际应用中，需要根据业务需求选择合适的约束类型，并正确设置约束条件，需要定期检查和维护约束，确保其有效性。