SQL数据完整性.docx

SQL数据完整性.docx

《SQL数据完整性.docx》由会员分享，可在线阅读，更多相关《SQL数据完整性.docx（16页珍藏版）》请在冰点文库上搜索。

SQL数据完整性

实验6数据完整性

一、实验目的

1．掌握Transact-SQL语句（CREATERULE、DROPRULE）创建和删除规则的方法。

2．掌握系统存储过程sp_bindrule、sp_unbindrule绑定和解除绑定规则的操作方法，以及sp_help、sp_helptext查询规则信息、sp_rename更名规则的方法。

3．掌握Transact-SQL语句（CREATEDEFAULT、DROPDEFAULT）创建和删除默认对象的方法。

4．掌握系统存储过程sp_bindefault、sp_unbindefault绑定和解除绑定默认对象的操作方法，以及sp_helptext查询规则信息。

5．掌握SQLServer管理平台和Transact-SQL语句（CREATETABLE、ALTERTABLE）定义和删除约束的方法，并了解约束的类型。

二、实验内容和步骤

1．为studentsdb数据库创建一个规则，限制所输入的数据为7位0～9的数字。

（1）复制学生表命名为stu_phone，在stu_phone表中插入一列，列名为“电话号码”。

完成以下代码实现该操作。

SELECT*INTOstu_phoneFROM学生表

ALTERTABLEstu_phoneADD电话号码CHAR（7）NULL

stu_phone表结构如图1-1所示。

图1-1stu_phone表结构

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

CREATERULEphone_ruleAS@电话号码like'[0-9][0-9][0-9][0-9][0-9][0-9][0-9]'

（3）使用系统存储过程sp_hndrule将phone_rule规则绑定到stu_phone表的“电话号码”列上。

sp_bindrulephone_rule,'stu_phone.电话号码'

（4）插入操作输入以下数据：

学号姓名电话号码

0009王国强1234yyy

是否可以成功插入？

如果出现错误，请列出错误信息，为什么会产生该出错信息?

如果要实现插入操作，应修改INSERTINTO语句中的哪个值?

phone_rule规则能否对其他操作（如DELETE）进行规则检查?

答：

、插入语句如下：

INSERTINTOstu_phone（学号,姓名,电话号码）

VALUES（'0009','王国强','1234yyy'）

、插入出错，错误信息如下：

消息513，级别16，状态0，第1行

列的插入或更新与先前的CREATERULE语句所指定的规则发生冲突。

该语句已终止。

冲突发生于数据库'studentsdb'，表'dbo.stu_phone'，列'电话号码'。

语句已终止。

、出现错误的原因：

在于电话号码的输入不对，因为电话号码绑定的规则是7位0-9的数字。

、修改：

将电话号码中的字母改为数字即可，代码如下

INSERTINTOstu_phone（学号,姓名,电话号码）

VALUES（'0009','王国强','1234567'）

执行结果如下：

、不能，代码如下：

deletefromstu_phone

where电话号码='1234567'

结果如下：

2．创建一个规则stusex_rule，将其绑定到stu_phone表的“性别”列上，保证输入的性别值只能是“男”或“女”。

CREATERULESTUSEX_RULE

AS@SEXIN（'男','女'）

EXECSP_BINDRULESTUSEX_RULE,'STU_PHONE.性别'

3．使用系统存储过程sp_help查询stusex_rule规则列表。

EXECUTESP_HELPSTUSEX_RULE

结果截图如下：

使用sp_helptext查询stusex_rule规则的文本。

EXECUTESP_HELPTEXTSTUSEX_RULE

结果截图如下：

使用sp_rename将stusex_rule规则更名为stu_s_rule。

EXECUTESP_RENAMESTUSEX_RULE,STU_S_RULE

结果截图如下：

4．删除stu_s_rule规则。

EXECSP_UNBINDRULE'STU_PHONE.性别'

用DROPRULESTU_S_RULE语句进行删除，

截图如下：

5．在studentdb数据库中，建立日期、货币和字符等数据类型的默认对象。

（1）创建默认对象df_date、df_char、df_money。

、创建日期型默认对象df_date，默认日期为2006-4-12。

CREATEDEFAULTDF_DATEAS'2006-4-12'

结果截图如下：

、创建字符型默认对象df_char，默认字符为“unknown”

CREATEDEFAULTDF_CHARAS'UNKNOWN'

结果截图如下：

、创建货币型默认对象df_money，默认为100元

代码如下：

CREATEDEFAULTDF_MONEYAS'RMB:

100'

结果截图如下：

（2）在studentsdb数据库中创建stu_fee数据表。

学号char（10）NOTNULL

姓名char（8）NOTNULL，

学费money

交费日期datetime

电话号码char（7）

CREATETABLESTU_FEE

（

学号CHAR（10）NOTNULL,

姓名CHAR（8）NOTNULL,

学费MONEY,

缴费日期DATETIME,

电话号码CHAR（7）

）

结果截图如下：

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

EXECSP_BINDEFAULTDF_DATE,'STU_FEE.缴费日期'

EXECSP_BINDEFAULTDF_CHAR,'STU_FEE.电话号码'

EXECSP_BINDEFAULTDF_MONEY,'STU_FEE.学费'

结果截图如下：

（4）输入命令，在stu_fee表进行插入操作:

（学号，姓名）values（'0001'，’刘卫平’）

（学号，姓名，学费）values（'0001'，’张卫民’,$120）

