实验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 定义和 删除约束的方法 并了解约束的类型 2 2 实验内容及步骤实验内容及步骤 1 为 studentsdb 数据库创建一个规则 限制所输入的数据为 7 位 0 9 的数字 复制 student info 表命名为 stu phone 在 stu phone 表中插入一列 列名为 电话号码 完成以下代码实现该操作 SELECT INTO stu phone FROM student info ALTER TABLE stu phone ADD CHAR 7 NULL stu phone 表结构如图 1 10 所示 图 1 10 stu phone 表结构 创建一个规则 phone rule 限制所输入的数据为 7 位 0 9 的数字 实现该规则的 代码为 CREATE phone rule AS phone LIKE 0 9 0 9 0 9 0 9 0 9 0 9 0 9 使用系统存储过程 sp bindrule 将 phone rule 规则绑定到 stu phone 表的 电话 号码 列上 实现该操作的代码为 sp bindrule stu phone 电话号码 输入以下代码 进行一次插入操作 INSERT INTO stu phone 学号 姓名 电话号码 VALUES 0009 王国强 1234yyy 产生以下出错信息 消息 513 级别 16 状态 0 第 1 行 列的插入或更新与先前的 CREATE RULE 语句所指定的规则发生冲突 该语句已终止 冲突发生于 数据库 studentsdb 表 dbo stu phone 列 电话号码 语句已终止 试分析 为什么会产生该出错信息 如果要实现插入操作 应修改 INSERT INTO 语句 中的哪个值 phone rule 规则能否对其他操作 如 DELETE 进行规则检查 2 创建一个规则 stusex rule 将其绑定到 stu phone 表的 性别 列上 保证输 入的性别值只能是 男 或 女 3 使用系统存储过程 sp help 查询 stusex rule 规则列表 使用 sp helptext 查询 stusex rule 规则的文本 使用 sp rename 将 stusex rule 规则更名为 stu s rule 4 删除 stu s rule 规则 stu s rule stusex rule 更名后规则名 是否仍然绑定在 stu phone 表的 性 别 列上 应如何操作才能删除它 5 在 studentdb 数据库中 建立日期 货币和字符等数据类型的默认对象 在查询设计器中 完成以下代码 创建默认对象 df date df char df money 创建日期型默认对象 df date CREATE df date AS 2009 4 12 GO 创建字符型默认对象 df char CREATE DEFAULT df char unknown GO 创建货币型默认对象 df money CREATE DEFAULT AS 100 GO 输入以下代码 在 studentsdb 数据库中创建 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 的数据结构 使用系统存储过程 sp bindefault 将默认对象 df money df date df char 分别 绑定在 stu fee 表的 学费 交费日期 电话号码 列上 df money stu fee 学费 GO sp bindefault stu fee 交费日期 GO sp bindefault df char stu fee 电话号码 GO 输入以下代码 在 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 表中插入记录的各列的值是什么 完成以下代码 解除默认对象 df char 的绑定 并删除之 stu fee 电话号码 DEFAULT df char 按同样的方式 删除默认对象 df date df money 6 为 student info 表添加一列 命名为 院系 创建一个默认对象 stu d df 将其绑定到 student info 表的 院系 列上 使其默认值为 信息院 对 student info 表进行插入操作 操作完成后 删除该默认对象 7 在 studentsdb 数据库中用 CREATE TABLE 语句创建表 stu con 并同时创建约束 创建表的同时创建约束 表结构如图 1 12 所示 图 1 12 要创建的表的结构 约束要求如下 将学号设置为主键 PRIMARY KEY 主键名为 pk sid 为姓名添加唯一约束 UNIQUE 约束名为 uk name 为性别添加默认约束 DEFAULT 默认名称为 df sex 其值为 男 为出生日期添加属性值约束 CHECK 约束名为 ck bday 其检查条件为 出生日期 1988 1 1 在 stu con 表中插入如表 1 1 所示的数据记录 表 1 1 在 stu con 表中插入的数据 学号姓名性别出生日期家庭住址 0009 张小东 1989 4 6 0010 李梅女 1983 8 5 0011 王强 1988 9 10 0012 王强 1989 6 3 分析各约束在插入记录时所起的作用 查看插入记录后表中数据与所插入的数据是否 一致 使用 ALTER TABLE 语句的 DROP CONSTRAINT 参数项在查询设计器中删除为 stu con 表所建的约束 8 用 SQL Server 管理平台完成实验内容 7 的所有设置 9 在查询设计器中 为 studentsdb 数据库的 grade 表添加外键约束 FOREIGN KEY 要求将 学号 设置为外键 参照表为 student info 外键名称为 fk sid 使用系统存储过程 sp help 查看 grade 表的外键信息 在 grade 表中插入一条记录 学号为 0100 课程编号为 0001 分数为 78 观察 SQL Server 会做何处理 为什么 如何解决所产生的问题 使用查询设计器删除 grade 表的外键 fk sid 三 实验结果三 实验结果 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 表结构 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 3 使用系统存储过程 sp bindrule 将 phone rule 规则绑定到 stu phone 表的 电话号码 列上 sp bindrule phone rule stu phone 电话号码 4 输入以下代码 进行一次插入操作 INSERT INTO stu phone 学号 姓名 电话号码 VALUES 0009 王国强 1234yyy 产生以下出错信息 消息 513 级别 16 状态 0 第 1 行 列的插入或更新与先前的 CREATE RULE 语句所指定的规则发生冲突 该语句已终止 冲突 发生于 数据库 studentsdb 表 dbo stu phone 列 电话号码 语句已终止 试分析 为什么会产生该出错信息 如果要实现插入操作 应修改 INSERT INTO 语句中的 哪个值 phone rule 规则能否对其他操作 如 DELETE 进行规则检查 修改 INSERT INTO stu phone 学号 姓名 电话号码 VALUES 0009 王国强 1234567 phone rule 规则可以对其他操作进行规则检查 2 创建一个规则 stusex rule 将其绑定到 stu phone 表的 性别 列上 保证输入的性 别值只能是 男 或 女 create rule stusex rule as 性别 in 男 女 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 规则 drop rule stu s rule 注意 stu s rule 为 stusex rule 更名后规则名 是否仍然绑定在 stu phone 表的 性 别 列上 应如何操作才能删除它 5 在 studentdb 数据库中 建立日期 货币和字符等数据类型的默认对象 1 创建默认对象 df date df char df money 创建日期型默认对象 df date 默认日期为 2006 4 12 创建日期型默认对象 df date CREATE DEFAULT df date AS 2006 4 12 创建字符型默认对象 df char 默认字符为 unknown 创建字符型默认对象 df char CREATE DEFAULT df char as unknown 创建货币型默认对象 df money 默认为 100 元 创建货币型默认对象 df money CREATE DEFAULT df money AS 100 2 输入以下代码 在 studentsdb 数据库中创建 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 bindehult 将默认对象 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 刘卫平 INSERT INTO stu fee 学号 姓名 values 0001 刘卫平 学号 姓名 学费 values 0001 张卫民 120 INSERT INTO stu fee 学号 姓名 学费 values 0001 张卫民 120 学号 姓名 学费 交费日期 values 0001 马东 110 2006 5 12 INSERT INTO stu fee 学号 姓名 学费 交费日期 VALUES 0001 马东 110 2006 5 12 分析 stu fee 表中插入记录的各列的值是什么 5 完成以下代码解除默认对象 df char df date df money 的绑定 并删除之 sp unbindefault stu fee 电话号码 drop DEFAULT df char Go sp unbindefault stu fee 交费日期 drop DEFAULT df date go sp unbindefault stu fee 学费 drop DEFAULT df money 6 为学生表添加一列 命名为 院系 创建一个默认对象 stu d df 将其绑定到学生 表的 院系 列上 使其默认值为 信息院 对学生表进行插入操作 操作完成后 删 除该默认对象 1 alter table 学生表 add 院系 varchar 8 2 create default stu d df as 信息院 go sp bindefault stu d df 学生表 院系 go 3 sp unbindefault 学生表 院系 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 CREATE TABLE stu con 学号 char 4 姓名 char 8 性别 char 2 出生日期 datetime 家庭住址 varchar 50 constraint PK sid PRIMARY KEY 学号 constraint uk name UNIQUE 姓名 constraint ck bday check 出生日期 1988 1 1 alter table stu con add constraint df sex default 男 FOR 性别 2 在 stu con 表中插入如表 1 1 所示的数据记录 表 1 1 在 stu con 表中插入的数据 学号姓名性别出生日期家庭住址 0009 张小东 1989 4 6 0010 李梅女 1983 8 5 0011 王强 1988 9 10 0012 王强 1989 6 3 分析各约束在插入记录时所起的作用 查看插入记录后表中数据与所插入的数据是否一致 INSERT INTO stu con 学号 姓名 出生日期 values 0009 张小东 1989 4 6 INSERT INTO stu con 学号 姓名 出生日期 values 0010 李梅 1983 8 5 消息 547 级别 16 状态 0 第 1 行 INSERT 语句与 CHECK 约束 ck bday 冲突 该冲突发生于数据库 studentsdb 表 dbo stu con