Production.Product Table

Information

Name	Product
Schema	Production
Row Count	0
Data Size
Index Size
Reserved Size
Unused Size
Created	29.3.2010. 21:58:18
Modified	29.3.2010. 21:58:56

Extended Properties

Name	Type	Property Name	Value
Product	TABLE	MS_Description	Products sold or used in the manfacturing of sold products.

Columns

Name	Data Type	Collation	References	Default
ProductID	int
Name	nvarchar(50)	SQL_Latin1_General_CP1_CI_AS
ProductNumber	nvarchar(25)	SQL_Latin1_General_CP1_CI_AS
MakeFlag	bit			((1))
FinishedGoodsFlag	bit			((1))
Color	nvarchar(15)	SQL_Latin1_General_CP1_CI_AS
SafetyStockLevel	smallint
ReorderPoint	smallint
StandardCost	money
ListPrice	money
Size	nvarchar(5)	SQL_Latin1_General_CP1_CI_AS
SizeUnitMeasureCode	nchar(3)	SQL_Latin1_General_CP1_CI_AS	Production.UnitMeasure.UnitMeasureCode
WeightUnitMeasureCode	nchar(3)	SQL_Latin1_General_CP1_CI_AS	Production.UnitMeasure.UnitMeasureCode
Weight	decimal(8,2)
DaysToManufacture	int
ProductLine	nchar(2)	SQL_Latin1_General_CP1_CI_AS
Class	nchar(2)	SQL_Latin1_General_CP1_CI_AS
Style	nchar(2)	SQL_Latin1_General_CP1_CI_AS
ProductSubcategoryID	int		Production.ProductSubcategory.ProductSubcategoryID
ProductModelID	int		Production.ProductModel.ProductModelID
SellStartDate	datetime
SellEndDate	datetime
DiscontinuedDate	datetime
rowguid	uniqueidentifier			(newid())
ModifiedDate	datetime			(getdate())

Indexes

Name	Type	Columns
PK_Product_ProductID	Clustered	ProductID
AK_Product_ProductNumber	NonClustered	ProductNumber
AK_Product_Name	NonClustered	Name
AK_Product_rowguid	NonClustered	rowguid

Check Constraints

Name	Column	Expression
CK_Product_SellEndDate		([SellEndDate]>=[SellStartDate] OR [SellEndDate] IS NULL)
CK_Product_SafetyStockLevel	SafetyStockLevel	([SafetyStockLevel]>(0))
CK_Product_ReorderPoint	ReorderPoint	([ReorderPoint]>(0))
CK_Product_StandardCost	StandardCost	([StandardCost]>=(0.00))
CK_Product_ListPrice	ListPrice	([ListPrice]>=(0.00))
CK_Product_Weight	Weight	([Weight]>(0.00))
CK_Product_DaysToManufacture	DaysToManufacture	([DaysToManufacture]>=(0))
CK_Product_ProductLine	ProductLine	(upper([ProductLine])='R' OR upper([ProductLine])='M' OR upper([ProductLine])='T' OR upper([ProductLine])='S' OR [ProductLine] IS NULL)
CK_Product_Class	Class	(upper([Class])='H' OR upper([Class])='M' OR upper([Class])='L' OR [Class] IS NULL)
CK_Product_Style	Style	(upper([Style])='U' OR upper([Style])='M' OR upper([Style])='W' OR [Style] IS NULL)

Foreign Key Constraints

Name	Type	Referenced Table
FK_Product_ProductModel_ProductModelID	Foreign key	Production.ProductModel
FK_Product_ProductSubcategory_ProductSubcategoryID	Foreign key	Production.ProductSubcategory
FK_Product_UnitMeasure_SizeUnitMeasureCode	Foreign key	Production.UnitMeasure
FK_Product_UnitMeasure_WeightUnitMeasureCode	Foreign key	Production.UnitMeasure

SQL Script

CREATE TABLE [Production].[Product] (
    [ProductID] int NOT NULL IDENTITY,
    [Name] nvarchar(50) NOT NULL,
    [ProductNumber] nvarchar(25) NOT NULL,
    [MakeFlag] bit NOT NULL DEFAULT ((1)),
    [FinishedGoodsFlag] bit NOT NULL DEFAULT ((1)),
    [Color] nvarchar(15) NULL,
    [SafetyStockLevel] smallint NOT NULL,
    [ReorderPoint] smallint NOT NULL,
    [StandardCost] money NOT NULL,
    [ListPrice] money NOT NULL,
    [Size] nvarchar(5) NULL,
    [SizeUnitMeasureCode] nchar(3) NULL,
    [WeightUnitMeasureCode] nchar(3) NULL,
    [Weight] decimal(8,2) NULL,
    [DaysToManufacture] int NOT NULL,
    [ProductLine] nchar(2) NULL,
    [Class] nchar(2) NULL,
    [Style] nchar(2) NULL,
    [ProductSubcategoryID] int NULL,
    [ProductModelID] int NULL,
    [SellStartDate] datetime NOT NULL,
    [SellEndDate] datetime NULL,
    [DiscontinuedDate] datetime NULL,
    [rowguid] uniqueidentifier NOT NULL DEFAULT (newid()),
    [ModifiedDate] datetime NOT NULL DEFAULT (getdate())
);
CREATE UNIQUE INDEX [AK_Product_ProductNumber] on [Production].[Product]([ProductNumber]);
CREATE UNIQUE INDEX [AK_Product_Name] on [Production].[Product]([Name]);
CREATE UNIQUE INDEX [AK_Product_rowguid] on [Production].[Product]([rowguid]);