内蒙古科技大学课程设计说明书
3.3创建约束
3.3.1 创建Check约束
alter table tourist –-check约束，只能为男女 add constraint CK_tourist_sex check(tourist_sex='男' or tourist_sex='女'); alter table staff add constraint CK_staff_sex check(staff_sex='男' or staff_sex='女'); alter table tourist –-check约束，身份证号不能大于18位 add constraint CK_tourist_num check(tourist_num<999999999999999999); alter table hotel –-check约束，星级不能大于五位数 add constraint CK_hotel_star check(hotel_star<99999);
3.3.2 创建唯一约束
alter table tour –-唯一约束，团名称不能重复 add constraint UQ_group_name UNIQUE(group_name); 21
内蒙古科技大学课程设计说明书
3.3.3 创建外键
alter table tour add constraint route_id foreign key (route_id) references route(route_id); add constraint staff2_id foreign key (staff2_id) references staff(staff_id); alter table tour alter table hotel add constraint tourist_id foreign key (tourist_id) references tourist(tourist_id); alter table route add constraint group_id foreign key (group_id) references tour(group_id); alter table serve add alter table serve add constraint staff_id foreign key (staff_id) references staff(staff_id); constraint tourist1_id foreign key (tourist1_id) references tourist(tourist_id); alter table ticket add constraint tourist2_id foreign key (tourist2_id) references tourist(tourist_id); alter table tourist constraint group1_id foreign key (group1_id) references tour(group_id); alter table tourist add add constraint staff1_id foreign key (staff1_id) references staff(staff_id);
3.4 创建视图
3.4.1 单表视图
22
内蒙古科技大学课程设计说明书
创建名为view_tourist的单表视图
[travel agency] usego create view view_tourist as select tourist.tourist_id,tourist.tourist_name,tourist.tourist_num,tourist.tour