crebas.sql

powerbuild开发实例！涉及学生管理系统！

/*==============================================================*/
/* DBMS name:      Sybase AS Anywhere 9                         */
/* Created on:     2008-6-16 23:14:09                           */
/*==============================================================*/


if exists(select 1 from sys.sysforeignkey where role='FK_指选_RELATIONS_指选课程计划') then
    alter table 指选
       delete foreign key FK_指选_RELATIONS_指选课程计划
end if;

if exists(select 1 from sys.sysforeignkey where role='FK_指选_RELATIONS_班级') then
    alter table 指选
       delete foreign key FK_指选_RELATIONS_班级
end if;

if exists(select 1 from sys.sysforeignkey where role='FK_指选课程计划_课程计划2_课程计划') then
    alter table 指选课程计划
       delete foreign key FK_指选课程计划_课程计划2_课程计划
end if;

if exists(select 1 from sys.sysforeignkey where role='FK_教学部_归属_组织机构') then
    alter table 教学部
       delete foreign key FK_教学部_归属_组织机构
end if;

if exists(select 1 from sys.sysforeignkey where role='FK_教师_用户_用户') then
    alter table 教师
       delete foreign key FK_教师_用户_用户
end if;

if exists(select 1 from sys.sysforeignkey where role='FK_课程_RELATIONS_教学部') then
    alter table 课程
       delete foreign key FK_课程_RELATIONS_教学部
end if;

if exists(select 1 from sys.sysforeignkey where role='FK_课程计划_RELATIONS_教师') then
    alter table 课程计划
       delete foreign key FK_课程计划_RELATIONS_教师
end if;

if exists(select 1 from sys.sysforeignkey where role='FK_课程计划_RELATIONS_课程') then
    alter table 课程计划
       delete foreign key FK_课程计划_RELATIONS_课程
end if;

if exists(select 1 from sys.sysforeignkey where role='FK_选修_RELATIONS_选修课程计划') then
    alter table 选修
       delete foreign key FK_选修_RELATIONS_选修课程计划
end if;

if exists(select 1 from sys.sysforeignkey where role='FK_选修_RELATIONS_用户') then
    alter table 选修
       delete foreign key FK_选修_RELATIONS_用户
end if;

if exists(select 1 from sys.sysforeignkey where role='FK_选修课程计划_课程计划_课程计划') then
    alter table 选修课程计划
       delete foreign key FK_选修课程计划_课程计划_课程计划
end if;

if exists(
   select 1 from sys.sysindex i, sys.systable t
   where i.table_id=t.table_id 
     and i.index_name='Relationship_4_FK'
     and t.table_name='指选'
) then
   drop index 指选.Relationship_4_FK
end if;

if exists(
   select 1 from sys.sysindex i, sys.systable t
   where i.table_id=t.table_id 
     and i.index_name='Relationship_7_FK'
     and t.table_name='指选'
) then
   drop index 指选.Relationship_7_FK
end if;

if exists(
   select 1 from sys.sysindex i, sys.systable t
   where i.table_id=t.table_id 
     and i.index_name='指选_PK'
     and t.table_name='指选'
) then
   drop index 指选.指选_PK
end if;

if exists(
   select 1 from sys.sysindex i, sys.systable t
   where i.table_id=t.table_id 
     and i.index_name='指选课程计划_PK'
     and t.table_name='指选课程计划'
) then
   drop index 指选课程计划.指选课程计划_PK
end if;

if exists(
   select 1 from sys.sysindex i, sys.systable t
   where i.table_id=t.table_id 
     and i.index_name='教学部_PK'
     and t.table_name='教学部'
) then
   drop index 教学部.教学部_PK
end if;

if exists(
   select 1 from sys.sysindex i, sys.systable t
   where i.table_id=t.table_id 
     and i.index_name='教师_PK'
     and t.table_name='教师'
) then
   drop index 教师.教师_PK
end if;

if exists(
   select 1 from sys.sysindex i, sys.systable t
   where i.table_id=t.table_id 
     and i.index_name='班级_PK'
     and t.table_name='班级'
) then
   drop index 班级.班级_PK
end if;

if exists(
   select 1 from sys.sysindex i, sys.systable t
   where i.table_id=t.table_id 
     and i.index_name='用户_PK'
     and t.table_name='用户'
) then
   drop index 用户.用户_PK
end if;

if exists(
   select 1 from sys.sysindex i, sys.systable t
   where i.table_id=t.table_id 
     and i.index_name='组织机构_PK'
     and t.table_name='组织机构'
) then
   drop index 组织机构.组织机构_PK
