主题：关于VFP数据库中几种数据约束的实现

数据库的约束 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