创建数据库练习2

最新推荐文章于 2025-01-15 19:59:21 发布
那抹绯红乀
最新推荐文章于 2025-01-15 19:59:21 发布
阅读量354
点赞数
CC 4.0 BY-SA版权
本文链接：https://blog.youkuaiyun.com/qq_36074056/article/details/53978807
/*==============================================================*/
/* DBMS name:      Microsoft SQL Server 2005                    */
/* Created on:     2017/1/2 14:59:15                            */
/*==============================================================*/


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('CuestRecord') and o.name = 'FK_CUESTREC_REFERENCE_ROOM')
alter table CuestRecord
   drop constraint FK_CUESTREC_REFERENCE_ROOM
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('Room') and o.name = 'FK_ROOM_REFERENCE_ROOMSTAT')
alter table Room
   drop constraint FK_ROOM_REFERENCE_ROOMSTAT
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('Room') and o.name = 'FK_ROOM_REFERENCE_ROOMTYPE')
alter table Room
   drop constraint FK_ROOM_REFERENCE_ROOMTYPE
go

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

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

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

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

/*==============================================================*/
/* Table: CuestRecord                                           */
/*==============================================================*/
create table CuestRecord (
   CuestID              int                  not null,
   RoomID               int                  null,
   CuestName            numeric(0)           null,
   ResideDate           nvarchar(0)          null,
   LeaveDate            nvarchar(Max)        null,
   Deposit              float                null,
   TotaMoney            float                null,
   constraint PK_CUESTRECORD primary key (CuestID)
)
go

/*==============================================================*/
/* Table: Room                                                  */
/*==============================================================*/
create table Room (
   CuestID              int                  not null,
   RoomStateID          int                  null,
   TypeID               int                  null,
   RoomID               int                  null,
   Description          nvarchar(Max)        null,
   BedNum               int                  null,
   CuestNum             int                  null,
   RoomState            varchar(Max)         null,
   RoomType             nvarchar(0)          null,
   Price                int                  null,
   constraint PK_ROOM primary key (CuestID)
)
go

/*==============================================================*/
/* Table: RoomState                                             */
/*==============================================================*/
create table RoomState (
   RoomStateID          int                  not null,
   RoomStateName        int                  null,
   constraint PK_ROOMSTATE primary key (RoomStateID)
)
go

/*==============================================================*/
/* Table: RoomType                                              */
/*==============================================================*/
create table RoomType (
   TypeID               int                  not null,
   TypeName             nvarchar(0)          null,
   TypePrice            nvarchar(0)          null,
   BedNum               int                  null,
   constraint PK_ROOMTYPE primary key (TypeID)
)
go

alter table CuestRecord
   add constraint FK_CUESTREC_REFERENCE_ROOM foreign key (CuestID)
      references Room (CuestID)
go

alter table Room
   add constraint FK_ROOM_REFERENCE_ROOMSTAT foreign key (RoomStateID)
      references RoomState (RoomStateID)
go

alter table Room
   add constraint FK_ROOM_REFERENCE_ROOMTYPE foreign key (TypeID)
      references RoomType (TypeID)
go