实验4-数据完整性.doc

实验4 SQL Server 2000数据完整性
一、 实验目的
1. 理解完整性的概念、类型及作用
2. 使用约束、默认值等方法实现数据完整性
3. 掌握应用企业管理器设置、修改和删除常见完整性控制的方法。
4. 掌握应用T-SQL设置、修改和删除常见约束的方法。
二、 实验要求
1. 在“电子工厂”数据库eshop中创建和使用约束、默认值。
2. 分别用T-SQL和企业管理器两种方法创建和使用约束、默认值。
三、 实验步骤
1. 打开企业管理器，附加“电子工厂”数据库eshop。

2. 使用修改表命令将职工表的“家庭住址”列设置为非空，体会NOT NULL约束的使用。
先用系统存储过程sp_help显示职工表的结构。在查询分析器中输入以下T-SQL代码：
USE eshop
GO
sp_help 职工表
按F5或点击工具栏上的运行按钮“”，查看结果。在查询分析器窗口应看到如下所示的信息：

在查询分析器中输入更新职工表的SQL代码：
ALTER TABLE 职工表 ALTER COLUMN 家庭住址 VARCHAR(30) NOT NULL
再按F5或点击工具栏上的运行按钮“”，运行以上更新数据表的SQL代码。

然后输入插入记录到职工表的SQL代码：
INSERT INTO 职工表
VALUES('000888','翁红','男', '汉族','1970-04-20','党员',NULL,'67758111',5500.0,NULL,NULL)
执行此段SQL插入记录代码后，将在结果框中显示以下消息：
服务器: 消息 515，级别 16，状态 2，行 1
无法将 NULL 值插入列 '家庭住址'，表 'eshop.dbo.职工表'；该列不允许空值。INSERT 失败。
语句已终止。
表示执行插入操作失败。

3. 在职工表中，将“工号”设置为主键，体会PRIMARY KEY约束的作用。
首先，在查询分析器中输入更新职工表的SQL代码：
ALTER TABLE 职工表 ALTER COLUMN 工号 CHAR(6) NOT NULL
GO
ALTER TABLE 职工表 ADD CONSTRAINT pk_members PRIMARY KEY(工号)
GO
按F5或点击工具栏上的运行按钮“”，运行更新数据表的SQL代码。

然后输入插入记录到职工表的SQL代码：
INSERT INTO 职工表
VALUES('000066','翁红','男', '汉族','1970-04-20','党员', '郑州','67758111',5500.0,NULL,NULL)
执行此段SQL插入记录代码后，将在结果框中显示以下消息：
服务器: 消息 2627，级别 14，状态 1，行 1
违反了 PRIMARY KEY 约束 'pk_members'。不能在对象 '职工表' 中插入重复键。
语句已终止。
表示执行插入操作失败。

4. 在职工表中，为“姓名”列增加UNIQUE约束，体会UNIQUE约束的作用。
首先，在查询分析器中输入更新数据职工表的SQL代码：
ALTER TABLE 职工表 ADD CONSTRAINT un_name UNIQUE(姓名)
再按F5或点击工具栏上的运行按钮“”，运行更新数据表的SQL代码后，将在结果框中显示以下消息：
服务器: 消息 1505，级别 16，状态 1，行 1
CREATE UNIQUE INDEX 终止，因为发现了索引 ID 7 的重复键。最重要的主键为 '张磊 00'。
服务器: 消息 1750，级别 16，状态 1，行 1
未能创建约束。请参阅前面的错误信息。
语句已终止。
表示由于有两个姓名为“张磊”的职工，故不能创建UNIQUE约束。
将工号为000039的姓名（张磊）改为“张小磊”：
select * from 职工表
go
UPDATE 职工表 SET 姓名='张小磊' WHERE 工号='000039'
go
select * from 职工表 where 工号='000039'
go

然后再运行SQL代码：
ALTER TABLE 职工表 ADD CONSTRAINT un_name UNIQUE(姓名)
这样便可成功地为职工表的姓名字段添加唯一性约束。

然后输入插入记录到职工表的SQL代码：
INSERT INTO 职工表
VALUES('000120','乔瑞','男', '汉族','1970-04-20','党员', '郑州','67758111',5500.0,NULL,NULL)
执行此段SQL插入记录代码后，将在结果框中显示以下消息：
服务器: 消息 2627，级别 14，状态 2，行 1
违反了 UNIQUE KEY 约束 'un_name'。不能在对象 '职工表' 中插入重复键。
语句已终止。
表示执行插入操作失败。

5. 在职工表中，增加三个CHECK约束，并体会CHECK的约束的作用。
（1） 性别只能为“男”或“女”；
（2） 年龄在15~60岁之间；
（3） 基本工资在1000到9999元之间。
首先，在查询分析器中依次输入三条更新职工表的SQL代码：
ALTER TABLE 职工表
ADD CONSTRAINT ck_sex CHECK(性别 IN('男', '女'))

ALTER TABLE 职工表
ADD CONSTRAINT ck_age CHECK( (YEAR(GETDATE())-YEAR(出生日期)) BETWEEN 15 AND 60)

