SQL数据完整性.docx

1、SQL数据完整性实验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查询规

2、则信息。5掌握SQL Server管理平台和Transact-SQL语句(CREATE TABLE、ALTER TABLE)定义和删除约束的方法，并了解约束的类型。二、实验内容和步骤 1为studentsdb数据库创建一个规则，限制所输入的数据为7位09的数字。(1)复制学生表命名为stu_phone，在stu_phone表中插入一列，列名为“电话号码”。完成以下代码实现该操作。SELECT * INTO stu_phone FROM 学生表ALTER TABLE stu_phone ADD 电话号码 CHAR(7)NULLstu_phone表结构如图1-1所示。图 1-1 stu_phone

3、表结构(2)创建一个规则phone_rule，限制所输入的数据为7位09的数字CREATE RULE phone_rule AS 电话号码like 0-90-90-90-90-90-90-9 (3)使用系统存储过程sp_hndrule将phone_rule规则绑定到stu_phone表的“电话号码”列上。 sp_bindrule phone_rule,stu_phone.电话号码(4)插入操作输入以下数据：学号 姓名 电话号码 0009 王国强 1234yyy 是否可以成功插入？如果出现错误，请列出错误信息，为什么会产生该出错信息?如果要实现插入操作，应修改INSERT INTO语句中的哪个值

4、?phone_rule规则能否对其他操作(如DELETE)进行规则检查? 答：、插入语句如下： INSERT INTO stu_phone(学号,姓名,电话号码) VALUES(0009,王国强,1234yyy) 、插入出错，错误信息如下： 消息513，级别16，状态0，第1 行 列的插入或更新与先前的CREATE RULE 语句所指定的规则发生冲突。该语句已终止。冲突发生于数据库studentsdb，表dbo.stu_phone，列电话号码。 语句已终止。 、出现错误的原因：在于电话号码的输入不对，因为电话号码绑定的规则是7位0-9的数字。 、修改：将电话号码中的字母改为数字即可，代码如下

5、INSERT INTO stu_phone(学号,姓名,电话号码) VALUES(0009,王国强,1234567) 执行结果如下： 、不能，代码如下： delete from stu_phone where 电话号码= 1234567 结果如下： 2创建一个规则stusex_rule，将其绑定到stu_phone表的“性别”列上，保证输入的性别值只能是“男”或“女”。CREATE RULE STUSEX_RULEAS SEX IN(男,女)EXEC SP_BINDRULE STUSEX_RULE,STU_PHONE.性别 3使用系统存储过程sp_help查询stusex_rule规则列表。E

6、XECUTE SP_HELP STUSEX_RULE结果截图如下：使用sp_helptext查询stusex_rule规则的文本。EXECUTE SP_HELPTEXT STUSEX_RULE结果截图如下：使用sp_rename将stusex_rule规则更名为stu_s_rule。EXECUTE SP_RENAME STUSEX_RULE , STU_S_RULE结果截图如下： 4删除stu_s_rule规则。EXEC SP_UNBINDRULE STU_PHONE.性别用DROP RULE STU_S_RULE语句进行删除， 截图如下： 5在studentdb数据库中，建立日期、货币和字符

7、等数据类型的默认对象。 (1)创建默认对象df_date、df_char、df_money。 、创建日期型默认对象df_date，默认日期为2006-4-12。CREATE DEFAULT DF_DATE AS 2006-4-12 结果截图如下： 、创建字符型默认对象df_char，默认字符为“unknown”CREATE DEFAULT DF_CHAR AS UNKNOWN 结果截图如下：、创建货币型默认对象df_money，默认为100元代码如下：CREATE DEFAULT DF_MONEY AS RMB:100 结果截图如下： (2)在studentsdb数据库中创建stu_fee数据

