创建数据库练习

最新推荐文章于 2023-03-21 12:23:44 发布

那抹绯红乀

最新推荐文章于 2023-03-21 12:23:44 发布

阅读量264

点赞数

本文链接：https://blog.csdn.net/qq_36074056/article/details/53978784

版权

/*==============================================================*/
/* DBMS name:      Microsoft SQL Server 2005                    */
/* Created on:     2017/1/2 14:57:38                            */
/*==============================================================*/


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

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

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

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

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

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

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

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

/*==============================================================*/
/* Table: Book                                                  */
/*==============================================================*/
create table Book (
   BID                  int                  not null,
   Pen_BID              int                  null,
   PubDate              nvarchar(0)          null,
   BCount               nvarchar(0)          null,
   BName                int                  null,
   Author               nvarchar(0)          null,
   PubComp              numeric(0,0)         null,
   Price                int                  null,
   constraint PK_BOOK primary key (BID)
)
go

/*==============================================================*/
/* Table: Borrow                                                */
/*==============================================================*/
create table Borrow (
   BID                  int                  not null,
   RID                  int                  null,
   ReturnDate           numeric(0)           null,
   WillDate             nvarchar(0)          null,
   LendDate             nvarchar(0)          null,
   constraint PK_BORROW primary key (BID)
)
go

/*==============================================================*/
/* Table: Penalty                                               */
/*==============================================================*/
create table Penalty (
   BID                  int                  not null,
   RID                  int                  null,
   PType                numeric(0,0)         null,
   PDate                numeric(0,0)         null,
   Amount               char(10)             null,
   constraint PK_PENALTY primary key (BID)
)
go

/*==============================================================*/
/* Table: Reader                                                */
/*==============================================================*/
create table Reader (
   RID                  int                  not null,
   BID                  int                  null,
   Bor_BID              int                  null,
   RName                numeric(0,0)         null,
   LendNum              int                  null,
   constraint PK_READER primary key (RID)
)
go

alter table Book
   add constraint FK_BOOK_REFERENCE_BORROW foreign key (BID)
      references Borrow (BID)
go

alter table Book
   add constraint FK_BOOK_REFERENCE_PENALTY foreign key (Pen_BID)
      references Penalty (BID)
go

alter table Reader
   add constraint FK_READER_REFERENCE_PENALTY foreign key (BID)
      references Penalty (BID)
go

alter table Reader
   add constraint FK_READER_REFERENCE_BORROW foreign key (Bor_BID)
      references Borrow (BID)
go