药品销售管理系统01.sql

药店GMP管理（带毕业论文） 勉强可以用啦

/*==============================================================*/
/* DBMS name:      Sybase AS Anywhere 7                         */
/* Created on:     2007-07-09 21:47:12                          */
/*==============================================================*/


if exists(select 1 from sys.sysforeignkey where role='FK_JINYAO_JINYAO_YAODIAN') then
    alter table jinyao
       delete foreign key FK_JINYAO_JINYAO_YAODIAN
end if;

if exists(select 1 from sys.sysforeignkey where role='FK_JINYAO_JINYAO_YAOPIN') then
    alter table jinyao
       delete foreign key FK_JINYAO_JINYAO_YAOPIN
end if;

if exists(select 1 from sys.sysforeignkey where role='FK_KUCUN_YONGYOU_YAODIAN') then
    alter table kucun
       delete foreign key FK_KUCUN_YONGYOU_YAODIAN
end if;

if exists(select 1 from sys.sysforeignkey where role='FK_KUCUN_YONGYOU_YAOPIN') then
    alter table kucun
       delete foreign key FK_KUCUN_YONGYOU_YAOPIN
end if;

if exists(select 1 from sys.sysforeignkey where role='FK_MAIYAO_MAIYAO_YAODIAN') then
    alter table maiyao
       delete foreign key FK_MAIYAO_MAIYAO_YAODIAN
end if;

if exists(select 1 from sys.sysforeignkey where role='FK_MAIYAO_MAIYAO_YAOPIN') then
    alter table maiyao
       delete foreign key FK_MAIYAO_MAIYAO_YAOPIN
end if;

if exists(
   select 1 from sys.sysindex i, sys.systable t
   where i.table_id=t.table_id 
     and i.index_name='jinyao_FK'
     and t.table_name='jinyao'
) then
   drop index jinyao.jinyao_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='jinyao_FK2'
     and t.table_name='jinyao'
) then
   drop index jinyao.jinyao_FK2
end if;

if exists(
   select 1 from sys.sysindex i, sys.systable t
   where i.table_id=t.table_id 
     and i.index_name='maiyao_FK'
     and t.table_name='maiyao'
) then
   drop index maiyao.maiyao_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='maiyao_FK2'
     and t.table_name='maiyao'
) then
   drop index maiyao.maiyao_FK2
end if;

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

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

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

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

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

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

/*==============================================================*/
/* Table: jinyao                                                */
/*==============================================================*/
create table jinyao 
(
    jy_id                char(8)                        not null,
    yp_id                char(8)                        not null,
    yd_id                char(3)                        not null,
    jy_shuliang          int,
    jy_zongjia           money,
    jy_date              datetime,
    jy_beizhu            varchar(50),
    primary key (jy_id, yp_id, yd_id)
);

/*==============================================================*/
/* Index: jinyao_FK                                             */
/*==============================================================*/
create  index jinyao_FK on jinyao (
yp_id ASC
);

/*==============================================================*/
/* Index: jinyao_FK2                                            */
/*==============================================================*/
create  index jinyao_FK2 on jinyao (
yd_id ASC
);

/*==============================================================*/
/* Table: kucun                                                 */
/*==============================================================*/
create table kucun 
(
    yp_id                char(8)                        not null,
    yd_id                char(3)                        not null,
    kc_shuliang          int,
    kc_min               int,
    kc_zongjia           money,
    kc_beizhu            varchar(50),
    primary key (yp_id, yd_id)
);

/*==============================================================*/
/* Table: maiyao                                                */
/*==============================================================*/
create table maiyao 
(
    my_id                char(8)                        not null,
    yp_id                char(8)                        not null,
    yd_id                char(3)                        not null,
    my_shuliang          int,
    my_zongjia           money,
    my_date              datetime,
    my_beizhu            varchar(50),
    primary key (my_id, yp_id, yd_id)
);

/*==============================================================*/
/* Index: maiyao_FK                                             */
/*==============================================================*/
create  index maiyao_FK on maiyao (
yp_id ASC
);

/*==============================================================*/
/* Index: maiyao_FK2                                            */
/*==============================================================*/
create  index maiyao_FK2 on maiyao (
yd_id ASC
);

/*==============================================================*/
/* Table: yaodian                                               */
/*==============================================================*/
create table yaodian 
(
    yd_id                char(3)                        not null,
    yd_name              varchar(20),
    yd_address           varchar(50),
    primary key (yd_id)
);

/*==============================================================*/
/* Table: yaopin                                                */
/*==============================================================*/
create table yaopin 
(
    yp_id                char(8)                        not null,
    yp_name              varchar(20),
    yp_type              varchar(20),
    yp_jiage             numeric(8,2),
    yp_scriqi            datetime,
    yp_scchangjia        varchar(30),
    yp_youxiaoqiz        datetime,
    yp_beizhu            varchar(50),
    primary key (yp_id)
);

/*==============================================================*/
/* Table: yonghu                                                */
/*==============================================================*/
create table yonghu 
(
    user_id              char(3)                        not null,
    user_type            varchar(10),
    user_name            varchar(10),
    user_password        varchar(15),
    primary key (user_id)
);

alter table jinyao
   add foreign key FK_JINYAO_JINYAO_YAODIAN (yd_id)
      references yaodian (yd_id)
      on update restrict
      on delete restrict;

alter table jinyao
   add foreign key FK_JINYAO_JINYAO_YAOPIN (yp_id)
      references yaopin (yp_id)
      on update restrict
      on delete restrict;

alter table kucun
   add foreign key FK_KUCUN_YONGYOU_YAODIAN (yd_id)
      references yaodian (yd_id)
      on update restrict
      on delete restrict;

alter table kucun
   add foreign key FK_KUCUN_YONGYOU_YAOPIN (yp_id)
      references yaopin (yp_id)
      on update restrict
      on delete restrict;

alter table maiyao
   add foreign key FK_MAIYAO_MAIYAO_YAODIAN (yd_id)
      references yaodian (yd_id)
      on update restrict
      on delete restrict;

alter table maiyao
   add foreign key FK_MAIYAO_MAIYAO_YAOPIN (yp_id)
      references yaopin (yp_id)
      on update restrict
      on delete restrict;