Distribution Outlet SQL Create

WH01db.sql

USE [WH01]
GO
/****** Object:  Table [dbo].[COUNTRY]    Script Date: 2017-02-05 16:51:04 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[COUNTRY](
	[countryID] [bigint] IDENTITY(1,10) NOT NULL,
	[countryName] [nvarchar](max) NOT NULL,
	[countryAbbreviation] [nvarchar](max) NOT NULL,
	[countryFlag] [nvarchar](max) NOT NULL,
 CONSTRAINT [PK_COUNTRY] PRIMARY KEY CLUSTERED 
(
	[countryID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO
/****** Object:  Table [dbo].[PRODUCT]    Script Date: 2017-02-05 16:51:04 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[PRODUCT](
	[productID] [bigint] IDENTITY(1,10) NOT NULL,
	[productName] [nvarchar](max) NOT NULL,
	[productSerialNumber] [nvarchar](max) NOT NULL,
 CONSTRAINT [PK_PRODUCT] PRIMARY KEY CLUSTERED 
(
	[productID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO
/****** Object:  Table [dbo].[PRODUCT_BOXING_DETAILS]    Script Date: 2017-02-05 16:51:04 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[PRODUCT_BOXING_DETAILS](
	[productBoxingDetailsID] [bigint] IDENTITY(1,10) NOT NULL,
	[shipmentProductDetailsID] [bigint] NOT NULL,
	[numberOfBoxes] [bigint] NOT NULL,
	[numberOfPacks] [bigint] NOT NULL,
	[numberOfProducts] [bigint] NOT NULL,
	[numberOfProductsPerPack] [bigint] NOT NULL,
	[numberOfPacksPerBox] [bigint] NOT NULL,
 CONSTRAINT [PK_PRODUCT_BOXING_DETAILS] PRIMARY KEY CLUSTERED 
(
	[productBoxingDetailsID] 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
/****** Object:  Table [dbo].[PRODUCT_DESCRIPTION]    Script Date: 2017-02-05 16:51:04 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[PRODUCT_DESCRIPTION](
	[productDescriptionID] [bigint] IDENTITY(1,10) NOT NULL,
	[productID] [bigint] NOT NULL,
	[productDescLong] [nvarchar](max) NOT NULL,
	[prodctCopied] [bit] NOT NULL,
 CONSTRAINT [PK_PRODUCT_DESCRIPTION] PRIMARY KEY CLUSTERED 
(
	[productDescriptionID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO
/****** Object:  Table [dbo].[PRODUCT_DESCRIPTION_DETAIL]    Script Date: 2017-02-05 16:51:04 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[PRODUCT_DESCRIPTION_DETAIL](
	[productDescriptionDetailsID] [bigint] IDENTITY(1,10) NOT NULL,
	[productDescriptionID] [bigint] NOT NULL,
	[productColor] [nvarchar](max) NOT NULL,
	[productWeight] [nvarchar](max) NOT NULL,
	[productSize] [nvarchar](max) NOT NULL,
	[productPrice] [nvarchar](max) NOT NULL,
 CONSTRAINT [PK_PRODUCT_DESCRIPTION_DETAILS] PRIMARY KEY CLUSTERED 
(
	[productDescriptionDetailsID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO
/****** Object:  Table [dbo].[SHIPMENT]    Script Date: 2017-02-05 16:51:04 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[SHIPMENT](
	[shipmentID] [bigint] IDENTITY(1,10) NOT NULL,
	[countryID] [bigint] NULL,
	[shipmentCity] [nvarchar](max) NULL,
	[shipmentProvince] [nvarchar](max) NULL,
 CONSTRAINT [PK_SHIPMENT] PRIMARY KEY CLUSTERED 
(
	[shipmentID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO
/****** Object:  Table [dbo].[SHIPMENT_DETAIL]    Script Date: 2017-02-05 16:51:04 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[SHIPMENT_DETAIL](
	[shipmentDetailsID] [bigint] IDENTITY(1,10) NOT NULL,
	[shipmentID] [bigint] NOT NULL,
	[shipmentDate] [datetime] NULL,
	[shipmentEstimatedArrivalDate] [datetime] NULL,
	[shipmentArrivalDate] [datetime] NULL,
	[shipmentMethod] [nvarchar](max) NULL,
	[numberOfProducts] [bigint] NULL,
 CONSTRAINT [PK_SHIPMENT_DETAILS] PRIMARY KEY CLUSTERED 
(
	[shipmentDetailsID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO
/****** Object:  Table [dbo].[SHIPMENT_PRODUCT_DETAIL]    Script Date: 2017-02-05 16:51:04 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[SHIPMENT_PRODUCT_DETAIL](
	[shipmentProductDetailsID] [bigint] IDENTITY(1,10) NOT NULL,
	[shipmentDetailsID] [bigint] NULL,
	[productID] [bigint] NULL,
 CONSTRAINT [PK_SHIPMENT_PRODUCT_DETAILS] PRIMARY KEY CLUSTERED 
(
	[shipmentProductDetailsID] 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
/****** Object:  Table [dbo].[SHIPMENT_SUPPLIER]    Script Date: 2017-02-05 16:51:04 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[SHIPMENT_SUPPLIER](
	[shipmentSupplierID] [bigint] IDENTITY(1,10) NOT NULL,
	[shipmentID] [bigint] NOT NULL,
	[supplierID] [bigint] NOT NULL,
 CONSTRAINT [PK_SHIPMENT_SUPPLIER] PRIMARY KEY CLUSTERED 
(
	[shipmentSupplierID] 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
/****** Object:  Table [dbo].[SUPPLIER]    Script Date: 2017-02-05 16:51:04 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[SUPPLIER](
	[supplierID] [bigint] IDENTITY(1,10) NOT NULL,
	[supplierName] [nvarchar](max) NOT NULL,
	[countryID] [bigint] NOT NULL,
	[supplierProvince] [nvarchar](max) NOT NULL,
	[supplierCity] [nvarchar](max) NOT NULL,
	[supplierPostalCode] [nvarchar](50) NOT NULL,
	[supplierTelephone] [nvarchar](max) NOT NULL,
	[supplierFax] [nvarchar](max) NOT NULL,
	[supplierEmail] [nvarchar](max) NOT NULL,
	[supplierStreet] [nvarchar](max) NOT NULL,
	[supplierUnitNumber] [nvarchar](max) NOT NULL,
 CONSTRAINT [PK_SUPPLIER] PRIMARY KEY CLUSTERED 
(
	[supplierID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO
ALTER TABLE [dbo].[PRODUCT_BOXING_DETAILS]  WITH CHECK ADD  CONSTRAINT [FK_PRODUCT_BOXING_DETAILS_SHIPMENT_PRODUCT_DETAIL] FOREIGN KEY([shipmentProductDetailsID])
REFERENCES [dbo].[SHIPMENT_PRODUCT_DETAIL] ([shipmentProductDetailsID])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[PRODUCT_BOXING_DETAILS] CHECK CONSTRAINT [FK_PRODUCT_BOXING_DETAILS_SHIPMENT_PRODUCT_DETAIL]
GO
ALTER TABLE [dbo].[PRODUCT_DESCRIPTION]  WITH CHECK ADD  CONSTRAINT [FK_PRODUCT_DESCRIPTION_PRODUCT] FOREIGN KEY([productID])
REFERENCES [dbo].[PRODUCT] ([productID])
GO
ALTER TABLE [dbo].[PRODUCT_DESCRIPTION] CHECK CONSTRAINT [FK_PRODUCT_DESCRIPTION_PRODUCT]
GO
ALTER TABLE [dbo].[PRODUCT_DESCRIPTION_DETAIL]  WITH CHECK ADD  CONSTRAINT [FK_PRODUCT_DESCRIPTION_DETAIL_PRODUCT_DESCRIPTION] FOREIGN KEY([productDescriptionID])
REFERENCES [dbo].[PRODUCT_DESCRIPTION] ([productDescriptionID])
GO
ALTER TABLE [dbo].[PRODUCT_DESCRIPTION_DETAIL] CHECK CONSTRAINT [FK_PRODUCT_DESCRIPTION_DETAIL_PRODUCT_DESCRIPTION]
GO
ALTER TABLE [dbo].[SHIPMENT]  WITH CHECK ADD  CONSTRAINT [FK_SHIPMENT_COUNTRY] FOREIGN KEY([countryID])
REFERENCES [dbo].[COUNTRY] ([countryID])
GO
ALTER TABLE [dbo].[SHIPMENT] CHECK CONSTRAINT [FK_SHIPMENT_COUNTRY]
GO
ALTER TABLE [dbo].[SHIPMENT]  WITH CHECK ADD  CONSTRAINT [FK_SHIPMENT_SHIPMENT] FOREIGN KEY([shipmentID])
REFERENCES [dbo].[SHIPMENT] ([shipmentID])
GO
ALTER TABLE [dbo].[SHIPMENT] CHECK CONSTRAINT [FK_SHIPMENT_SHIPMENT]
GO
ALTER TABLE [dbo].[SHIPMENT_DETAIL]  WITH CHECK ADD  CONSTRAINT [FK_SHIPMENT_DETAIL_SHIPMENT] FOREIGN KEY([shipmentID])
REFERENCES [dbo].[SHIPMENT] ([shipmentID])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[SHIPMENT_DETAIL] CHECK CONSTRAINT [FK_SHIPMENT_DETAIL_SHIPMENT]
GO
ALTER TABLE [dbo].[SHIPMENT_PRODUCT_DETAIL]  WITH CHECK ADD  CONSTRAINT [FK_SHIPMENT_PRODUCT_DETAIL_PRODUCT] FOREIGN KEY([productID])
REFERENCES [dbo].[PRODUCT] ([productID])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[SHIPMENT_PRODUCT_DETAIL] CHECK CONSTRAINT [FK_SHIPMENT_PRODUCT_DETAIL_PRODUCT]
GO
ALTER TABLE [dbo].[SHIPMENT_PRODUCT_DETAIL]  WITH CHECK ADD  CONSTRAINT [FK_SHIPMENT_PRODUCT_DETAIL_SHIPMENT_DETAIL] FOREIGN KEY([shipmentDetailsID])
REFERENCES [dbo].[SHIPMENT_DETAIL] ([shipmentDetailsID])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[SHIPMENT_PRODUCT_DETAIL] CHECK CONSTRAINT [FK_SHIPMENT_PRODUCT_DETAIL_SHIPMENT_DETAIL]
GO
ALTER TABLE [dbo].[SHIPMENT_SUPPLIER]  WITH CHECK ADD  CONSTRAINT [FK_SHIPMENT_SUPPLIER_SHIPMENT] FOREIGN KEY([shipmentID])
REFERENCES [dbo].[SHIPMENT] ([shipmentID])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[SHIPMENT_SUPPLIER] CHECK CONSTRAINT [FK_SHIPMENT_SUPPLIER_SHIPMENT]
GO
ALTER TABLE [dbo].[SHIPMENT_SUPPLIER]  WITH CHECK ADD  CONSTRAINT [FK_SHIPMENT_SUPPLIER_SUPPLIER] FOREIGN KEY([supplierID])
REFERENCES [dbo].[SUPPLIER] ([supplierID])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[SHIPMENT_SUPPLIER] CHECK CONSTRAINT [FK_SHIPMENT_SUPPLIER_SUPPLIER]
GO
ALTER TABLE [dbo].[SUPPLIER]  WITH CHECK ADD  CONSTRAINT [FK_SUPPLIER_COUNTRY] FOREIGN KEY([countryID])
REFERENCES [dbo].[COUNTRY] ([countryID])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[SUPPLIER] CHECK CONSTRAINT [FK_SUPPLIER_COUNTRY]
GO
ALTER TABLE [dbo].[SUPPLIER]  WITH CHECK ADD  CONSTRAINT [FK_SUPPLIER_SUPPLIER] FOREIGN KEY([supplierID])
REFERENCES [dbo].[SUPPLIER] ([supplierID])
GO
ALTER TABLE [dbo].[SUPPLIER] CHECK CONSTRAINT [FK_SUPPLIER_SUPPLIER]
GO