end if;

if exists(
   select 1 from sys.sysindex i, sys.systable t
   where i.table_id=t.table_id 
     and i.index_name='Relationship_6_FK'
     and t.table_name='课程'
) then
   drop index 课程.Relationship_6_FK
end if;

if exists(
   select 1 from sys.sysindex i, sys.systable t
   where i.table_id=t.table_id 
     and i.index_name='课程_PK'
     and t.table_name='课程'
) then
   drop index 课程.课程_PK
end if;

if exists(
   select 1 from sys.sysindex i, sys.systable t
   where i.table_id=t.table_id 
     and i.index_name='Relationship_10_FK'
     and t.table_name='课程计划'
) then
   drop index 课程计划.Relationship_10_FK
end if;

if exists(
   select 1 from sys.sysindex i, sys.systable t
   where i.table_id=t.table_id 
     and i.index_name='Relationship_9_FK'
     and t.table_name='课程计划'
) then
   drop index 课程计划.Relationship_9_FK
end if;

if exists(
   select 1 from sys.sysindex i, sys.systable t
   where i.table_id=t.table_id 
     and i.index_name='课程计划_PK'
     and t.table_name='课程计划'
) then
   drop index 课程计划.课程计划_PK
end if;

if exists(
   select 1 from sys.sysindex i, sys.systable t
   where i.table_id=t.table_id 
     and i.index_name='Relationship_5_FK'
     and t.table_name='选修'
) then
   drop index 选修.Relationship_5_FK
end if;

if exists(
   select 1 from sys.sysindex i, sys.systable t
   where i.table_id=t.table_id 
     and i.index_name='Relationship_8_FK'
     and t.table_name='选修'
) then
   drop index 选修.Relationship_8_FK
end if;

if exists(
   select 1 from sys.sysindex i, sys.systable t
   where i.table_id=t.table_id 
     and i.index_name='选修_PK'
     and t.table_name='选修'
) then
   drop index 选修.选修_PK
end if;

if exists(
   select 1 from sys.sysindex i, sys.systable t
   where i.table_id=t.table_id 
     and i.index_name='选修课程计划_PK'
     and t.table_name='选修课程计划'
) then
   drop index 选修课程计划.选修课程计划_PK
end if;

if exists(
   select 1 from sys.systable 
   where table_name='指选'
     and table_type in ('BASE', 'GBL TEMP')
) then
    drop table 指选
end if;

if exists(
   select 1 from sys.systable 
   where table_name='指选课程计划'
     and table_type in ('BASE', 'GBL TEMP')
) then
    drop table 指选课程计划
end if;

if exists(
   select 1 from sys.systable 
   where table_name='教学部'
     and table_type in ('BASE', 'GBL TEMP')
) then
    drop table 教学部
end if;

if exists(
   select 1 from sys.systable 
   where table_name='教师'
     and table_type in ('BASE', 'GBL TEMP')
) then
    drop table 教师
end if;

if exists(
   select 1 from sys.systable 
   where table_name='班级'
     and table_type in ('BASE', 'GBL TEMP')
) then
    drop table 班级
end if;

if exists(
   select 1 from sys.systable 
   where table_name='用户'
     and table_type in ('BASE', 'GBL TEMP')
) then
    drop table 用户
end if;

if exists(
   select 1 from sys.systable 
   where table_name='组织机构'
     and table_type in ('BASE', 'GBL TEMP')
) then
    drop table 组织机构
end if;

if exists(
   select 1 from sys.systable 
   where table_name='课程'
     and table_type in ('BASE', 'GBL TEMP')
) then
    drop table 课程
end if;

if exists(
   select 1 from sys.systable 
   where table_name='课程计划'
     and table_type in ('BASE', 'GBL TEMP')
) then
    drop table 课程计划
end if;

if exists(
   select 1 from sys.systable 
   where table_name='选修'
     and table_type in ('BASE', 'GBL TEMP')
) then
    drop table 选修
end if;

if exists(
   select 1 from sys.systable 
   where table_name='选修课程计划'
     and table_type in ('BASE', 'GBL TEMP')
) then
    drop table 选修课程计划
end if;

/*==============================================================*/
/* Table: 指选                                                    */
/*==============================================================*/
create table 指选 
(
    计划编号                 varchar(20)                    not null,
    班级号                  varchar(20)                    not null,
    constraint PK_指选 primary key clustered (计划编号, 班级号)
);

/*==============================================================*/