www.pudn.com > iBATIS_JPetStore-4.0.5.zip > 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