【SQL】实验十 数据库完整性实验

一、实验目的

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 primary key(Class_id),
Class_name varchar(20) not null,
Director varchar(4) null,
Monitor Int null,
)

在建立好table2后再添加主键约束

alter table Table1
add foreign key(Monitor) references Table2(Stu_id)

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

create table Table2(
Stu_id Int identity(201801,1),
Stu_name varchar(10),
Stu_Sex varchar(2) default('男'),
Birthdate Smalldatetime,
Age aS year(getdate())-year(Birthdate),
Phone varchar(8),
Class_id varchar(6) 
)

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

alter table Table1
add foreign key(Monitor) references Table2(Stu_id)

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

添加空值约束

alter table Table3
alter column Stu_id Int not null

添加check约束

alter table Table2
add constraint Phone check(Phone like'3935%')

 添加默认约束

alter table Table2
add constraint Stu_sex default('男')

 添加唯一性约束

alter table Table2
add constraint Stu_name unique

添加外键约束

alter table Table2
add constraint foreign key(Class_id) references Table1(Class_id)

 添加主键约束

alter table Table2
add constraint primary key(Stu_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')， 请问能否顺利执行？如不能，出现什么问题？原因是什么？该如何解决？

消息 2627，级别 14，状态 1，第 1 行
违反了 PRIMARY KEY 约束“PK__Table1__B096396F59B4E266”。不能在对象“dbo.Table1”中插入重复键。重复键值为 (0101)。
语句已终止。

不能，违反了主键约束

原因是在插入或对主码列进行更新操作时，RDBMS按照实体完整性规则自动进行检查，包括： 1. 检查主码值是否唯一，如果不唯一则拒绝插入或修改 2. 检查主码的各个属性是否为空，只要有一个为空就拒绝插入或修改

修改重复值或者删除主键约束即可

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 off

set 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

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

第一段代码可以正常执行，第二段代码不能正常执行，报错如下

消息 547，级别 16，状态 0，第 3 行
INSERT 语句与 FOREIGN KEY 约束"FK__Table2__Class_id__37A5467C"冲突。该冲突发生于数据库"TEST"，表"dbo.Table1", column 'Class_id'。
语句已终止。
 

原因是与table2的外键冲突，因为这两个表被我们用Class_id外键连接起来了，而我们只添加了一个表的Class_id

在table1中添加与table2相同的class_id的内容，或者删除此外键约束即可

试运行下面代码：

delete from table1 where class_id='0101'

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

消息 547，级别 16，状态 0，第 1 行
DELETE 语句与 REFERENCE 约束"FK__Table2__Class_id__37A5467C"冲突。该冲突发生于数据库"TEST"，表"dbo.Table2", column 'Class_id'。
语句已终止 

不能，与table2的外键冲突 ，理由同上题类似

可在创建基本表时指定采用级联删除 cascade，或者将两个表的值一起删除，或者删除外键约束

备注：创建基本表时可指定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

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

消息 2627，级别 14，状态 1，第 3 行
违反了 UNIQUE KEY 约束“UQ__Table2__4561991B95564E6D”。不能在对象“dbo.Table2”中插入重复键。重复键值为 (张三)。
语句已终止。

不能，违反了Stu-name的唯一性约束，Stu_name的值重复了

修改Stu_name的重复值或者删除唯一性约束即可

5、验证检查（CHECK）约束

试运行下面代码：

insert into Table2(stu_name,stu_sex,birthdate,phone,class_id)

values( '张历', '男','2000-9-23','79357887','0101')

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

消息 547，级别 16，状态 0，第 1 行
INSERT 语句与 CHECK 约束"CK__Table2__Phone__2E1BDC42"冲突。该冲突发生于数据库"TEST"，表"dbo.Table2", column 'Phone'。
语句已终止。
不能，违反了Phone的check约束，不满足check约束里面的条件，拒绝执行

将添加的Phone值修改为满足check条件的值再添加，或者删除check约束即可

 三、实验小结

1．什么是数据库的完整性？

数据完整性是指存储在数据库中的数据正确无误并且相关数据具有一致性。

2. 思考各类完整性约束对于数据库的数据检查有何作用？ 

1．数据库完整性约束能够防止合法用户使用数据库时向数据库中添加不合语义的数据。
2．利用基于DBMS的完整性控制机制来实现业务规则，易于定义，容易理解，而且可以降低应用程序的复杂性，提高应用程序的运行效率。同时，基于DBMS的完整性控制机制是集中管理的，因此比应用程序更容易实现数据库的完整性。
3．合理的数据库完整性设计，能够同时兼顾数据库的完整性和系统的效能。
4．在应用软件的功能测试中，完善的数据库完整性有助于尽早发现应用软件的错误

3. 此次实验中得到的哪些经验教训、疑难问题？有什么心得或总结？

注意关键词，如添加空值约束使用的是column修饰，添加其他如唯一性约束时使用的是constraint修饰

也可以自定义约束名，在约束前面添加自定义约束名即可，不添加则为系统默认约束名