实验4 数据完整性

一、实验目的

理解实体完整性、参照完整性和自定义完整性的概念

理解触发器的概念、定义方法和触发条件。

二、实验示例

1、给employee的emp_no列加上约束，要求员工编号的第一位为E，后面4位为数字

Alter table employee add constraint ck_employee_emp_no check(emp_no like '[E][0-9][0-9][0-9][0-9]')

2、写一个允许用户一次只删除一条记录的触发器。

create trigger tr_emp

on employee for delete as

declare @row_cnt int

select @row_cnt=count(*) from deleted

if @row_cnt>1

  begin

   print '此删除操作可能回删除多条人事表数据!!!'

   rollback transaction

  end

delete from employee

where sex='女'          /*结果：所影响的行数为0*/

三、实验内容与步骤（注意：请设计数据和操作来验证完整性和触发器是否能够正常执行。）

1、给5个表加上合适的主键和外键约束。

alter table 产品名称表

add constraint C1 check(产品编号 like 'g%' or 产品编号 like 'P%' )

insert into 产品名称表(产品编号,产品名称)

values ('g1234','贝多芬')

insert into 产品名称表(产品编号,产品名称)

values ('w1234','贝多芬')

alter table 客户表

add constraint C2 check(客户号 like 'C[0-9][0-9][0-9][0-9]')

alter table 销货明细表

add constraint C3 foreign key(订单号) references 销售主表(订单号)

on delete cascade

on update cascade,

constraint C4 foreign key(产品编号) references 产品名称表(产品编号)

on delete cascade

on update cascade)

alter table 销售主表

add constraint 订单号_C1 check(订单号 like '[0-9][0-9][0-9][0-9][0-9]')

alter table 员工人事表

add constraint 员工编号_C check (员工编号 like 'E[0-9][0-9][0-9][0-9]')

2、给employee表性别列增加约束，使得性别的取值只能是’m’或’f’

alter table 员工人事表

add constraint 员工性别_C check (性别 like 'm' or 性别 like 'F')

3、创建一个employee表的触发器，当有人操作其中数据时，用print语句输出欢迎信息。

create trigger CaoZuo_ShuJu

on 员工人事表

for insert,update,delete

as

begin

print '欢迎操作数据!'

end

4、设置一个针对employee表的触发器，当有人操作该列值时，触发器将自动将该操作者的名称和操作时间记录在日志表(operate_log)内，以便追踪。日志表属性自己设置。(提示：当前操作者的名字保存在系统常量current_user中，当前时间保存在系统常量current_timestamp中)

//创建日志表

create table 日志表(

操作者名称 nvarchar(20) not null,

操作时间 time not null,

primary key(操作者名称,操作时间)

)

create trigger 员工表_触发器

on 员工人事表

after delete,insert,update

as

begin

insert into 日志表

values(current_user,current_timestamp)

end

5、从sales表中删除一个订单信息的时候，自动完成sale_item表中对应的明细的删除。（提示：先删除sales表和sale-item表中的外键关系，否则该触发器无法成功执行。原因是？）

alter table 销货明细表

drop constraint C3

Create trigger 销售主表触发器

on 销售主表

After delete

As

begin

declare @order_no int

Select @order_no=订单号 from deleted

Delete from 销货明细表 where 订单号=@order_no

End

原因是：被参照关系（sales）中删除元组的主码值出现在参照关系（sale_item）的外码中，系统拒绝该操，所以要给出级联（CASCADE）操作的显示定义。

6、针对employee表写一个UPDATE触发器，当插入的薪水的值小于1000时，自动修改为1000。（提示：可用上变量和inserted、deleted表）

create trigger UP_DATE

on 员工人事表

after insert,update

as

begin

declare @员工编号 nvarchar (5)

select @员工编号=员工编号 from inserted where 薪水<1000

update 员工人事表 set 薪水=1000 where 员工编号=@员工编号

end

7、建立sale_statics表用于记录每个员工的目前为止销售总业绩。该表由emp_no和money_saled两列，设置合理地属性数据类型。每当sales表中插入新的销售记录时，如果是新员工，增加销售总额记录，如果已经存在销售记录时，则更新该员工的销售总业绩。（提示：可用上if语句来判断某员工是否存在于sale_statics表中）

create table 销售总业绩表(

员工编号 nvarchar (5) Not null primary key,

销售总金额 numeric(9,2)  Not null

)

