数据库原理与应用实验十 数据库完整性实验

实验十 数据库完整性实验

文章内容为作者自己做实验，在自己电脑上试验，如有错误，欢迎提意见与建议

一、实验目的

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。

USE TEST
CREATE TABLE Table1
(
Class_id varchar(6) NOT NULL PRIMARY KEY,
	Class_name varchar(20) NOT NULL,
	Director varchar(4) NULL,
	Monitor int  NULL,
)
GO

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

CREATE TABLE Table2(
	Stu_id int identity(201801,1) NOT NULL PRIMARY KEY,
	Stu_name varchar(10) NOT NULL UNIQUE,
	Stu_sex varchar(2)  NULL,
	Birthdate smalldatetime  NULL,
	Phone varchar(8) NULL  ,
	Class_id varchar(6)  NULL,
)  
GO

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

不能，因为这时候还没有Table2。应该在建立了Table2后创建。代码如下：

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

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

这个是因为没有指定数据类型，是属于计算列 Age。

ALTER TABLE Table2 
ADD  Age  AS year(getdate())-year(Birthdate)

这是限制Phone的输入必须以’3935’开头，注意 ] 与 [ 之间没有空格，本人因为这点后面实验一直失败，因为输入不进去，找了非常久的bug。

ALTER TABLE Table2  
WITH CHECK ADD  CONSTRAINT CK_Phone 
CHECK  (Phone like '3935[0-9][0-9][0-9][0-9]') 

这个是设置外键。

ALTER TABLE Table2 
ADD 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__Table1__B096396F3069A922”。不能在对象“dbo.Table1”中插入重复键。重复键值为 (0101)。
解决方法：修改class_id 的值。
（为了不影响后面的实验，请不要改成’0102’）

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

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

不能顺利执行。
INSERT 语句与 FOREIGN KEY 约束"fk_Class_id"冲突。该冲突发生于数据库"TEST"，表"dbo.Table1", column ‘Class_id’。
因为我们一开始只有一个名为’0101’的Class_id，没有’0102’，而这两个表被我们用外键连起来了。
想要解决这个问题，有两种方式：
1.在Table1中加入一个Class_id为’0102’的数据。
2.将’0102’改成’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__Table2__4561991BA78A88B6”。不能在对象“dbo.Table2”中插入重复键。重复键值为 (张三)。

	这个是因为我们设置了Stu_name为唯一性约束，也就是它是唯一的。
我们做第2小题的时候已经录入了张三的信息，所以不能再使用张三这个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’。

我们在做检查约束的时候是限制了前面是'3935'开头，后面是4个0到9的数字。
这里是'7935'开头，所以是不给录入的。

三、实验小结

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

数据完整性是指数据的精确性和可靠性。
它是应防止数据库中存在不符合语义规定的数据和防止因错误信息的输入输出造成
无效操作或错误信息而提出的。
数据完整性分为四类：实体完整性、域完整性、参照完整性、用户自定义完整性。

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

完整性约束对数据检查的作用主要体现在输入的数据是否有非法的值，
保持数据的一致性。

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