8、表。学号 char(10) NOT NULL姓名 char(8) NOT NULL，学费 money交费日期 datetime电话号码 char(7)CREATE TABLE STU_FEE(学号CHAR(10)NOT NULL,姓名CHAR(8)NOT NULL,学费MONEY ,缴费日期DATETIME,电话号码CHAR(7) 结果截图如下： (3)使用系统存储过程sp_bindehult将默认对象df_date、df_char、df_money分别绑定在stu_fee表的“学费”、“交费日期”、“电话号码”列上。EXEC SP_BINDEFAULT DF_DATE,STU_FEE.缴费日

9、期EXEC SP_BINDEFAULT DF_CHAR,STU_FEE.电话号码EXEC SP_BINDEFAULT DF_MONEY,STU_FEE.学费 结果截图如下： (4)输入命令，在stu_fee表进行插入操作: (学号，姓名) values (0001，刘卫平) (学号，姓名，学费) values (0001，张卫民,$120) (学号，姓名，学费，交费日期) values (0001，马东，$110,2006-5-12)INSERT STU_FEE(学号,姓名) VALUES(0001,刘卫平)INSERT STU_FEE (学号,姓名,学费) VALUES(0002,张卫民,$

10、120)INSERT STU_FEE(学号,姓名,学费,缴费日期) VALUES(0003,马东,$110,2006-5-12) 结果截图如下：(5)完成以下代码解除默认对象df_char、df_date、df_money的绑定，并删除之。、解除绑定EXEC SP_UNBINDEFAULT STU_FEE.缴费日期EXEC SP_UNBINDEFAULT STU_FEE.电话号码EXEC SP_UNBINDEFAULT STU_FEE.学费、删除DROP DEFAULT DF_DATE,DF_CHAR,DF_MONEY 结果截图如下： 6为学生表添加一列，命名为“院系”，ALTER TABLE

11、 学生表 ADD 院系CHAR(16) 结果截图如下： 创建一个默认对象stu_d_df，将其绑定到学生表的“院系”列上，CREATE DEFAULT STU_D_DF AS 信息院EXEC SP_BINDEFAULT STU_D_DF,学生表.院系 使其默认值为“信息院”，对学生表进行插入操作，INSERT 学生表 (学号,姓名) VALUES(0009,王国强) 结果截图如下： 7在studentsdb数据库中用CREATE TABLE语句创建表stu_con，并同时创建约束。 (1)创建表的同时创建约束。表结构如图1-12所示。图1-12 要创建的表的结构约束要求如下： 将学号设置为主键

12、(PRIMARY KEY)，主键名为pk_sid。 为姓名添加唯一约束(UNIQUE)，约束名为uk_name。 为性别添加默认约束(DEFAULT)，默认名称为df_sex，其值为“男”为出生日期添加属性值约束(CHECK)，约束名为ck_bday，其检查条件为：出生日期1988-1-1。CREATE TABLE STU_CON(学号CHAR(4)NOT NULLCONSTRAINT PK_SID PRIMARY KEY(学号),姓名CHAR(8)NOT NULLCONSTRAINT UK_NAME UNIQUE,性别CHAR(2) CONSTRAINT DF_SEX DEFAULT (男)

13、,出生日期DATETIMECONSTRAINT CK_BDAY CHECK(出生日期1988-1-1),家庭住址VARCHAR(50) 结果截图如下： (2)在stu_con表中插入如表1-1所示的数据记录。 在插入0010的数据时出现错误截图如下：约束CK_BDAY的作用是使插入的数据中出生日期项都小于“1988-1-1”在插入0012的数据记录时出现错误截图如下：约束UK_NAME的作用是使插入的数据姓名项没有重复的内容经修改完全插入后截图如下：可发现，除自行修改外，表中数据与所插入的数据也不全相同。(3)使用ALTER TABLE语句的DROP CONSTRAINT参数项在查询分析器中删

14、除为stu_con表所建的约束。ALTER TABLE STU_CONDROP CONSTRAINT PK_SID,UK_NAME,DF_SEX,CK_BDAY 结果截图如下： 8、用SQL Server管理平台完成实验内容7的所有设置 将学号设置为主键(PRIMARY KEY)，主键名为pk_sid。为姓名添加唯一约束(UNIQUE)，约束名为uk_name。为性别添加默认约束(DEFAULT)，默认名称为df_sex，其值为“男”为出生日期添加属性值约束(CHECK)，约束名为ck_bday，其检查条件为：出生日期1988-1-1。 9、 在查询分析器中，为studentsdb数据库的成绩

15、表添加外键约束(FOREIGN KEY)，要求将“学号”设置为外键，参照表为学生表，外键名称为fk_sid。alter table 成绩表add constraint fk_sid foreign key(学号) references 学生表(学号)使用系统存储过程sp_help查看grade表的外键信息。sp_help fk_sid 在成绩表中插入表1-2所示记录，观察SQLServer会做何处理，为什么?如何解决所产生的问题?表1-2学号课程编号分数0100000178INSERT 成绩表VALUES (0100,0001,78)因为外键约束fk_sid，学生表中不存在学号为0100学号的

16、学生信息解决办法一：在学生表中插入一行学号为0100的信息再次执行INSERT 成绩表VALUES (0100,0001,78)方法二：先删除外键fk_sid再插入。使用查询分析器删除成绩表的外键fk_sid。ALTER TABLE 成绩表DROP CONSTRAINT fk_sid四、实验思考1在SQLServer 2005中，可采用哪些方法实现数据完整性?答：有SQL Server管理平台和查询分析器两种方法。2，比较默认对象和默认约束的异同。答：默认值对象与CREATE TABLE 或 ALTER TABLE 语句操作表时用默认约束指定的默认值功能相似，两者的区别类似于规则与检查约束在使用上的区别。默认值对象可以用于多个列或用户定义数据类型。表的一列或一个用户定义数据类型只能与一个默认值相绑定。3.在数据库中建立的规则不绑定到到数据表的字段上会起作用吗？为什么？ 答：不能。因为创建规则后，规则仅仅只是一个存在于数据库中的对象，并未发生作用。需要将规则与数据库表或用户定义对象联系起来，即绑定，才能达到创建规则的目的。