ALTER TABLE 职工表
ADD CONSTRAINT ck_salery CHECK( 基本工资 BETWEEN 1000 AND 9999)

再按F5或点击工具栏上的运行按钮“”，运行更新数据表的SQL代码。
然后输入第一条插入记录到数据职工表的SQL代码：
INSERT INTO 职工表
VALUES('000120','乔华','M', '汉族','1970-04-20','党员', '郑州','67758111',5500.0,NULL,NULL)
执行此段SQL插入记录代码后，将在结果框中显示以下消息，表示执行插入操作失败（违反了性别约束）：
服务器: 消息 547，级别 16，状态 1，行 1
INSERT 语句与 COLUMN CHECK 约束 'ck_sex' 冲突。该冲突发生于数据库 'eshop'，表 '职工表', column '性别'。
语句已终止。

输入第二条插入记录到数据表members的SQL代码：
INSERT INTO 职工表
VALUES('000120','乔华','男', '汉族','1870-04-20','党员', '郑州','67758111',5500.0,NULL,NULL)
执行此段SQL插入记录代码后，将在结果框中显示以下消息，表示执行插入操作失败（违反了年龄约束）：
服务器: 消息 547，级别 16，状态 1，行 1
INSERT 语句与 COLUMN CHECK 约束 'ck_age' 冲突。该冲突发生于数据库 'eshop'，表 '职工表', column '出生日期'。
语句已终止。

输入第三条插入记录到数据表members的SQL代码：
INSERT INTO 职工表
VALUES('000120','乔华','男', '汉族','1970-04-20','党员', '郑州','67758111',850.0,NULL,NULL)
执行此段SQL插入记录代码后，将在结果框中显示以下消息，表示执行插入操作失败（违反了基本工资约束）：
服务器: 消息 547，级别 16，状态 1，行 1
INSERT 语句与 COLUMN CHECK 约束 'ck_salery' 冲突。该冲突发生于数据库 'eshop'，表 '职工表', column '基本工资'。
语句已终止。

6. 在职工表和生产表之间创建关系，并将生产表的工号设置为外键，体会FOREIGN KEY约束的作用。
在查询分析器中输入更新生产表的SQL代码：
ALTER TABLE 生产表
ADD CONSTRAINT fk_account FOREIGN KEY(工号) REFERENCES 职工表(工号)
然后，输入一条插入记录到生产表的SQL代码：
INSERT INTO 生产表 VALUES('000444','2-007',100)
执行此段SQL插入记录代码后，将在结果框中显示以下消息，表示执行插入操作失败：
服务器: 消息 547，级别 16，状态 1，行 1
INSERT 语句与 COLUMN FOREIGN KEY 约束 'fk_account' 冲突。该冲突发生于数据库 'eshop'，表 '职工表', column '工号'。
语句已终止。

7. 为职工表中的“性别”列设置默认值为“男”，体会DEFAULT约束的作用。
首先，在查询分析器中输入更新职工表、生成默认值和绑定默认值的SQL代码：
ALTER TABLE 职工表 ALTER COLUMN 性别 CHAR(2) NOT NULL
go
create default sex_male AS '男'
go
sp_bindefault sex_male,'职工表.性别'
go
然后，输入插入记录到职工表的SQL代码和查询代码如下：
INSERT INTO 职工表(工号,姓名,性别,家庭住址,基本工资) VALUES('000444','张利强',default,'河南省郑州市',8888)
GO
SELECT * FROM 职工表 where 工号='000444'
GO
8. 再次显示职工表的结构
sp_help 职工表
查询分析器窗口中将显示职工表的所有信息，包括字段定义、主键、外键、约束、默认值等，如下图所示。

请仔细观察查询分析器窗口中所给出的全部信息。
9. 依次（反向）删除实验步骤2~7中所创建的全部约束、默认值和所插入的记录。
DELETE 职工表 where 工号='000444'
GO
sp_unbindefault '职工表.性别'
drop default sex_male
ALTER TABLE 职工表 ALTER COLUMN 性别 CHAR(2) NULL
GO

ALTER TABLE 生产表 DROP CONSTRAINT fk_account
GO

ALTER TABLE 职工表 DROP CONSTRAINT ck_sex
ALTER TABLE 职工表 DROP CONSTRAINT ck_age
ALTER TABLE 职工表 DROP CONSTRAINT ck_salery
GO

ALTER TABLE 职工表 DROP CONSTRAINT un_name
UPDATE 职工表 set 姓名='张磊' where 工号='000039'
GO

ALTER TABLE 职工表 DROP CONSTRAINT pk_members
ALTER TABLE 职工表 ALTER COLUMN 工号 CHAR(6) NULL
GO

ALTER TABLE 职工表 ALTER COLUMN 家庭住址 VARCHAR(30) NULL
GO

再用“sp_help 职工表”显示职工表的信息，此时结果如下所示：

10. 在企业管理器中完成步骤2-9，并与T-SQL操作进行比较。