1、创建、删除约束
–在选修数据库中,以Student(sno,sname,ssex,sage,sdept),Course(cno,cname,cpno,credit),SC(sno,cno,grade)表为基础完成下列完整性约束。
–(1)为数据库表Course创建check约束:当插入或修改一个记录时,确保学分为1-8之间。
alter table course add constraint c1 check(credit between 1 and 8 );
1
–(2)为数据库表Student创建check约束:当插入或修改一个记录时,确保女学生年龄<30,确保男学生年龄<40。
alter table student add constraint s1 check((ssex='男' and sage<40) or (ssex='女' and sage<30) );
1
–(3)为数据库表SC创建check约束:当插入或修改一个记录时,确保成绩为0-100之间。
alter table sc add constraint sc1 check(grade between 0 and 100);
1
–(4)为数据库表SC中sno,cno创建外键约束。
alter table sc add constraint fk_sc_sno foreign key(sno) references student (sno) ;
alter table sc add constraint fk_sc_cno foreign key(cno) references course (cno) ;
1
2
–2、创建、删除触发器
–在选修数据库中,以Student(sno,sname,ssex,sage,sdept),Course(cno,cname,cpno,credit),SC(sno,cno,grade)表为基础完成下列触发器。
–(1)为数据库表SC创建一个触发器:当插入或修改一个记录时,确保此记录的成绩为0-100之间。
create trigger tr_sc1 on sc for insert,update as
declare @sc_grade tinyint
select @sc_grade=grade FROM inserted
IF @sc_grade not between 0 and 100
BEGIN
PRINT '成绩超出0~100!请重新输入。'
Rollback Transaction
END
1
2
3
4
5
6
7
8
–(2)为数据库表Student创建一个触发器:当插入或修改一个记录时,确保女学生年龄<30,确保男学生年龄<40。
create trigger tr_s on student for insert,update as
declare @s_age int ,@s_sex char
select @s_age=sage FROM inserted
select @s_sex=ssex FROM inserted
IF (@s_sex='女' and @s_age<30) or (@s_sex='男' and @s_age<40)
commit
else
BEGIN
PRINT '数据不合法.'
rollback transaction
END
1
2
3
4
5
6
7
8
9
10
11
–(3)为数据库表Student、SC、Course创建参照完整性,级联删除和级联修改触发器。
//级联删除
create trigger DC_SC
on course for delete
as
declare @cno_del char
select @cno_del=cno from deleted
delete from sc where cno=@cno_del
1
2
3
4
5
6
create trigger DS_SC
on student for delete
as
declare @sno_del char
select @sno_del=sno from deleted
delete from sc where sno=@sno_del
1
2
3
4
5
6
//级联更新
create trigger UC_SC
on course for update
as
declare @cno_old char,@cno_new char
select @cno_old=cno from deleted
select @cno_new=cno from inserted
update sc set cno=@cno_new where cno=@cno_old
1
2
3
4
5
6
7
create trigger US_SC
on student for update
as
declare @sno_old char,@sno_new char
select @sno_old=sno from deleted
select @sno_new=sno from inserted
update sc set sno=@sno_new where sno=@sno_old
1
2
3
4
5
6
7