数据库的约束
1、主键约束
create table stu1 (number c(6) primary key, name c(20))
alter table stu add primary key number tag sid
alter table stu drop primary key
2、唯一约束
create table stu2 (number c(6) primary key, name c(20) unique)(其实是候选索引)
alter table stu add unique name tag sname
alter table stu drop unique tag sname
3、check约束
alter table stu alter column height set check height>= 0 .and. height<=4 error "错误的身高!"
alter table stu add column age n(3)
update stu set age=year(date())-year(birthday)
alter table stu set check year(date())-year(birthday)=age error "错误的年龄!"
alter table stu set check val(number)%2=0 .and. sex=.f. .or. val(number)%2=1 .and. sex=.t. error "错误的学号!"
alter table stu alter height drop check
alter table stu drop check
alter table grade alter column course set check left(course,1)="A" .or. left(course,1)="B" .or. left(course,1)="C" error "错误的课程号!"
alter table grade with check add constraint cc check(substring(course,1,1) in ('A','B','C'))(SQL Server)
4、默认值约束
create table stu1(number c(5),height n(4,2) default 1.80)
alter table stu alter height set default 1.80
alter table stu alter height drop default
5、非空约束(空值约束)
create table stu1(number c(5),height n(4,2) null)
alter table stu alter height null
alter table stu alter height not null
6、外键约束(先在stu中建立主键)
alter table grade add foreign key number tag sg reference stu
alter table grade drop foreign key tag sg
7、触发器约束
create trigger on stu for update as height>0
delete trigger on stu for update
新建存储过程modi proc
procedure exec
param h
if h>=3 .or. h<=0
return .f.
else
return .t.
endif
endproc
create trigger on stu for update as exec(height)
注意:插入非法记录可以
主键约束的触发器实现
procedure NumberIsValid
param n
select number from stu where n==number into cursor temp
select temp
if recc()!=0
return .f.
else
return .t.
endif
endproc
外键约束的触发器实现
procedure NumberExisted
param n
select * from stu where n==number into cursor temp
select temp
if recc()==0
return .f.
else
return .t.
endif
endproc
练习:
生日和年龄在不同表之间的对应约束(触发器实现)
要求1:不可以在stu2中插入生日不对应的记录
要求2:在stu1中删除记录,同时能删除对应年龄的记录
要求3:在stu1中修改生日,对应年龄亦能相应修改
create table stu1(number char(4) not null,birthday datetime)
create table stu2(number char(4) not null,age int)
inserttrigger(number,age)
procedure InsertTrigger
param n,a
select * from stu1 where number==n .and. year(date())-year(birthday)<>a into cursor temp
select temp
if recc()!=0
return .f.
else
return .t.
endif
endproc
deletetrigger(number,birthday)
procedure DeleteTrigger
param n,b
delete from stu2 where number==n .and. age=year(date())-year(b)
return .t.
endproc
updatetrigger(number,birthday)
procedure UpdateTrigger
param n,b
update stu2 set age=year(date())-year(b) where number==n
return .t.
endproc