?? jpetstore-mssql-schema.sql
字號:
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[fk_orders_1]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[orders] DROP CONSTRAINT fk_orders_1
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[fk_product_1]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[product] DROP CONSTRAINT fk_product_1
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[fk_lineitem_1]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[lineitem] DROP CONSTRAINT fk_lineitem_1
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[fk_orderstatus_1]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[orderstatus] DROP CONSTRAINT fk_orderstatus_1
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[fk_item_1]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[item] DROP CONSTRAINT fk_item_1
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[fk_item_2]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[item] DROP CONSTRAINT fk_item_2
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[account]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[account]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[bannerdata]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[bannerdata]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[category]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[category]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[inventory]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[inventory]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[item]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[item]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[lineitem]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[lineitem]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[orders]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[orders]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[orderstatus]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[orderstatus]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[product]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[product]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[profile]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[profile]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sequence]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[sequence]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[signon]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[signon]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[supplier]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[supplier]
GO
CREATE TABLE [dbo].[account] (
[userid] [varchar] (80) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[email] [varchar] (80) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[firstname] [varchar] (80) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[lastname] [varchar] (80) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[status] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[addr1] [varchar] (80) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[addr2] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[city] [varchar] (80) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[state] [varchar] (80) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[zip] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[country] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[phone] [varchar] (80) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[bannerdata] (
[favcategory] [varchar] (80) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[bannername] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[category] (
[catid] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[name] [varchar] (80) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[descn] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[inventory] (
[itemid] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[qty] [int] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[item] (
[itemid] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[productid] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[listprice] [decimal](10, 2) NULL ,
[unitcost] [decimal](10, 2) NULL ,
[supplier] [int] NULL ,
[status] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[attr1] [varchar] (80) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[attr2] [varchar] (80) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[attr3] [varchar] (80) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[attr4] [varchar] (80) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[attr5] [varchar] (80) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[lineitem] (
[orderid] [int] NOT NULL ,
[linenum] [int] NOT NULL ,
[itemid] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[quantity] [int] NOT NULL ,
[unitprice] [numeric](10, 2) NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[orders] (
[orderid] [int] NOT NULL ,
[userid] [varchar] (80) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[orderdate] [datetime] NOT NULL ,
[shipaddr1] [varchar] (80) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[shipaddr2] [varchar] (80) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[shipcity] [varchar] (80) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[shipstate] [varchar] (80) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[shipzip] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[shipcountry] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[billaddr1] [varchar] (80) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[billaddr2] [varchar] (80) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[billcity] [varchar] (80) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[billstate] [varchar] (80) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[billzip] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[billcountry] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[courier] [varchar] (80) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[totalprice] [numeric](10, 2) NOT NULL ,
[billtofirstname] [varchar] (80) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[billtolastname] [varchar] (80) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[shiptofirstname] [varchar] (80) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[shiptolastname] [varchar] (80) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[creditcard] [varchar] (80) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[exprdate] [varchar] (7) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[cardtype] [varchar] (80) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[locale] [varchar] (80) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[orderstatus] (
[orderid] [int] NOT NULL ,
[linenum] [int] NOT NULL ,
[timestamp] [datetime] NOT NULL ,
[status] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[product] (
[productid] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[category] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[name] [varchar] (80) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[descn] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[profile] (
[userid] [varchar] (80) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[langpref] [varchar] (80) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[favcategory] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[mylistopt] [int] NULL ,
[banneropt] [int] NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[sequence] (
[name] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[nextid] [int] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[signon] (
[username] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[password] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[supplier] (
[suppid] [int] NOT NULL ,
[name] [varchar] (80) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[status] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[addr1] [varchar] (80) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[addr2] [varchar] (80) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[city] [varchar] (80) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[state] [varchar] (80) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[zip] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[phone] [varchar] (80) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[account] WITH NOCHECK ADD
CONSTRAINT [pk_account] PRIMARY KEY CLUSTERED
(
[userid]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[bannerdata] WITH NOCHECK ADD
CONSTRAINT [pk_bannerdata] PRIMARY KEY CLUSTERED
(
[favcategory]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[category] WITH NOCHECK ADD
CONSTRAINT [pk_category] PRIMARY KEY CLUSTERED
(
[catid]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[inventory] WITH NOCHECK ADD
CONSTRAINT [pk_inventory] PRIMARY KEY CLUSTERED
(
[itemid]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[item] WITH NOCHECK ADD
CONSTRAINT [pk_item] PRIMARY KEY CLUSTERED
(
[itemid]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[lineitem] WITH NOCHECK ADD
CONSTRAINT [pk_lineitem] PRIMARY KEY CLUSTERED
(
[orderid],
[linenum]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[orders] WITH NOCHECK ADD
CONSTRAINT [pk_orders] PRIMARY KEY CLUSTERED
(
[orderid]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[orderstatus] WITH NOCHECK ADD
CONSTRAINT [pk_orderstatus] PRIMARY KEY CLUSTERED
(
[orderid],
[linenum]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[product] WITH NOCHECK ADD
CONSTRAINT [pk_product] PRIMARY KEY CLUSTERED
(
[productid]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[profile] WITH NOCHECK ADD
CONSTRAINT [pk_profile] PRIMARY KEY CLUSTERED
(
[userid]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[sequence] WITH NOCHECK ADD
CONSTRAINT [pk_sequence] PRIMARY KEY CLUSTERED
(
[name]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[signon] WITH NOCHECK ADD
CONSTRAINT [pk_signon] PRIMARY KEY CLUSTERED
(
[username]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[supplier] WITH NOCHECK ADD
CONSTRAINT [pk_supplier] PRIMARY KEY CLUSTERED
(
[suppid]
) ON [PRIMARY]
GO
CREATE INDEX [itemProd] ON [dbo].[item]([productid]) ON [PRIMARY]
GO
CREATE INDEX [productCat] ON [dbo].[product]([category]) ON [PRIMARY]
GO
CREATE INDEX [productName] ON [dbo].[product]([name]) ON [PRIMARY]
GO
ALTER TABLE [dbo].[item] ADD
CONSTRAINT [fk_item_1] FOREIGN KEY
(
[productid]
) REFERENCES [dbo].[product] (
[productid]
),
CONSTRAINT [fk_item_2] FOREIGN KEY
(
[supplier]
) REFERENCES [dbo].[supplier] (
[suppid]
)
GO
ALTER TABLE [dbo].[lineitem] ADD
CONSTRAINT [fk_lineitem_1] FOREIGN KEY
(
[orderid]
) REFERENCES [dbo].[orders] (
[orderid]
)
GO
ALTER TABLE [dbo].[orders] ADD
CONSTRAINT [fk_orders_1] FOREIGN KEY
(
[userid]
) REFERENCES [dbo].[account] (
[userid]
)
GO
ALTER TABLE [dbo].[orderstatus] ADD
CONSTRAINT [fk_orderstatus_1] FOREIGN KEY
(
[orderid]
) REFERENCES [dbo].[orders] (
[orderid]
)
GO
ALTER TABLE [dbo].[product] ADD
CONSTRAINT [fk_product_1] FOREIGN KEY
(
[category]
) REFERENCES [dbo].[category] (
[catid]
)
GO
?? 快捷鍵說明
復制代碼
Ctrl + C
搜索代碼
Ctrl + F
全屏模式
F11
切換主題
Ctrl + Shift + D
顯示快捷鍵
?
增大字號
Ctrl + =
減小字號
Ctrl + -