实验6数据完整性.doc

实验6 数据完整性
一、实验目的
1. 掌握企业管理器和Transact-SQL语句（CREATE RULE、DROP RULE）创建和删除规则的方法。
2. 掌握系统存储过程sp_bindrule、sp_unbindrule绑定和解除绑定规则的操作方法，以及sp_help、sp_helptext查询规则信息，sp_rename 更名规则的方法。
3. 掌握企业管理器和Transact-SQL 语句（CREATE DEFAULT 、DROP DEFAULT）创建和删除默认对象的方法。
4. 掌握系统存储过程sp_bindefault、sp_unbindefault 绑定和解除绑定默认对象的操作方法，以及sp_help、sp_helptext 查询规则信息，sp_rename更名规则的方法。
5. 掌握企业管理器和Transact-SQL 语句（CREATE TABLE、AKTER TABLE）定义和删除约束的方法，并了解约束的类型。

二、实验准备
1. 了解数据完整性概念。
2. 了解创建规则和删除规则的语法，了解绑定规则和解除绑定规则的语法。
3. 了解创建默认对象和删除默认对象的语法，了解绑定和删除绑定默认对象的语法。
4. 了解约束的类型，创建约束和删除约束的语法。

三、实验内容和步骤

1．为sutdentsdb数据库创建一个规则，限制所输入的数据为7位0-9的数字。
(1)复制student_info表命名为stu_phone，在stu_phone表中插入一列，列名为“电话号码”。完成以下代码实现该操作。

SELECT*INTO stu_phone FROM student_info
ALTER TABLE stu_phone ADD __ colomn 电话号码__ CHAR(7)NULL

Stu_phone表结构如图1-10所示。

学号
姓名
性别
出生日期
家庭住址
备注
电话号码
1
0001
刘卫平
男
1980-10-01 00：00：00.000
衡山市东风路78号
NULL
NULL
2
0002
张为民
男
1980-12-02 00：00：00.000
东阳市八一北路25号
NULL
NULL

图 1-10 stu_phone 表结构

（2）创建一个规则phone_rule，限制所输入的数据为7位0～9的数字。实现该规则的代码为

