实验6 数据完整性
一、实验目的
1．掌握Transact-SQL语句(CREATE RULE、DROP RULE)创建和删除规则的方法。
2．掌握系统存储过程sp_bindrule、sp_unbindrule绑定和解除绑定规则的操作方法，以及sp_help、sp_helptext查询规则信息、sp_rename更名规则的方法。
3．掌握Transact-SQL语句(CREATE DEFAULT、DROPDEFAULT)创建和删除默认对象的方法。
4．掌握系统存储过程sp_bindefault、sp_unbindefault绑定和解除绑定默认对象的操作方法，以及sp_helptext查询规则信息。
5．掌握SQL Server管理平台和Transact-SQL语句(CREATE TABLE、ALTER TABLE)定义和删除约束的方法，并了解约束的类型。
二、实验内容和步骤
1．为studentsdb数据库创建一个规则，限制所输入的数据为7位0～9的数字。
(1)复制学生表命名为stu_phone，在stu_phone表中插入一列，列名为“电话号码”。完成以下代码实现该操作。
SELECT * INTO stu_phone FROM 学生表
ALTER TABLE stu_phone ADD 电话号码 CHAR(7)NULL
stu_phone表结构如图1-10所示。
图1-10 stu_phone表结构
SELECT * INTO stu_phone FROM 学生表
ALTER TABLE stu_phone ADD 电话号码CHAR(7)NULL
(2)创建一个规则phone_rule，限制所输入的数据为7位0～9的数字。
CREATE rule phone_rule
AS
@电话号码LIKE '[0-9][0-9][0-9][0-9][0-9][0-9][0-9]'
检验：
电话号码为8位时无法插入！
电话号码为7位时可以插入！
(3)使用系统存储过程sp_hndrule将phone_rule规则绑定到stu_phone表的“电话号码”列上。
sp_hndrule phone_rule , ‘stu_phone.电话号码’
(4)插入操作输入以下数据：
学号 姓名 电话号码
0009 王国强 1234yyy
是否可以成果插入？如果出现错误，请列出错误信息，为什么会产生该出错信息?如果要实现插入操作，应修改INSERT INTO语句中的哪个值?phone_rule规则能否对其他操作(如DELETE)进行规则检查?
INSERT INTO stu_phone(学号,姓名,电话号码)
VALUES('0009','王国强','1234yyy')
错误信息：消息513，级别16，状态0，第1 行
列的插入或更新与先前的CREATE RULE 语句所指定的规则发生冲突。该语句已终止。冲突发生于数据库'studentsdb'，表'dbo.stu_phone'，列'电话号码'。
语句已终止。
修改后：
INSERT INTO stu_phone(学号,姓名,电话号码)
VALUES('0009','王国强','1234yyy')
phone_rule规则能否对其他操作(如DELETE)进行规则检查?
可以
2．创建一个规则stusex_rule，将其绑定到stu_phone表的“性别”列上，保证输入的性别值只能是“男”或“女”。
CREATE rule stusex_rule
AS
@性别in ('男','女')
sp_bindrule stusex_rule , 'stu_phone.性别'
3．使用系统存储过程sp_help查询stusex_rule规则列表，使用sp_helptext查询stusex_rule规则的文本，使用sp_rename将stusex_rule规则更名为stu_s_rule。
sp_help stusex_rule
sp_helptext stusex_rule
sp_rename stusex_rule,stu_s_rule
4．删除stu_s_rule规则。
先解除绑定：sp_unbindrule stu_s_rule, 'stu_phone.性别'
再删除规则：drop rule stu_s_rule
注意：stu_s_ rule为stusex_ rule更名后规则名，是否仍然绑定在stu_phone表的“性别”列上，应如何操作才能删除它。
5．在studentdb数据库中，建立日期、货币和字符等数据类型的默认对象。
(1)创建默认对象df_date、df_char、df_money。
l 创建日期型默认对象df_date，默认日期为20##-4-12。
l 创建字符型默认对象df_char，默认字符为“unknown”
l 创建货币型默认对象df_money，默认为100元
CREATE DEFAULT df_date
AS '20##-4-12'
GO
CREATE DEFAULT df_char
AS 'unknown'
GO
CREATE DEFAULT df_money
AS $100
GO
(2)在studentsdb数据库中创建stu_fee数据表。
学号 char(10) NOT NULL
姓名 char(8) NOT NULL，
学费 money
交费日期 datetime
电话号码 char(7)
表stu_fee的数据结构如图1-11所示。
图l-11 stu_fee的数据结构
CREATE TABLE stu_fee
(学号char(10) NOT NULL,
姓名char（）NOT NULL,
学费money,
交费日期datetime,
电话号码char（））
(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表进行插入操作:
(学号，姓名) values ('0001'，’刘卫平’)
（学号，姓名，学费) values ('0001'，’张卫民’,$120)
(学号，姓名，学费，交费日期) values ('0001'，‘马东‘，$110，’20##-5-12‘)
分析stu_fee表中插入记录的各列的值是什么?
INSERT INTO stu_fee(学号,姓名) VALUES('0001','刘卫平')
INSERT INTO stu_fee(学号,姓名,学费)VALUES('0001','张卫民',$120)
INSERT INTO stu_fee(学号,姓名,学费,交费日期) VALUES('0001','马东',$110,'20##-5-12')
各列值为：
刘卫平，张卫民的交费日期为默认值
(5)完成以下代码解除默认对象df_char、df_date、df_money的绑定，并删除之。
sp_unbindefault 'stu_fee.电话号码'
DROP DEFAULT df_char
sp_unbindefault 'stu_fee.交费日期'
DROP DEFAULT df_date
sp_unbindefault 'stu_fee.学费'
DROP DEFAULT df_money
6．为学生表添加一列，命名为“院系”，创建一个默认对象stu_d_df，将其绑定到学生表的“院系”列上，使其默认值为“信息院”，对学生表进行插入操作，操作完成后，删除该默认对象。
SELECT * FROM 学生表
ALTER TABLE 学生表ADD 院系CHAR(8) NULL
CREATE DEFAULT stu_d_df AS '信息院'
sp_bindefault stu_d_df,'学生表.院系'
INSERT INTO 学生表(学号,姓名) VALUES('3244','郭冰骅')
sp_unbindefault '学生表.院系'
DROP DEFAULT stu_d_df
7．在studentsdb数据库中用CREATE TABLE语句创建表stu_con，并同时创建约束。
(1)创建表的同时创建约束。表结构如图1-12所示。
图1-12 要创建的表的结构
约束要求如下：
①将学号设置为主键(PRIMARY KEY)，主键名为pk_sid。
②为姓名添加唯一约束(UNIQUE)，约束名为uk_name。
③为性别添加默认约束(DEFAULT)，默认名称为df_sex，其值为“男”
④为出生日期添加属性值约束(CHECK)，约束名为ck_bday，其检查条件为：出生日期>'1988-1-1’。
(2)在stu_con表中插入如表1-1所示的数据记录。
表1-1 在stu_con表中插入的数据
分析各约束在插入记录时所起的作用，查看插入记录后表中数据与所插入的数据是否一致?
CREATE TABLE stu_con
(学号char(4) NOT NULL CONSTRAINT pk_sid PRIMARY KEY ,
姓名char(8) NOT NULL CONSTRAINT uk_name UNIQUE,
学费money,
性别char(2) CONSTRAINT df_sex DEFAULT ('男'),
出生日期datetime CONSTRAINT ck_bday CHECK (出生日期>'1988-1-1'),
家庭住址char(50))
INSERT INTO stu_con(学号,姓名,出生日期) VALUES('0009','张小东','1989-4-6')
INSERT INTO stu_con(学号,姓名,性别,出生日期)VALUES('0010','李梅','男','1983-8-5')
INSERT INTO stu_con(学号,姓名,出生日期) VALUES('0011','王强','1988-9-10')
INSERT INTO stu_con(学号,姓名,出生日期) VALUES('0012','王强','1989-6-3')
运行结果：
(1 行受影响)
消息547，级别16，状态0，第2 行
INSERT 语句与CHECK 约束"ck_bday"冲突。该冲突发生于数据库"studentsdb"，表"dbo.stu_con", column '出生日期'。
语句已终止。
(1 行受影响)
消息2627，级别14，状态1，第4 行
违反了UNIQUE KEY 约束'uk_name'。不能在对象'dbo.stu_con' 中插入重复键。
语句已终止。
(3)使用ALTER TABLE语句的DROP CONSTRAINT参数项在查询分析器中删除为stu_con表所建的约束。
ALTER TABLE stu_con
DROP CONSTRAINT pk_sid
ALTER TABLE stu_con
DROP CONSTRAINT uk_name
ALTER TABLE stu_con
DROP CONSTRAINT df_sex
ALTER TABLE stu_con
DROP CONSTRAINT ck_bday
8．用SQL Server管理平台完成实验内容7的所有设置。
出现问题！只能出现张小东的信息
9. 在查询分析器中，为studentsdb数据库的成绩表添加外键约束(FOREIGN KEY)，要求将“学号”设置为外键，参照表为学生表，外键名称为fk_sid。
ALTER TABLE 成绩表
ADD CONSTRAINT fk_sid
FOREIGN KEY(学号) references 学生表(学号)
注意：学生表里的学号一定要与成绩表中的学号对应，否则会报错冲突
⑴使用系统存储过程sp_help查看grade表的外键信息。
sp_help 成绩表
⑵在成绩表中插入表1-2所示记录，观察SQLServer会做何处理，为什么?如何解决所产生的问题?
表1-2
INSERT INTO 成绩表(学号,课程编号,分数) VALUES('0010','0001','78')
出现问题：
消息547，级别16，状态0，第2 行
INSERT 语句与FOREIGN KEY 约束"fk_sid"冲突。该冲突发生于数据库"studentsdb"，表"dbo.学生表", column '学号'。
语句已终止。
解决问题：在学生表中先插入一个学号为0010的学生信息
⑶使用查询分析器删除成绩表的外键fk_sid。
ALTER table 成绩表
DROP CONSTRAINT fk_sid
四、实验思考
1．在SQLServer 2005中，可采用哪些方法实现数据完整性?
答：数据类型、主键、外键、默认值、约束与规则
2，比较默认对象和默认约束的异同。
答：相同点：
默认对象与默认约束功能相似。默认值可以在没有指定具体数据的列中自动插入数据。默认约束是通过定义列的默认值或使用数据库的默认值对象绑定表的列，以确保在没有为某列指定数据时，来指定列的值。每列只能有一个默认约束一个默认值
不同点：
默认对象与CREAT TABLE 和ALTER TABLE语句一起使用，而默认约束只能用于INSERT语句。
3.在数据库中建立的规则不绑定到到数据表的字段上会起作用吗？为什么？
答：不会规则仅仅只是一个存在与数据库中的对象，并未发生作用。