SQLize Online / PHPize Online  /  SQLtest Online

/*** Drop Order Tables Script Date: Thu Jun 06 2013 18:37:00 GMT-0700 (Pacific Daylight Time) ***/ IF OBJECT_ID('[dbo].[OrderItemOption]', 'U') IS NOT NULL BEGIN ALTER TABLE [dbo].[OrderItemOption] DROP CONSTRAINT [FK_OrderItemOption_ProductOption] ALTER TABLE [dbo].[OrderItemOption] DROP CONSTRAINT [FK_OrderItemOption_OrderItem] DROP TABLE [dbo].[OrderItemOption] END GO IF OBJECT_ID('[dbo].[OrderItem]', 'U') IS NOT NULL BEGIN ALTER TABLE [dbo].[OrderItem] DROP CONSTRAINT [FK_OrderItem_ProductSize] ALTER TABLE [dbo].[OrderItem] DROP CONSTRAINT [FK_OrderItem_Product] ALTER TABLE [dbo].[OrderItem] DROP CONSTRAINT [FK_OrderItem_Order] DROP TABLE [dbo].[OrderItem] END GO IF OBJECT_ID('[dbo].[Order]', 'U') IS NOT NULL BEGIN ALTER TABLE [dbo].[Order] DROP CONSTRAINT [FK_Order_Customer] ALTER TABLE [dbo].[Order] DROP CONSTRAINT [FK_Order_OrderStatus] DROP TABLE [dbo].[Order] END GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO PRINT ' *** Customer Table Generation ***' /*** Table [dbo].[Customer] Script Date: Thu Jun 06 2013 18:37:00 GMT-0700 (Pacific Daylight Time) ***/ IF OBJECT_ID('[dbo].[Customer]', 'U') IS NOT NULL DROP TABLE [dbo].[Customer] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[Customer]( [Id] [uniqueidentifier] NOT NULL, [StoreId] [uniqueidentifier] NULL, [FirstName] [nvarchar](50) NULL, [LastName] [nvarchar](50) NULL, [Phone] [nvarchar](100) NULL, [Email] [nvarchar](255) NULL, [Street] [nvarchar](100) NULL, [City] [nvarchar](100) NULL, [State] [nvarchar](2) NULL, [Zip] [nvarchar](10) NULL, CONSTRAINT [PK_Customer] PRIMARY KEY CLUSTERED ([Id] ASC) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO /*** Table [dbo].[ProductSize] Script Date: Thu Jun 06 2013 18:37:00 GMT-0700 (Pacific Daylight Time) ***/ IF OBJECT_ID('[dbo].[ProductSize]', 'U') IS NOT NULL BEGIN ALTER TABLE [dbo].[ProductSize] DROP CONSTRAINT [DF_ProductSize_IsGlutenFree] DROP TABLE [dbo].[ProductSize] END GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[ProductSize]( [Id] [int] NOT NULL, [Type] [nvarchar](20) NOT NULL, [Name] [nvarchar](50) NOT NULL, [Price] [smallmoney] NOT NULL, [PremiumPrice] [smallmoney] NULL, [ToppingPrice] [smallmoney] NULL, [IsGlutenFree] [bit] NULL, CONSTRAINT [PK_ProductSize] PRIMARY KEY CLUSTERED ([Id] ASC) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO ALTER TABLE [dbo].[ProductSize] ADD CONSTRAINT [DF_ProductSize_IsGlutenFree] DEFAULT ((0)) FOR [IsGlutenFree] GO INSERT INTO [dbo].[ProductSize]([Id],[Type],[Name],[Price] ,[PremiumPrice],[ToppingPrice],[IsGlutenFree])VALUES(1,'pizza','Personal 8"',6.15,7.65,0.65,0); INSERT INTO [dbo].[ProductSize]([Id],[Type],[Name],[Price] ,[PremiumPrice],[ToppingPrice],[IsGlutenFree])VALUES(2,'pizza','Medium 12"',12.4,16.95,1.35,0); INSERT INTO [dbo].[ProductSize]([Id],[Type],[Name],[Price] ,[PremiumPrice],[ToppingPrice],[IsGlutenFree])VALUES(3,'pizza','Large 14"',14.45,20.95,1.7,0); INSERT INTO [dbo].[ProductSize]([Id],[Type],[Name],[Price] ,[PremiumPrice],[ToppingPrice],[IsGlutenFree])VALUES(4,'pizza','X-Large 16"',16.5,23.45,2.05,0); INSERT INTO [dbo].[ProductSize]([Id],[Type],[Name],[Price] ,[PremiumPrice],[ToppingPrice],[IsGlutenFree])VALUES(5,'pizza','Huge 18"',17.95,26.95,2.25,0); INSERT INTO [dbo].[ProductSize]([Id],[Type],[Name],[Price] ,[PremiumPrice],[ToppingPrice],[IsGlutenFree])VALUES(6,'pizza','Gluten Free 12"',16.9,21.45,1.35,1); INSERT INTO [dbo].[ProductSize]([Id],[Type],[Name],[Price] ,[PremiumPrice],[ToppingPrice],[IsGlutenFree])VALUES(7,'salad','Side',3.95,NULL,0.65,0); INSERT INTO [dbo].[ProductSize]([Id],[Type],[Name],[Price] ,[PremiumPrice],[ToppingPrice],[IsGlutenFree])VALUES(8,'salad','Entree',7.95,NULL,1.3,0); INSERT INTO [dbo].[ProductSize]([Id],[Type],[Name],[Price] ,[PremiumPrice],[ToppingPrice],[IsGlutenFree])VALUES(9,'salad','Entree',8.95,NULL,1.3,0); INSERT INTO [dbo].[ProductSize]([Id],[Type],[Name],[Price] ,[PremiumPrice],[ToppingPrice],[IsGlutenFree])VALUES(10,'drink','Can',1.5,NULL,NULL,0); INSERT INTO [dbo].[ProductSize]([Id],[Type],[Name],[Price] ,[PremiumPrice],[ToppingPrice],[IsGlutenFree])VALUES(11,'drink','20 oz',1.95,NULL,NULL,0); INSERT INTO [dbo].[ProductSize]([Id],[Type],[Name],[Price] ,[PremiumPrice],[ToppingPrice],[IsGlutenFree])VALUES(12,'drink','2 liter',2.75,NULL,NULL,0); INSERT INTO [dbo].[ProductSize]([Id],[Type],[Name],[Price] ,[PremiumPrice],[ToppingPrice],[IsGlutenFree])VALUES(13,'drink','20 oz',1.75,NULL,NULL,0); INSERT INTO [dbo].[ProductSize]([Id],[Type],[Name],[Price] ,[PremiumPrice],[ToppingPrice],[IsGlutenFree])VALUES(14,'drink','Serving',1.75,NULL,NULL,0); INSERT INTO [dbo].[ProductSize]([Id],[Type],[Name],[Price] ,[PremiumPrice],[ToppingPrice],[IsGlutenFree])VALUES(15,'drink','Serving',2,NULL,NULL,0); PRINT ' *** OrderStatus Table Generation ***' /*** Table [dbo].[OrderStatus] Script Date: Thu Jun 06 2013 18:37:00 GMT-0700 (Pacific Daylight Time) ***/ IF OBJECT_ID('[dbo].[OrderStatus]', 'U') IS NOT NULL DROP TABLE [dbo].[OrderStatus] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[OrderStatus]( [Id] [int] NOT NULL, [Name] [nvarchar](50) NOT NULL, CONSTRAINT [PK_OrderStatus] PRIMARY KEY CLUSTERED ([Id] ASC) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO INSERT INTO [dbo].[OrderStatus]([Id],[Name])VALUES(1,'Ordered'); INSERT INTO [dbo].[OrderStatus]([Id],[Name])VALUES(2,'PickedUp'); INSERT INTO [dbo].[OrderStatus]([Id],[Name])VALUES(3,'Delivered'); INSERT INTO [dbo].[OrderStatus]([Id],[Name])VALUES(4,'Cancelled'); INSERT INTO [dbo].[OrderStatus]([Id],[Name])VALUES(5,'Pending'); PRINT ' *** Order Tables Generation ***' /*** Create Order Tables Script Date: Thu Jun 06 2013 18:37:00 GMT-0700 (Pacific Daylight Time) ***/ CREATE TABLE [dbo].[Order]( [Id] [bigint] IDENTITY(1,1) NOT NULL, [StoreId] [uniqueidentifier] NULL, [CustomerId] [uniqueidentifier] NOT NULL, [OrderStatusId] [int] NOT NULL, [OrderDate] [datetime2](7) NOT NULL, [Phone] [nvarchar](100) NULL, [DeliveryDate] [datetime2](7) NULL, [DeliveryCharge] [smallmoney] NULL, [DeliveryStreet] [nvarchar](100) NULL, [DeliveryCity] [nvarchar](100) NULL, [DeliveryState] [nvarchar](2) NULL, [DeliveryZip] [nvarchar](10) NULL, [ItemsTotal] [smallmoney] NOT NULL, CONSTRAINT [PK_Order] PRIMARY KEY CLUSTERED ([Id] ASC) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO ALTER TABLE [dbo].[Order] WITH CHECK ADD CONSTRAINT [FK_Order_Customer] FOREIGN KEY([CustomerId]) REFERENCES [dbo].[Customer] ([Id]) GO ALTER TABLE [dbo].[Order] CHECK CONSTRAINT [FK_Order_Customer] GO ALTER TABLE [dbo].[Order] WITH CHECK ADD CONSTRAINT [FK_Order_OrderStatus] FOREIGN KEY([OrderStatusId]) REFERENCES [dbo].[OrderStatus] ([Id]) GO ALTER TABLE [dbo].[Order] CHECK CONSTRAINT [FK_Order_OrderStatus] GO CREATE TABLE [dbo].[OrderItem]( [Id] [bigint] IDENTITY(1,1) NOT NULL, [StoreId] [uniqueidentifier] NULL, [OrderId] [bigint] NOT NULL, [ProductId] [int] NOT NULL, [ProductSizeId] [int] NOT NULL, [Quantity] [int] NOT NULL, [UnitPrice] [smallmoney] NOT NULL, [TotalPrice] [smallmoney] NOT NULL, [Instructions] [nvarchar](255) NULL, CONSTRAINT [PK_OrderItem] PRIMARY KEY CLUSTERED ([Id] ASC) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO ALTER TABLE [dbo].[OrderItem] WITH CHECK ADD CONSTRAINT [FK_OrderItem_Order] FOREIGN KEY([OrderId]) REFERENCES [dbo].[Order] ([Id]) GO ALTER TABLE [dbo].[OrderItem] CHECK CONSTRAINT [FK_OrderItem_Order] GO ALTER TABLE [dbo].[OrderItem] WITH CHECK ADD CONSTRAINT [FK_OrderItem_Product] FOREIGN KEY([ProductId]) REFERENCES [dbo].[Product] ([Id]) GO ALTER TABLE [dbo].[OrderItem] CHECK CONSTRAINT [FK_OrderItem_Product] GO ALTER TABLE [dbo].[OrderItem] WITH CHECK ADD CONSTRAINT [FK_OrderItem_ProductSize] FOREIGN KEY([ProductSizeId]) REFERENCES [dbo].[ProductSize] ([Id]) GO ALTER TABLE [dbo].[OrderItem] CHECK CONSTRAINT [FK_OrderItem_ProductSize] GO CREATE TABLE [dbo].[OrderItemOption]( [Id] [bigint] IDENTITY(1,1) NOT NULL, [StoreId] [uniqueidentifier] NULL, [OrderItemId] [bigint] NOT NULL, [ProductOptionId] [int] NOT NULL, [Quantity] [int] NOT NULL, [Price] [smallmoney] NOT NULL, CONSTRAINT [PK_OrderItemOption] PRIMARY KEY CLUSTERED ([Id] ASC) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO ALTER TABLE [dbo].[OrderItemOption] WITH CHECK ADD CONSTRAINT [FK_OrderItemOption_OrderItem] FOREIGN KEY([OrderItemId]) REFERENCES [dbo].[OrderItem] ([Id]) GO ALTER TABLE [dbo].[OrderItemOption] CHECK CONSTRAINT [FK_OrderItemOption_OrderItem] GO ALTER TABLE [dbo].[OrderItemOption] WITH CHECK ADD CONSTRAINT [FK_OrderItemOption_ProductOption] FOREIGN KEY([ProductOptionId]) REFERENCES [dbo].[ProductOption] ([Id]) GO ALTER TABLE [dbo].[OrderItemOption] CHECK CONSTRAINT [FK_OrderItemOption_ProductOption] GO