if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[t_book]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[t_book] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[t_category]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[t_category] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[t_company]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[t_company] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[t_dept]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[t_dept] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[t_duty]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[t_duty] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[t_loanlog]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[t_loanlog] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[t_subject]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[t_subject] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[t_user]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[t_user] GO CREATE TABLE [dbo].[t_book] ( [bookId] [int] IDENTITY (1, 1) NOT NULL , [bookName] [varchar] (100) COLLATE Chinese_PRC_CI_AS NOT NULL , [author] [varchar] (100) COLLATE Chinese_PRC_CI_AS NULL , [press] [varchar] (100) COLLATE Chinese_PRC_CI_AS NULL , [publicationDate] [datetime] NULL , [price] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL , [editionNo] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL , [isbn] [varchar] (100) COLLATE Chinese_PRC_CI_AS NULL , [categoryId] [int] NULL , [categoryName] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL , [wordCount] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL , [pageCount] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL , [bookSize] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL , [paper] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL , [pack] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL , [imageUrl] [varchar] (255) COLLATE Chinese_PRC_CI_AS NULL , [address] [varchar] (100) COLLATE Chinese_PRC_CI_AS NULL , [editorRecommend] [varchar] (1000) COLLATE Chinese_PRC_CI_AS NULL , [description] [varchar] (1000) COLLATE Chinese_PRC_CI_AS NULL , [authorDesc] [varchar] (1000) COLLATE Chinese_PRC_CI_AS NULL , [state] [int] NOT NULL , [currentReaderId] [int] NULL , [currentReader] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL , [logId] [int] NULL ) ON [PRIMARY] GO CREATE TABLE [dbo].[t_category] ( [categoryId] [int] IDENTITY (1, 1) NOT NULL , [categoryName] [varchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL , [subjectId] [int] NULL , [subjectName] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL , [remark] [varchar] (200) COLLATE Chinese_PRC_CI_AS NULL ) ON [PRIMARY] GO CREATE TABLE [dbo].[t_company] ( [companyId] [int] IDENTITY (1, 1) NOT NULL , [companyName] [varchar] (100) COLLATE Chinese_PRC_CI_AS NOT NULL , [address] [varchar] (150) COLLATE Chinese_PRC_CI_AS NULL , [tellPhone] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL , [leader] [varchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL , [mobilePhone] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL , [remark] [varchar] (500) COLLATE Chinese_PRC_CI_AS NULL ) ON [PRIMARY] GO CREATE TABLE [dbo].[t_dept] ( [deptId] [int] IDENTITY (1, 1) NOT NULL , [deptName] [varchar] (100) COLLATE Chinese_PRC_CI_AS NOT NULL , [companyId] [int] NULL , [companyName] [varchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL , [address] [varchar] (150) COLLATE Chinese_PRC_CI_AS NULL , [tellPhone] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL , [leader] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL , [mobilePhone] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL , [remark] [varchar] (500) COLLATE Chinese_PRC_CI_AS NULL ) ON [PRIMARY] GO CREATE TABLE [dbo].[t_duty] ( [dutyId] [int] IDENTITY (1, 1) NOT NULL , [dutyName] [varchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL , [remark] [varchar] (200) COLLATE Chinese_PRC_CI_AS NULL ) ON [PRIMARY] GO CREATE TABLE [dbo].[t_loanlog] ( [logId] [int] IDENTITY (1, 1) NOT NULL , [bookId] [int] NOT NULL , [bookName] [varchar] (100) COLLATE Chinese_PRC_CI_AS NULL , [loanTime] [datetime] NULL , [loanDays] [int] NULL , [preReturnTime] [datetime] NULL , [returnTime] [datetime] NULL , [readerId] [int] NOT NULL , [reader] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL , [loannerId] [int] NOT NULL , [loanner] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ) ON [PRIMARY] GO CREATE TABLE [dbo].[t_subject] ( [subjectId] [int] IDENTITY (1, 1) NOT NULL , [subjectName] [varchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL , [remark] [varchar] (200) COLLATE Chinese_PRC_CI_AS NULL ) ON [PRIMARY] GO CREATE TABLE [dbo].[t_user] ( [userId] [int] IDENTITY (1, 1) NOT NULL , [emplName] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL , [emplNo] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL , [mobilePhone] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL , [sex] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL , [age] [int] NULL , [userName] [varchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL , [password] [varchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL , [companyId] [int] NULL , [companyName] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL , [deptId] [int] NULL , [deptName] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL , [dutyId] [int] NULL , [dutyName] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL , [manager] [bit] NULL , [remark] [varchar] (200) COLLATE Chinese_PRC_CI_AS NULL ) ON [PRIMARY] GO ALTER TABLE [dbo].[t_book] ADD CONSTRAINT [PK_t_book] PRIMARY KEY CLUSTERED ( [bookId] ) ON [PRIMARY] GO ALTER TABLE [dbo].[t_category] ADD CONSTRAINT [PK_t_category] PRIMARY KEY CLUSTERED ( [categoryId] ) ON [PRIMARY] , CONSTRAINT [IX_t_category] UNIQUE NONCLUSTERED ( [categoryName], [subjectId] ) ON [PRIMARY] GO ALTER TABLE [dbo].[t_company] ADD CONSTRAINT [PK_t_company] PRIMARY KEY CLUSTERED ( [companyId] ) ON [PRIMARY] , CONSTRAINT [IX_t_company] UNIQUE NONCLUSTERED ( [companyName] ) ON [PRIMARY] GO ALTER TABLE [dbo].[t_dept] ADD CONSTRAINT [PK_t_dept] PRIMARY KEY CLUSTERED ( [deptId] ) ON [PRIMARY] , CONSTRAINT [IX_t_dept] UNIQUE NONCLUSTERED ( [deptName], [companyId] ) ON [PRIMARY] GO ALTER TABLE [dbo].[t_duty] ADD CONSTRAINT [PK_t_duty] PRIMARY KEY CLUSTERED ( [dutyId] ) ON [PRIMARY] , CONSTRAINT [IX_t_duty] UNIQUE NONCLUSTERED ( [dutyName] ) ON [PRIMARY] GO ALTER TABLE [dbo].[t_loanlog] ADD CONSTRAINT [PK_t_loanlog] PRIMARY KEY CLUSTERED ( [logId] ) ON [PRIMARY] GO ALTER TABLE [dbo].[t_subject] ADD CONSTRAINT [PK_Table1] PRIMARY KEY CLUSTERED ( [subjectId] ) ON [PRIMARY] , CONSTRAINT [IX_Table1] UNIQUE NONCLUSTERED ( [subjectName] ) ON [PRIMARY] GO ALTER TABLE [dbo].[t_user] ADD CONSTRAINT [PK_t_user] PRIMARY KEY CLUSTERED ( [userId] ) ON [PRIMARY] , CONSTRAINT [IX_t_user] UNIQUE NONCLUSTERED ( [userName] ) ON [PRIMARY] GO |
|