--1. 将stu数据库中student表的sno定义为主键;
alter table student
add constraint pk_sno primary key(sno)
--2. 将数据库stu的表course的cno字段定义为主键,约束名称为cno_pk;
alter table course
add constraint cno_pk primary key(cno)
--3. 为表course中的字段cname添加唯一值约束;
alter table course
add constraint uq_cname unique(cname)
--4. 将数据库stu的表sc的sno及cno字段组合定义为主键,约束名称为sc_pk;
alter table sc
add constraint sc_pk primary key(Sno,Cno)
--5. 对于数据表sc的sno、cno字段定义为外码,使之与表student的主码sno及表course的主码cno对应,实现如下参照完整性:
--1) 删除student表中记录的同时删除sc表中与该记录sno字段值相同的记录;
--2) 修改student表某记录的sno时,若sc表中与该字段值对应的有若干条记录,则拒绝修改;
--3) 修改course表cno字段值时,该字段在sc表中的对应值也应修改;
--4) 删除course表一条记录时,若该字段在在sc表中存在,则删除该字段对应的记录;
--5) 向sc表添加记录时,如果该记录的sno字段的值在student中不存在,则拒绝插入;
alter table sc
add constraint fk_sno_student foreign key (Sno)references student(Sno) on delete cascade on update no action
alter table sc
add constraint fk_cno_course foreign key (Cno)references course(Cno)on delete cascade on update cascade
delete from student
where Sno='200515017'
--6. 定义check约束,要求学生学号sno必须为9位数字字符,且不能以0开头,第二三位皆为0;
alter table student
with nocheck
add constraint ck_sno
check ([Sno] like '[1-9]00%s' and len(Sno)=9)
--7. 定义stu数据库中student表中学生年龄值在16-25范围内;
alter table student
with nocheck
add constraint ck_age
check (16<=Sage and Sage<=25)
--8. 定义stu数据库中student表中学生姓名长度在2-8之间;
alter table student
with nocheck
add constraint ck_name
check (len(Sname)<=8 and len(Sname)>=2)
--9. 定义stu数据库中student表中学生性别列中只能输入“男”或“女”;
alter table student
with nocheck
add constraint ck_sex
check (Ssex in ('男','女'))
--10. 定义stu数据库student表中学生年龄值默认值为20;
alter table student
add constraint df_age default '20'
for Sage
--11. 修改student表学生的年龄值约束可以为15-30范围内;
alter table student
drop ck_age
alter table student
with nocheck
add constraint ck_age
check (15<=Sage and Sage<=30)
--12. 删除上述唯一值约束、外键约束及check约束;
alter table student
drop constraint ck_age,ck_sex,ck_sno,ck_name
alter table course
drop constraint uq_cname
alter table sc
drop constraint fk_sno_student,fk_cno_course
--13.向sc表中插入或修改一条记录时,通过触发器检查记录学号字段的值在student表中是否存在,同时还要检查课程号的值是否存在,若不存在,
--则取消插入或修改,否则插入成功;执行对sc的插入、修改操作,验证触发器的执行。
create trigger ck_scno
on sc
for insert,update
as
if not exists(select *
from student
where Sno=(
select Sno
from inserted
)
) or not exists(select *
from course
where Cno=(
select Cno
from inserted
)
)
rollback transaction
--14. 在student表中增加一列total,表示学生的总学分,初始值为0。定义一个触发器,实现有关学分的完整性约束:
--当向SC表插入一行选课记录时,若成绩大于等于60分,自动将该课程的学分累加到学生的总学分中。
alter table student
add total float
create trigger tr_xf
on sc
for insert
as
if((select Grade from inserted)>60)
begin
update student
set total=total+(select Ccredit from inserted,course where inserted.Cno=course.Cno)
where Sno=(select Sno from inserted)
end
insert sc
values('200515001','10','99')
--15.设计一触发器,约束数据库系统课程的课容量为120。
create trigger tr_db
on sc
for insert
as
if((select count(*) from sc,course where sc.Cno=course.Cno and Cname='数据库系统')>120)
rollback transaction
--16.设有两个表:商品库存表(商品编号,商品名称,库存数量,库存单价,库存金额);
--商品销售表(商品编号,商品名称,购货商号,销售数量,销售单价,销售金额);
--设计一触发器实现如下业务规则:
--(1)保证在商品库存表中插入的数据,库存金额 = 库存数量 * 库存单价。
--(2)如果销售的商品不存在库存或者库存为零,则返回提示信息。否则自动减少商品库存表中对应商品的库存数量和库存金额。
--建表并验证触发器的执行。
create table store(
商品编号 char(10),
商品名称 char(10),
库存数量 int,
库存单价 float,
库存金额 float
)
create table sale(
商品编号 char(10),
商品名称 char(10),
购货商号 char(10),
销售数量 int,
销售单价 float,
销售金额 float
)
create trigger tr_st
on store
for insert
as
if (((select 库存数量 from inserted)*(select 库存单价 from inserted))!=(select 库存金额 from inserted))
rollback transaction
insert store
values('1','a','1','2','3')
insert store
values('1','a','1','2','2')
create trigger tr_sa
on sale
for insert
as
if ((select 库存数量 from store where store.商品编号=(select 商品编号 from inserted))=0)
begin
print '库存不足'
rollback transaction
end
else
begin
update store
set 库存数量=库存数量-(select 销售数量 from inserted)
where store.商品编号=(select 商品编号 from inserted)
update store
set 库存金额=库存数量*库存单价
where store.商品编号=(select 商品编号 from inserted)
end
insert sale
values('1','a','1','1','1','1')
--17.选做题:设计实例,验证after触发器与instead of触发器的异同。
--向student表插入数据时,检查学号是否存在于student表中,如不存在则进行插入操作,否则就不插入。
create trigger tr_test1
on student
instead of insert
as
if not exists(select * from student where Sno=(select Sno from inserted))
begin
insert into student select * from inserted
print '成功插入!'
end
else
begin
rollback transaction
print '插入失败!'
end
create trigger tr_test2
on student
for insert
as
if exists(select * from student where Sno=(select Sno from inserted))
begin
rollback transaction
print '插入失败!'
end