数据库实验8

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

参考自：https://blog.csdn.net/qq_43127132/article/details/90177902

实验内容

注：以下所有用表都基于XSKC模式

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 cname_pk 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 sc_fk 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  

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

alter table XSKC.student  
add constraint sno_ck  
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 sage_ck  
check(sage between 16 and 25)

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

alter table XSKC.student  
add constraint sage_df  
default 20 for sage

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

alter table XSKC.student  
drop constraint sage_ck   
alter table XSKC.student  
add constraint sage_ck  
check(sage between 15 and 30)  

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

alter table XSKC.course  
drop constraint cname_pk  

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)  

可以看出，约束条件比触发器先发生作用