实验八：数据库的完整性约束

一、实验目的

1.    掌握主键约束、外键约束及check约束的用法；

2.    掌握默认值约束和默认值对象的应用；

3.    掌握用触发器实现参照完整性的方法。

二、实验学时

2学时

三、实验要求

1.    了解约束、默认值；

2.    了解触发器的定义方法；

3.    掌握触发器的编写和运行方法

4.    完成实验报告。

四、实验内容

1. 数据完整性。以实验数据库为基础数据，请使用T-SQL语句完成以下内容，并将SQL语句写在实验报告册中：

1)    将数据库stu的表course的cno字段定义为主键，约束名称为cno_pk;

alter table XSKC.course

add constraint cno_pk primary key(cno);

2)    为表course中的字段cname添加唯一值约束；

alter table XSKC.course

add constraint C1_2 unique(cname);

3)    对于数据表sc的sno、cno字段定义为外码，使之与表student的主码sno及表course的主码cno对应，实现如下参照完整性：

    删除student表中记录的同时删除sc表中与该记录sno字段值相同的记录；

    修改student表某记录的sno时，若sc表中与该字段值对应的有若干条记录，则拒绝修改；

    修改course表cno字段值时，该字段在sc表中的对应值也应修改；

    删除course表一条记录时，若该字段在在sc表中存在，则删除该字段对应的记录；

    向sc表添加记录时，如果该记录的sno字段的值在student中不存在，则拒绝插入；

alter table XSKC.sc

add constraint FK1_3 foreign key(sno) references XSKC.student(sno)

on delete cascade

on update no action,

foreign key(cno) references XSKC.course(cno)

on update cascade

on delete cascade

问题1:

在实验5  15题为了按照cno排序不出现问题

我们将其数据类型改为了int

现在需要将其更改回char.

问题2:

这是由于数据库中已经有数据导致的

现在有两种方法

1:重建数据库,最后加入数据

2:使用界面方式,勾选“在创建或重新启动时检查现有数据”否！！！

  这里我演示第二种方法

首先将FK1_3分为FK1_3_1和FK1_3_2

分别创建

步骤如图:

4）定义check约束，要求学生学号sno必须为9位数字字符，且不能以0开头，第二三位皆为0；

alter table XSKC.student

add constraint C1_4

check (sno like '[1-9]00[0-9][0-9][0-9][0-9][0-9][0-9]')

5）定义stu数据库中student表中学生年龄值在16~25范围内；

alter table XSKC.student

add constraint C1_5

check (sage between 16 and 25)

*问题:

由于朱小鸥原来为30岁,需要把她的年龄改为16~25岁,该约束才能成功添加;

6）定义stu数据库student表中学生年龄值默认值为20；

alter table XSKC.student

add constraint C1_6

default 20 for sage;

7）  修改student表学生的年龄值约束可以为15~30范围内；

alter table XSKC.student

drop constraint C1_5;

alter table XSKC.student

add constraint C1_7

check (sage between 15 and 30)

*问题:

1_5中为了成功执行约束语句我们更改了朱小鸥的年龄,

现在我们可以把朱小鸥年龄改回30岁;

8）  删除表course中字段cname的唯一值约束；

alter table XSKC.course

drop constraint C1_2;

2. 以实验数据库为基础数据，编写以下触发器并测试。

1) 为表course建立一个 INSERT触发器，当插入的新行中课程学分(ccredit)的值不是1~6时，就激活该出发器，撤销该插入操作，并使用RAISERROR语句返回一个错误信息。

create trigger insert_ccredit 

on XSKC.course for insert 

as 

declare @pr_ccredit char(4) 

select @pr_ccredit=ccredit from inserted 

if @pr_ccredit>7 or @pr_ccredit<1 

begin 

    raiserror('不能插入',16,10) 

    rollback tran   

end 

2）为course表再创建一个UPDATE触发器，当更新了某门课程的课程号信息时，就激活该触发器级联更新sc表中相关的课程号信息，并使用PRINT语句返回一个提示信息。

create trigger update_cno on XSKC.course 

after update 

as 

begin 

    declare @old_cno char(9),@new_cno char(9) 

    select @old_cno=cno from deleted 

    select @new_cno=cno from inserted 

    update sc set cno=@new_cno where cno=@old_cno 

end 

3）为student表创建DELECT触发器，在删除中的一条记录的同时将sc表中的相应记录也删除。

create trigger delete_sno 

on XSKC.student 

after delete 

as 

begin 

    delete from XSKC.sc  

    where sno in( 

    select sno  

    from deleted) 

end 

4）创建INSTEAD OF触发器，当向sc表中插入记录时，先检查sno列上的值在student中是否存在，如果存在执行插入操作，如果不存在则提示“该学生不存在”。

create trigger insert_sc_sno on XSKC.sc 

instead of 

insert  

as 

declare @pr_sno char(9) 

select @pr_sno = sno from inserted 

if @pr_sno not in(select sno from XSKC.student) 

begin 

    raiserror('该学生不存在！',6,10) 

    rollback tran 

end 

5）比较约束与触发器的执行顺序。(在一个表中创建CHECK约束和触发器,然后向表中插入一条不符合约束和触发器的记录,察看谁先发生作用。)

alter table XSKC.sc 

add constraint grade_ck 

check (grade between 0 and 100) 

 

insert XSKC.sc 

values('200515066','01',101)