bankpd.sql

来自「内容涉及PowerDesign概述」· SQL 代码 · 共 236 行

/*==============================================================*/
/* DBMS name:      Microsoft SQL Server 2000                    */
/* Created on:     2008-9-9 11:04:27                            */
/*==============================================================*/


if exists (select 1
   from dbo.sysreferences r join dbo.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('fetchType') and o.name = 'FK_FETCHTYP_SAVETYPE__SAVETYPE')
alter table fetchType
   drop constraint FK_FETCHTYP_SAVETYPE__SAVETYPE
go

if exists (select 1
   from dbo.sysreferences r join dbo.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('rate') and o.name = 'FK_RATE_RATE_SAVE_SAVETIME')
alter table rate
   drop constraint FK_RATE_RATE_SAVE_SAVETIME
go

if exists (select 1
   from dbo.sysreferences r join dbo.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('rate') and o.name = 'FK_RATE_SAVETYPE__SAVETYPE')
alter table rate
   drop constraint FK_RATE_SAVETYPE__SAVETYPE
go

if exists (select 1
   from dbo.sysreferences r join dbo.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('savetime') and o.name = 'FK_SAVETIME_RATE_SAVE_RATE')
alter table savetime
   drop constraint FK_SAVETIME_RATE_SAVE_RATE
go

if exists (select 1
   from dbo.sysreferences r join dbo.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('savetime') and o.name = 'FK_SAVETIME_SAVETYPE__SAVETYPE')
alter table savetime
   drop constraint FK_SAVETIME_SAVETYPE__SAVETYPE
go

if exists (select 1
            from  sysindexes
           where  id    = object_id('fetchType')
            and   name  = 'savetype_fetchtype_FK'
            and   indid > 0
            and   indid < 255)
   drop index fetchType.savetype_fetchtype_FK
go

if exists (select 1
            from  sysobjects
           where  id = object_id('fetchType')
            and   type = 'U')
   drop table fetchType
go

if exists (select 1
            from  sysindexes
           where  id    = object_id('rate')
            and   name  = 'rate_savetime_FK'
            and   indid > 0
            and   indid < 255)
   drop index rate.rate_savetime_FK
go

if exists (select 1
            from  sysindexes
           where  id    = object_id('rate')
            and   name  = 'savetype_rate_FK'
            and   indid > 0
            and   indid < 255)
   drop index rate.savetype_rate_FK
go

if exists (select 1
            from  sysobjects
           where  id = object_id('rate')
            and   type = 'U')
   drop table rate
go

if exists (select 1
            from  sysindexes
           where  id    = object_id('savetime')
            and   name  = 'rate_savetime2_FK'
            and   indid > 0
            and   indid < 255)
   drop index savetime.rate_savetime2_FK
go

if exists (select 1
            from  sysindexes
           where  id    = object_id('savetime')
            and   name  = 'savetype_savetime_FK'
            and   indid > 0
            and   indid < 255)
   drop index savetime.savetype_savetime_FK
go

if exists (select 1
            from  sysobjects
           where  id = object_id('savetime')
            and   type = 'U')
   drop table savetime
go

if exists (select 1
            from  sysobjects
           where  id = object_id('savetype')
            and   type = 'U')
   drop table savetype
go

/*==============================================================*/
/* Table: fetchType                                             */
/*==============================================================*/
create table fetchType (
   fetchId              bigint               not null,
   sav_typeId           bigint               null,
   typeId               bigint               not null,
   fetchTime            varchar(20)          null,
   constraint PK_FETCHTYPE primary key nonclustered (fetchId)
)
go

/*==============================================================*/
/* Index: savetype_fetchtype_FK                                 */
/*==============================================================*/
create index savetype_fetchtype_FK on fetchType (
sav_typeId ASC
)
go

/*==============================================================*/
/* Table: rate                                                  */
/*==============================================================*/
create table rate (
   id                   bigint               not null,
   sav_typeId           bigint               null,
   sav_saveId           bigint               null,
   beginTime            datetime             not null,
   endTime              datetime             not null,
   typeId               bigint               not null,
   saveId               bigint               not null,
   yearRate             float(20)            not null,
   changeTime           datetime             not null,
   workerId             bigint               not null,
   constraint PK_RATE primary key nonclustered (id)
)
go

/*==============================================================*/
/* Index: savetype_rate_FK                                      */
/*==============================================================*/
create index savetype_rate_FK on rate (
sav_typeId ASC
)
go

/*==============================================================*/
/* Index: rate_savetime_FK                                      */
/*==============================================================*/
create index rate_savetime_FK on rate (
sav_saveId ASC
)
go

/*==============================================================*/
/* Table: savetime                                              */
/*==============================================================*/
create table savetime (
   saveId               bigint               not null,
   sav_typeId           bigint               null,
   id                   bigint               null,
   typeId               bigint               not null,
   saveTime             varchar(20)          null,
   constraint PK_SAVETIME primary key nonclustered (saveId)
)
go

/*==============================================================*/
/* Index: savetype_savetime_FK                                  */
/*==============================================================*/
create index savetype_savetime_FK on savetime (
sav_typeId ASC
)
go

/*==============================================================*/
/* Index: rate_savetime2_FK                                     */
/*==============================================================*/
create index rate_savetime2_FK on savetime (
id ASC
)
go

/*==============================================================*/
/* Table: savetype                                              */
/*==============================================================*/
create table savetype (
   typeId               bigint               not null,
   typeName             varchar(20)          not null,
   lowMoney             int                  null,
   saveType             int                  null,
   fetchType            int                  null,
   constraint PK_SAVETYPE primary key nonclustered (typeId)
)
go

alter table fetchType
   add constraint FK_FETCHTYP_SAVETYPE__SAVETYPE foreign key (sav_typeId)
      references savetype (typeId)
go

alter table rate
   add constraint FK_RATE_RATE_SAVE_SAVETIME foreign key (sav_saveId)
      references savetime (saveId)
go

alter table rate
   add constraint FK_RATE_SAVETYPE__SAVETYPE foreign key (sav_typeId)
      references savetype (typeId)
go

alter table savetime
   add constraint FK_SAVETIME_RATE_SAVE_RATE foreign key (id)
      references rate (id)
go

alter table savetime
   add constraint FK_SAVETIME_SAVETYPE__SAVETYPE foreign key (sav_typeId)
      references savetype (typeId)
go