实验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 eshopGOsp_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 NULLGOALTER 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，行 1CREATE UNIQUE INDEX 终止，因为发现了索引 ID 7 的重复键。最重要的主键为 张磊 00。服务器: 消息 1750，级别 16，状态 1，行 1未能创建约束。请参阅前面的错误信息。语句已终止。表示由于有两个姓名为“张磊”的职工，故不能创建UNIQUE约束。将工号为000039的姓名（张磊）改为“张小磊”：select * from 职工表goUPDATE 职工表 SET 姓名=张小磊 WHERE 工号=000039goselect * from 职工表 where 工号=000039go然后再运行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） 年龄在1560岁之间；（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，行 1INSERT 语句与 COLUMN CHECK 约束 ck_sex 冲突。该冲突发生于数据库 eshop，表 职工表, column 性别。语句已终止。输入第二条插入记录到数据表members的SQL代码：INSERT INTO 职工表 VALUES(000120,乔华,男, 汉族,1870-04-20,党员, 郑州,67758111,5500.0,NULL,NULL)执行此段SQL插入记录代码后，将在结果框中显示以下消息，表示执行插入操作失败（违反了年龄约束）：服务器: 消息 547，级别 16，状态 1，行 1INSERT 语句与 COLUMN CHECK 约束 ck_age 冲突。该冲突发生于数据库 eshop，表 职工表, column 出生日期。语句已终止。输入第三条插入记录到数据表members的SQL代码：INSERT INTO 职工表 VALUES(000120,乔华,男, 汉族,1970-04-20,党员, 郑州,67758111,850.0,NULL,NULL)执行此段SQL插入记录代码后，将在结果框中显示以下消息，表示执行插入操作失败（违反了基本工资约束）：服务器: 消息 547，级别 16，状态 1，行 1INSERT 语句与 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，行 1INSERT 语句与 COLUMN FOREIGN KEY 约束 fk_account 冲突。该冲突发生于数据库 eshop，表 职工表, column 工号。语句已终止。7. 为职工表中的“性别”列设置默认值为“男”，体会DEFAULT约束的作用。 首先，在查询分析器中输入更新职工表、生成默认值和绑定默认值的SQL代码：ALTER TABLE 职工表 ALTER COLUMN 性别 CHAR(2) NOT NULLgocreate default sex_male AS 男gosp_bindefault sex_male,职工表.性别 go然后，输入插入记录到职工表的SQL代码和查询代码如下：INSERT INTO 职工表(工号,姓名,性别,家庭住址,基本工资) VALUES(000444,张利强,default,河南省郑州市,8888)GOSELECT * FROM 职工表 where 工号=000444GO8. 再次显示职工表的结构sp_help 职工表查询分析器窗口中将显示职工表的所有信息，包括字段定义、主键、外键、约束、默认值等，如下图所示。 请仔细观察查询分析器窗口中所给出的全部信息。9. 依次（反向）删除实验步骤27中所创建的全部约束、默认值和所插入的记录。DELETE 职工表 where 工号=000444GOsp_unbindefault 职工表.性别drop default sex_maleALTER TABLE 职工表 ALTER COLUMN 性别 CHAR(2) NULLGOALTER TABLE 生产表 DROP CONSTRAINT fk_accountGOALTER TABLE 职工表 DROP CONSTRAINT ck_sexALTER TABLE 职工表 DROP CONSTRAINT ck_ageALTER TABLE 职工表 DROP CONSTRAINT ck_saleryGOALTER TABLE 职工表 DROP CONSTRAINT un_nameUPDATE 职工表 set 姓名=张磊 where 工号=000039GOALTER TABLE 职工表 DROP CONSTRAINT pk_membersALTER TABLE 职工表 ALTER COLUMN 工号 CHAR(6) NULLGOALTER TABLE 职工表 ALTER COLUMN 家庭住址 VARCHAR(30) NULLGO再用“sp_help 职工表”显示职工表的信息，此时结果如下所示：10. 在企业管理器中完成步骤2-9，并与T-SQL操作进行比较。