insert into 销售总业绩表

select 业务员编号,SUM(订单金额)销售总金额

from 销售主表

group by 业务员编号

create trigger 静态_销售 on 销售主表

after insert

as

begin

declare @业务员编号 nvarchar (5),@订单金额 numeric(9,2)

select @业务员编号=业务员编号 ,@订单金额=订单金额 from inserted

if(@业务员编号 in (select 员工编号 from 销售总业绩表))

update 销售总业绩表 set 销售总金额=销售总金额+@订单金额 where 员工编号=@业务员编号

else

insert into 销售总业绩表(员工编号,销售总金额)

select 业务员编号,订单金额

from inserted

end

drop trigger 静态_销售

insert into 销售主表

values ('10006','C0003','E0014','33000.00','1996-11-10 00:00:00','I000000004')

insert into 销售主表

values ('10005','C0003','E0018','33000.00','1996-11-10 00:00:00','I000000004')

 

第二篇：数据库实验报告- 数据完整性

实验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)定义和删除约束的方法，并了解约束的类型。

二、实验内容和步骤

    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表结构

SELECT * INTO stu_phone FROM 学生表

ALTER TABLE stu_phone ADD  电话号码CHAR(7)NULL

(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]'

检验：

电话号码为8位时无法插入！

电话号码为7位时可以插入！

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

sp_hndrule phone_rule , ‘stu_phone.电话号码’

(4)插入操作输入以下数据：

学号   姓名     电话号码

0009   王国强   1234yyy

是否可以成果插入？如果出现错误，请列出错误信息，为什么会产生该出错信息?如果要实现插入操作，应修改INSERT INTO语句中的哪个值?phone_rule规则能否对其他操作(如DELETE)进行规则检查?

INSERT INTO stu_phone(学号,姓名,电话号码)

VALUES('0009','王国强','1234yyy')

错误信息：消息513，级别16，状态0，第1 行

列的插入或更新与先前的CREATE RULE 语句所指定的规则发生冲突。该语句已终止。冲突发生于数据库'studentsdb'，表'dbo.stu_phone'，列'电话号码'。

语句已终止。

修改后：

INSERT INTO stu_phone(学号,姓名,电话号码)

VALUES('0009','王国强','1234yyy')

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

可以

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

CREATE  rule  stusex_rule

AS

@性别in ('男','女')

sp_bindrule stusex_rule , 'stu_phone.性别'

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规则。

先解除绑定：sp_unbindrule stu_s_rule, 'stu_phone.性别'

再删除规则：drop rule stu_s_rule

注意：stu_s_ rule为stusex_ rule更名后规则名，是否仍然绑定在stu_phone表的“性别”列上，应如何操作才能删除它。

   

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

    (1)创建默认对象df_date、df_char、df_money。

l  创建日期型默认对象df_date，默认日期为20##-4-12。

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

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

CREATE DEFAULT df_date

AS '20##-4-12'

GO

CREATE DEFAULT df_char

AS     'unknown'

GO

CREATE DEFAULT   df_money     

AS $100

GO

(2)在studentsdb数据库中创建stu_fee数据表。

学号       char(10)   NOT NULL

姓名       char(8)    NOT NULL，

学费       money

交费日期   datetime

电话号码   char(7)

表stu_fee的数据结构如图1-11所示。

图l-11  stu_fee的数据结构

CREATE TABLE stu_fee