（学号，姓名，学费，交费日期）values（'0001'，‘马东‘，'$110’,'2006-5-12'）

INSERTSTU_FEE

（学号,姓名）VALUES（'0001','刘卫平'）

INSERTSTU_FEE

（学号,姓名,学费）VALUES（'0002','张卫民',$120）

INSERTSTU_FEE

（学号,姓名,学费,缴费日期）VALUES（'0003','马东',$110,'2006-5-12'）

结果截图如下：

（5）完成以下代码解除默认对象df_char、df_date、df_money的绑定，并删除之。

、解除绑定

EXECSP_UNBINDEFAULT'STU_FEE.缴费日期'

EXECSP_UNBINDEFAULT'STU_FEE.电话号码'

EXECSP_UNBINDEFAULT'STU_FEE.学费'

、删除

DROPDEFAULTDF_DATE,DF_CHAR,DF_MONEY

结果截图如下：

6．为学生表添加一列，命名为“院系”，

ALTERTABLE学生表ADD院系CHAR（16）

结果截图如下：

创建一个默认对象stu_d_df，将其绑定到学生表的“院系”列上，

CREATEDEFAULTSTU_D_DFAS'信息院'

EXECSP_BINDEFAULTSTU_D_DF,'学生表.院系'

使其默认值为“信息院”，对学生表进行插入操作，

INSERT学生表（学号,姓名）VALUES（'0009','王国强'）

结果截图如下：

7．在studentsdb数据库中用CREATETABLE语句创建表stu_con，并同时创建约束。

（1）创建表的同时创建约束。

表结构如图1-12所示。

图1-12要创建的表的结构

约束要求如下：

①将学号设置为主键（PRIMARYKEY），主键名为pk_sid。

②为姓名添加唯一约束（UNIQUE），约束名为uk_name。

③为性别添加默认约束（DEFAULT），默认名称为df_sex，其值为“男”

④为出生日期添加属性值约束（CHECK），约束名为ck_bday，其检查条件为：

出生日期>'1988-1-1’。

CREATETABLESTU_CON

（

学号CHAR（4）NOTNULL

CONSTRAINTPK_SIDPRIMARYKEY（学号）,

姓名CHAR（8）NOTNULL

CONSTRAINTUK_NAMEUNIQUE,

性别CHAR

（2）

CONSTRAINTDF_SEXDEFAULT（'男'）,

出生日期DATETIME

CONSTRAINTCK_BDAYCHECK（出生日期>'1988-1-1'）,

家庭住址VARCHAR（50）

）

结果截图如下：

（2）在stu_con表中插入如表1-1所示的数据记录。

在插入0010的数据时出现错误截图如下：

约束CK_BDAY的作用是使插入的数据中出生日期项都小于“1988-1-1”

在插入0012的数据记录时出现错误截图如下：

约束UK_NAME的作用是使插入的数据姓名项没有重复的内容

经修改完全插入后截图如下：

可发现，除自行修改外，表中数据与所插入的数据也不全相同。

（3）使用ALTERTABLE语句的DROPCONSTRAINT参数项在查询分析器中删除为stu_con表所建的约束。

ALTERTABLESTU_CON

DROPCONSTRAINTPK_SID,UK_NAME,DF_SEX,CK_BDAY

结果截图如下：

8、用SQLServer管理平台完成实验内容7的所有设置

①将学号设置为主键（PRIMARYKEY），主键名为pk_sid。

②为姓名添加唯一约束（UNIQUE），约束名为uk_name。

③为性别添加默认约束（DEFAULT），默认名称为df_sex，其值为“男”

④为出生日期添加属性值约束（CHECK），约束名为ck_bday，其检查条件为：

出生日期>'1988-1-1’。

9、在查询分析器中，为studentsdb数据库的成绩表添加外键约束（FOREIGNKEY），要求将“学号”设置为外键，参照表为学生表，外键名称为fk_sid。

altertable成绩表addconstraintfk_sid

foreignkey（学号）references学生表（学号）

⑴使用系统存储过程sp_help查看grade表的外键信息。

sp_helpfk_sid

⑵在成绩表中插入表1-2所示记录，观察SQLServer会做何处理，为什么?

如何解决所产生的问题?

表1-2

学号

课程编号

分数

0100

0001

78

INSERT成绩表VALUES（'0100','0001','78'）

因为外键约束fk_sid，学生表中不存在学号为0100学号的学生信息

解决办法一：

在学生表中插入一行学号为0100的信息

再次执行INSERT成绩表VALUES（'0100','0001','78'）

方法二：

先删除外键fk_sid再插入。

⑶使用查询分析器删除成绩表的外键fk_sid。

ALTERTABLE成绩表

DROPCONSTRAINTfk_sid

四、实验思考

1．在SQLServer2005中，可采用哪些方法实现数据完整性?

答：

有SQLServer管理平台和查询分析器两种方法。

2，比较默认对象和默认约束的异同。

答：

默认值对象与CREATETABLE或ALTERTABLE语句操作表时用默认约束指定的默认值功能相似，两者的区别类似于规则与检查约束在使用上的区别。

默认值对象可以用于多个列或用户定义数据类型。

表的一列或一个用户定义数据类型只能与一个默认值相绑定。

3.在数据库中建立的规则不绑定到到数据表的字段上会起作用吗？

为什么？

答：

不能。

因为创建规则后，规则仅仅只是一个存在于数据库中的对象，并未发生作用。

需要将规则与数据库表或用户定义对象联系起来，即绑定，才能达到创建规则的目的。