CREATE___RULE___phone_rule
AS
@phone LIKE `[0-9] [0-9] [0-9] [0-9] [0-9] [0-9] [0-9]

（3）使用系统存储过程sp_bindrule将phone_rule规则绑定到stu_phone表的“电话号码”列上。实现该操作的代码为
sp_bindrule__phone_rule___,`stu_phone.电话号码`

（4）输入以下代码，进行一次插入操作：
INSERT INTO stu_phone(学号，姓名，电话号码)VALUES(`0009`,`王国强`,`1234yyy`)

产生以下出错信息：

服务器：消息513,级别16,状态1,行1
列的插入或更新与先前的CREATE RULE语句所强制的规则冲突。改语句已终止。冲突发生于数据库`studentsdb`,表`stu_phone`,列`电话号码`

试分析：为什么会产生该出错信息？如果要实现插入操作，应修改INSERT INTO语句中的哪个值？phone_rule规则能否对其他操作（如DELETE）进行规则检查？

INSERT INTO stu_phone(学号，姓名，电话号码)
VALUES('0009','王国强','1234yyy')

插入不成功,错误原因在于违反前述的规则约束(即'y'不在'0-9'之内);应修改'1234yyy'中的'yyy',使之均在0-9范围之内;phone_rule规则只对插入和修改进行规则检查,而删除不进行规则检查.

2．使用企业管理器实现实验内容1的每个操作。

3．创建一个规则stusex_rule，将其绑定到stu_phone表的“性别”列上，保证输入的性别值只能是“男”或“女”。
CREATE RULE stusex_rule
AS
@性别 in (‘男’,’女’)
sp_bindrule stusex_rule ‘stu_phone.性别’

4．使用系统存储过程sp_help查询stusex_rule规则列表，使用sp_helptext查询stusex_rule规则的文本，使用sp_rename将stusex_rule规则更名为stu_s_rule。
EXCUTE sp_help stusex_rule
EXCUTE sp_helptext stusex_rule
EXCUTE sp_rename stusex_rule,stu_s_rule

5．删除stu_s_rule规则。
答：应该先解除绑定再删除

EXCUTE sp_unbindrule ‘stu_phone.性别’
DROP stu_s_rule

6．在stuedentdb数据库中，建立日期、货币和字符等数据类型的默认对象。
（1）在查询分析其中，完成以下代码，创建默认对象df_date、df_char、df_money。

--创建日期型默认对象df_date
CREATE DEFAULT df_date
AS`2006-4-12`
GO
----创建字符型默认对象df_char
CREATE DEFAULT df_char
AS `unknown`
GO
--创建货币型默认对象df_money
CREATE DEFAULT df_money
AS $100
GO

(2) 输入以下代码，在studentdb数据库中创建stu_fee数据表。

CREATE TABLE stu_fee
(学号char(10)NOT NULL,
姓名char（8）NOT NULL,
学费 money,
交费日期 datetime,
电话号码 char(7））

表 stu_fee 的数据结构如图1-11所示。

学号
姓名
学费
交费日期
电话号码

图1-11 stu_fee.的数据结构

（3） 使用系统存储过程 sp-bindefault 将默认对象 df_date、df_char、df_money 分别绑定在stu_fee表的“学费”、“交费日期”、“电话号码”列上。

Sp_bindefault df_money,'stu_fee.学费'
GO
sp_bindefault df_date,'stu_fee.交费日期'
GO
Sp_bindefault df_char，'stu_fee.电话号码'
GO

(4) 输入以下代码，在stu_fee表进行插入操作：

INSERT INTO stu_fee(学号,姓名） values('0001','刘卫平'）
INSERT INTO stu_fee(学号,姓名,学费）values('0001',张卫民',$120）
INSERT INTO stu_fee(学号,姓名,学费,交费日期)
VALUES('0001','马东',$110,'2006-5-12')
分析 stu_fee表中插入记录的各列的值是什么？
0001 刘卫平 $100 2006-4-12
0001 张卫民 $120 2006-4-12
0001 马东 $110 2006-5-12

（5） 完成以下代码解除默认对象df_char的绑定，并删除之。
按同样的方式，删除默认对象df_date/df_money。
EXCUTE unbindefault 'stu_fee.电话号码'
DROP DEFAULT df_char

EXCUTE unbindefault 'stu_fee.交费日期'
DROP DEFAULT df_date

EXCUTE unbindefault 'stu_fee.学费'
DROP DEFAULT df_money

7.使用企业管理器创建默认对象df_date、df_money，并将它们分别绑定到stu_fee表的“学费”、“交费日期”、“电话号码”列上，在插入操作中观察stu_fee表的数据变化情况，完成操作后，删除这些默认对象。

具体操作。

8.为student_info表添加一页，命名为“院系”，创建一个默认对象stu_d_df，将其绑定到student_info表的“院系”列上，时期默认值为“信息院”，对student_info表进行插入操作，操作完成后，删除该默认对象。分别使用企业管理器和查询分析器实现。
ALTER TABLE student_info
ADD 院系
GO
CREATE DEFAULT stu_d_df
AS ‘信息院’
GO
sp_bindefault stu_d_df ‘student_info.院系’
GO

EXCUTE unbindefault 'stu_info.院系'
DROP DEFAULT stu_d_df

9.在studentsdb数据库中用CREATE TABLE语句创建表stu_con，并同时创建约束。
（1）创建表的同时创建约束。
表结构如图1-12所示
列名
数据类型
长度
学号
char
4
姓名
char
8
性别
char
2
出生日期
datetime
8
家庭住址
varchar
50
图 1-12 要创建的表的结构
约束要求如下：
① 将学号设置为主键（PRIMARY KEY），主键名为pk_sid。
② 为姓名添加唯一约束（UNIQUE），约束名为uk_name。
③ 为性别添加默认约束（DEFAULT），默认名称为df_sex，其值为“男”。
④ 为出生日期添加属性值约束（CHECK），约束名为ck_beday，其检查条件为：出生日期>’1988-1-1’。

CREATE TABLE stu_con
学号 char(4)
CONSTRAINT pk_sid PRIMARY KEY
姓名 char(8)
CONSTRAINT uk_name UNIQUE
性别 char(2)
CONSTRAINT df_sex DEFAULT ‘男’
出生日期 datetime(8)
CONSTRAINT ck_beday CHECK 出生日期>’1988-1-1’
家庭住址 varchar(50)

（2） 在stu_con表中插入如表1-1所示的数据记录。
分析各约束在插入记录时所起的作用，查看插入记录后表中数据与所插入的数据是否一致？
答:不一致

（3）使用ALTER TABLE语句的DROP CONSTRAINT参数项在查询分析器中删除为stu_con表所建的约束。
ALTER TABLE stu_con
DROP CONSTRAINT pk_sid,uk_name,df_sex,ck_date

10. 用企业管理器完成实验内容9的所有设置。

11. 在查询分析器中，为studentsdb数据库的grade表添加外键约束（FOREIGN KEY），要求将“学号”设置为外键，参照表为student_info，外键名为ufk_sid。
使用系统存储过程sp_help查看grade表的外键信息。
在grade表中插入表1-2所示记录，观察SQL Server会做何处理，为什么？如何解决所产生的问题?
表1-2
学号
课程编号
分数
0100
0001
78
使用查询分析器删除grade表的外键fk_sid。

USE studentsdb
ALTER TABLE grade
CONSTRAINT ufk_sid FOREIGN KEY(学号)
REFERENCES student_info(学号)
GO
sp_help grade
GO
INSERT INTO grade(学号,课程编号,分数) VALUES('0100','0001',78)
GO

SQL Server会报错,原因在于插入的学号违反外键约束,即在student_info表中没有学号为'0100'的字段.

四、实验思考
1. 在SQL Server 2000中，可采用哪些方法实现数据完整性？
使用规则实施数据完整性
使用默认值实施数据完整性
使用约束实施数据完整性

2. 比较默认对象和默认约束的异同。
检查约束是一个规则，它确认一个SQL Server表中某条记录中的数据可接受的字段值。检查约束帮助执行域完整性。域完整性定义了一个数据库表中字段的有效值。检查约束可以验证一个单独字段或一些字段的域完整性。你对一个单独的字段可以有多个检查完整性。如果被插入或更新的数据违反了一个检查约束，那么数据库引擎将不允许这个插入或更新的操作发生。
默认值约束和默认对象：默认值约束是对象级的，其作用范围仅限于一个表，删除表则删除默认值约束。默认值对象是数据库级的，作为数据库对象单独存在于数据库中，需要时可以绑定到任何一个表中。