(学号char(10) NOT NULL,

姓名char（）NOT NULL,

学费money,

交费日期datetime,

电话号码char（））

(3)使用系统存储过程sp_bindefault将默认对象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'，’刘卫平’)

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

(学号，姓名，学费，交费日期) values  ('0001'，‘马东‘，$110，’20##-5-12‘)

分析stu_fee表中插入记录的各列的值是什么?

INSERT INTO stu_fee(学号,姓名) VALUES('0001','刘卫平')

INSERT INTO stu_fee(学号,姓名,学费)VALUES('0001','张卫民',$120)

INSERT INTO stu_fee(学号,姓名,学费,交费日期) VALUES('0001','马东',$110,'20##-5-12')

各列值为：

刘卫平，张卫民的交费日期为默认值

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

sp_unbindefault   'stu_fee.电话号码'

DROP DEFAULT df_char

sp_unbindefault   'stu_fee.交费日期'

DROP DEFAULT df_date

sp_unbindefault   'stu_fee.学费'

DROP DEFAULT df_money

6．为学生表添加一列，命名为“院系”，创建一个默认对象stu_d_df，将其绑定到学生表的“院系”列上，使其默认值为“信息院”，对学生表进行插入操作，操作完成后，删除该默认对象。

SELECT * FROM 学生表

ALTER TABLE 学生表ADD 院系CHAR(8) NULL

CREATE DEFAULT stu_d_df AS '信息院'

sp_bindefault  stu_d_df,'学生表.院系'

INSERT INTO 学生表(学号,姓名) VALUES('3244','郭冰骅')

sp_unbindefault   '学生表.院系'

DROP DEFAULT  stu_d_df

    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’。

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

                     表1-1 在stu_con表中插入的数据

   

分析各约束在插入记录时所起的作用，查看插入记录后表中数据与所插入的数据是否一致?

CREATE TABLE stu_con

 (学号char(4) NOT NULL CONSTRAINT pk_sid PRIMARY KEY  ,

 姓名char(8) NOT NULL  CONSTRAINT uk_name UNIQUE,

 学费money,

 性别char(2) CONSTRAINT df_sex DEFAULT ('男'),

 出生日期datetime CONSTRAINT ck_bday CHECK (出生日期>'1988-1-1'),

 家庭住址char(50))

INSERT INTO stu_con(学号,姓名,出生日期) VALUES('0009','张小东','1989-4-6')

INSERT INTO stu_con(学号,姓名,性别,出生日期)VALUES('0010','李梅','男','1983-8-5')

INSERT INTO stu_con(学号,姓名,出生日期) VALUES('0011','王强','1988-9-10')

INSERT INTO stu_con(学号,姓名,出生日期) VALUES('0012','王强','1989-6-3')

运行结果：

(1 行受影响)

消息547，级别16，状态0，第2 行

INSERT 语句与CHECK 约束"ck_bday"冲突。该冲突发生于数据库"studentsdb"，表"dbo.stu_con", column '出生日期'。

语句已终止。

(1 行受影响)

消息2627，级别14，状态1，第4 行

违反了UNIQUE KEY 约束'uk_name'。不能在对象'dbo.stu_con' 中插入重复键。

语句已终止。

(3)使用ALTER TABLE语句的DROP CONSTRAINT参数项在查询分析器中删除为stu_con表所建的约束。

ALTER TABLE stu_con

DROP CONSTRAINT pk_sid

ALTER TABLE  stu_con

DROP CONSTRAINT uk_name

ALTER TABLE stu_con

DROP CONSTRAINT df_sex

ALTER TABLE  stu_con

DROP CONSTRAINT ck_bday

8．用SQL Server管理平台完成实验内容7的所有设置。

出现问题！只能出现张小东的信息

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

ALTER TABLE 成绩表

ADD CONSTRAINT fk_sid

FOREIGN KEY(学号) references 学生表(学号)

注意：学生表里的学号一定要与成绩表中的学号对应，否则会报错冲突

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

sp_help 成绩表

    ⑵在成绩表中插入表1-2所示记录，观察SQLServer会做何处理，为什么?如何解决所产生的问题?

表1-2

INSERT INTO 成绩表(学号,课程编号,分数) VALUES('0010','0001','78')

出现问题：

消息547，级别16，状态0，第2 行

INSERT 语句与FOREIGN KEY 约束"fk_sid"冲突。该冲突发生于数据库"studentsdb"，表"dbo.学生表", column '学号'。

语句已终止。

解决问题：在学生表中先插入一个学号为0010的学生信息

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

ALTER table 成绩表

DROP CONSTRAINT fk_sid

四、实验思考

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

答：数据类型、主键、外键、默认值、约束与规则

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

答：相同点：

默认对象与默认约束功能相似。默认值可以在没有指定具体数据的列中自动插入数据。默认约束是通过定义列的默认值或使用数据库的默认值对象绑定表的列，以确保在没有为某列指定数据时，来指定列的值。每列只能有一个默认约束一个默认值

不同点：

默认对象与CREAT TABLE 和ALTER TABLE语句一起使用，而默认约束只能用于INSERT语句。

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

    答：不会规则仅仅只是一个存在与数据库中的对象，并未发生作用。