/*==============================================================*/
/* DBMS name:      Microsoft SQL Server 2012                    */
/* Created on:     2021/5/7 20:58:39                            */
/*==============================================================*/


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('Orders') and o.name = 'FK_ORDERS_REFERENCE_MOVIE')
alter table Orders
   drop constraint FK_ORDERS_REFERENCE_MOVIE
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('Orders') and o.name = 'FK_ORDERS_REFERENCE_USER')
alter table Orders
   drop constraint FK_ORDERS_REFERENCE_USER
go

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

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

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

/*==============================================================*/
/* Table: Movie                                                 */
/*==============================================================*/
create table Movie (
   Code                 int                  identity,
   MName                char(20)             null,
   Direct               char(20)             null,
   Actor                char(20)             null,
   Address              varchar(20)          null,
   Year                 date                 null,
   Price                decimal(6,2)         null,
   constraint PK_MOVIE primary key (Code)
)
go

/*==============================================================*/
/* Table: Orders                                                */
/*==============================================================*/
create table Orders (
   OCode                int                  identity,
   Code                 int                  null,
   Time                 date                 null,
   Price                decimal(6,2)         null,
   Iden                 int                  null,
   State                nvarchar(2)          null
      constraint CKC_STATE_ORDERS check (State is null or (State in ('',''))),
   constraint PK_ORDERS primary key (OCode)
)
go

/*==============================================================*/
/* Table: "User"                                                */
/*==============================================================*/
create table "User" (
   Iden                 int                  identity,
   UName                char(20)             null,
   UPwd                 char(20)             null,
   Account              char(20)             null,
   Money                char(20)             null,
   constraint PK_USER primary key (Iden)
)
go

alter table Orders
   add constraint FK_ORDERS_REFERENCE_MOVIE foreign key (Code)
      references Movie (Code)
go

alter table Orders
   add constraint FK_ORDERS_REFERENCE_USER foreign key (Iden)
      references "User" (Iden)
go