AdventureWorks - [Production].[Product]

Description

Products sold or used in the manfacturing of sold products.

Table properties

name	value
name	[Production].[Product]
created	Mar 19 2009 9:08PM
modified	Mar 19 2009 9:08PM
ansi nulls	on
quoted identifier	on
row count	504
Size of data	104 kb
Size of indexes	120 kb
Maximum size of a single row	517 bytes

Columns

column	datatype	length	bytes	default	nulls	PK	FK	computed	comment
ProductID	int identity(1,1)	10	4		no	yes		no	Primary key for Product records.
Name	Name	50	200		no			no	Name of the product.
ProductNumber	nvarchar(25)	25	100		no			no	Unique product identification number.
MakeFlag	Flag	1	0	((1))	no			no	0 = Product is purchased, 1 = Product is manufactured in-house.
FinishedGoodsFlag	Flag	1	0	((1))	no			no	0 = Product is not a salable item. 1 = Product is salable.
Color	nvarchar(15)	15	60		yes			no	Product color.
SafetyStockLevel	smallint	5	2		no			no	Minimum inventory quantity.
ReorderPoint	smallint	5	2		no			no	Inventory level that triggers a purchase order or work order.
StandardCost	money	19	8		no			no	Standard cost of the product.
ListPrice	money	19	8		no			no	Selling price.
Size	nvarchar(5)	5	20		yes			no	Product size.
SizeUnitMeasureCode	nchar(3)	3	12		yes		UnitMeasure.UnitMeasureCode	no	Unit of measure for Size column.
WeightUnitMeasureCode	nchar(3)	3	12		yes		UnitMeasure.UnitMeasureCode	no	Unit of measure for Weight column.
Weight	decimal(8,2)	8	5		yes			no	Product weight.
DaysToManufacture	int	10	4		no			no	Number of days required to manufacture the product.
ProductLine	nchar(2)	2	8		yes			no	R = Road, M = Mountain, T = Touring, S = Standard
Class	nchar(2)	2	8		yes			no	H = High, M = Medium, L = Low
Style	nchar(2)	2	8		yes			no	W = Womens, M = Mens, U = Universal
ProductSubcategoryID	int	10	4		yes		ProductSubcategory.ProductSubcategoryID	no	Product is a member of this product subcategory. Foreign key to ProductSubCategory.ProductSubCategoryID.
ProductModelID	int	10	4		yes		ProductModel.ProductModelID	no	Product is a member of this product model. Foreign key to ProductModel.ProductModelID.
SellStartDate	datetime	23	8		no			no	Date the product was available for sale.
SellEndDate	datetime	23	8		yes			no	Date the product was no longer available for sale.
DiscontinuedDate	datetime	23	8		yes			no	Date the product was discontinued.
rowguid	uniqueidentifier	36	16	(newid())	no			no	ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample.
ModifiedDate	datetime	23	8	(getdate())	no			no	Date and time the record was last updated.

Indexes

name	description	column	comment
AK_Product_Name	nonclustered, unique located on PRIMARY	Name	Unique nonclustered index.
AK_Product_ProductNumber	nonclustered, unique located on PRIMARY	ProductNumber	Unique nonclustered index.
AK_Product_rowguid	nonclustered, unique located on PRIMARY	rowguid	Unique nonclustered index. Used to support replication samples.
PK_Product_ProductID	clustered, unique, primary key located on PRIMARY	ProductID	Clustered index created by a primary key constraint.

References

name
ProductModel
ProductSubcategory
UnitMeasure
UnitMeasure

Referenced by

name
BillOfMaterials
BillOfMaterials
ProductCostHistory
ProductDocument
ProductInventory
ProductListPriceHistory
ProductProductPhoto
ProductReview
TransactionHistory
WorkOrder
ProductVendor
PurchaseOrderDetail
ShoppingCartItem
SpecialOfferProduct

Foreign key graph

ProductModelProductSubcategoryUnitMeasureUnitMeasureProduct BillOfMaterialsBillOfMaterialsProductCostHistoryProductDocumentProductInventoryProductListPriceHistoryProductProductPhotoProductReviewTransactionHistoryWorkOrderProductVendorPurchaseOrderDetailShoppingCartItemSpecialOfferProduct

Foreign keys

name	columns	foreign columns	comment
FK_Product_ProductSubcategory_ProductSubcategoryID	ProductSubcategoryID	ProductSubcategory.ProductSubcategoryID	Foreign key constraint referencing ProductSubcategory.ProductSubcategoryID.
FK_Product_UnitMeasure_SizeUnitMeasureCode	SizeUnitMeasureCode	UnitMeasure.UnitMeasureCode	Foreign key constraint referencing UnitMeasure.UnitMeasureCode.
FK_Product_UnitMeasure_WeightUnitMeasureCode	WeightUnitMeasureCode	UnitMeasure.UnitMeasureCode	Foreign key constraint referencing UnitMeasure.UnitMeasureCode.
FK_Product_ProductModel_ProductModelID	ProductModelID	ProductModel.ProductModelID	Foreign key constraint referencing ProductModel.ProductModelID.

Check constraints

name	column	comment
CK_Product_SafetyStockLevel ([SafetyStockLevel]>(0))	SafetyStockLevel	Check constraint [SafetyStockLevel] > (0)
CK_Product_ReorderPoint ([ReorderPoint]>(0))	ReorderPoint	Check constraint [ReorderPoint] > (0)
CK_Product_StandardCost ([StandardCost]>=(0.00))	StandardCost	Check constraint [SafetyStockLevel] > (0)
CK_Product_ListPrice ([ListPrice]>=(0.00))	ListPrice	Check constraint [ListPrice] >= (0.00)
CK_Product_Weight ([Weight]>(0.00))	Weight	Check constraint [Weight] > (0.00)
CK_Product_DaysToManufacture ([DaysToManufacture]>=(0))	DaysToManufacture	Check constraint [DaysToManufacture] >= (0)
CK_Product_ProductLine (upper([ProductLine])='R' OR upper([ProductLine])='M' OR upper([ProductLine])='T' OR upper([ProductLine])='S' OR [ProductLine] IS NULL)	ProductLine	Check constraint [ProductLine]='r' OR [ProductLine]='m' OR [ProductLine]='t' OR [ProductLine]='s' OR [ProductLine]='R' OR [ProductLine]='M' OR [ProductLine]='T' OR [ProductLine]='S' OR [ProductLine] IS NULL
CK_Product_Class (upper([Class])='H' OR upper([Class])='M' OR upper([Class])='L' OR [Class] IS NULL)	Class	Check constraint [Class]='h' OR [Class]='m' OR [Class]='l' OR [Class]='H' OR [Class]='M' OR [Class]='L' OR [Class] IS NULL
CK_Product_Style (upper([Style])='U' OR upper([Style])='M' OR upper([Style])='W' OR [Style] IS NULL)	Style	Check constraint [Style]='u' OR [Style]='m' OR [Style]='w' OR [Style]='U' OR [Style]='M' OR [Style]='W' OR [Style] IS NULL
CK_Product_SellEndDate ([SellEndDate]>=[SellStartDate] OR [SellEndDate] IS NULL)	SellStartDate SellEndDate	Check constraint [SellEndDate] >= [SellStartDate] OR [SellEndDate] IS NULL

Defaults

default name	column	comment
DF_Product_MakeFlag	MakeFlag	Default constraint value of 1
DF_Product_FinishedGoodsFlag	FinishedGoodsFlag	Default constraint value of 1
DF_Product_rowguid	rowguid	Default constraint value of NEWID()
DF_Product_ModifiedDate	ModifiedDate	Default constraint value of GETDATE()

Dependency graph

Objects that [Production].[Product] depends on

name	object type	level
Flag	type	1
Name	type	1

Objects that depend on [Production].[Product]

name	object type	level
ufnGetProductDealerPrice	function	1
ufnGetProductListPrice	function	1
ufnGetProductStandardCost	function	1
uspGetBillOfMaterials	stored procedure	1
uspGetWhereUsedProductID	stored procedure	1
vProductAndDescription	view	1

Sample rows

ProductID	Name	ProductNumber	MakeFlag	FinishedGoodsFlag	Color	SafetyStockLevel	ReorderPoint	Size	SizeUnitMeasureCode	WeightUnitMeasureCode	Weight	DaysToManufacture	ProductLine	Class	Style	ProductSubcategoryID	ProductModelID	SellStartDate	SellEndDate	DiscontinuedDate	rowguid	ModifiedDate
1	Adjustable Race	AR-5381	False	False	NULL	1000	750	NULL	NULL	NULL	Null	0	NULL	NULL	NULL	NULL	NULL	6/1/1998 12:00:00 AM	NULL	NULL	694215b7-08f7-4c0d-acb1-d734ba44c0c8	3/11/2004 10:01:36 AM
2	Bearing Ball	BA-8327	False	False	NULL	1000	750	NULL	NULL	NULL	Null	0	NULL	NULL	NULL	NULL	NULL	6/1/1998 12:00:00 AM	NULL	NULL	58ae3c20-4f3a-4749-a7d4-d568806cc537	3/11/2004 10:01:36 AM
3	BB Ball Bearing	BE-2349	True	False	NULL	800	600	NULL	NULL	NULL	Null	1	NULL	NULL	NULL	NULL	NULL	6/1/1998 12:00:00 AM	NULL	NULL	9c21aed2-5bfa-4f18-bcb8-f11638dc2e4e	3/11/2004 10:01:36 AM
4	Headset Ball Bearings	BE-2908	False	False	NULL	800	600	NULL	NULL	NULL	Null	0	NULL	NULL	NULL	NULL	NULL	6/1/1998 12:00:00 AM	NULL	NULL	ecfed6cb-51ff-49b5-b06c-7d8ac834db8b	3/11/2004 10:01:36 AM
316	Blade	BL-2036	True	False	NULL	800	600	NULL	NULL	NULL	Null	1	NULL	NULL	NULL	NULL	NULL	6/1/1998 12:00:00 AM	NULL	NULL	e73e9750-603b-4131-89f5-3dd15ed5ff80	3/11/2004 10:01:36 AM
317	LL Crankarm	CA-5965	False	False	Black	500	375	NULL	NULL	NULL	Null	0	NULL	L	NULL	NULL	NULL	6/1/1998 12:00:00 AM	NULL	NULL	3c9d10b7-a6b2-4774-9963-c19dcee72fea	3/11/2004 10:01:36 AM
318	ML Crankarm	CA-6738	False	False	Black	500	375	NULL	NULL	NULL	Null	0	NULL	M	NULL	NULL	NULL	6/1/1998 12:00:00 AM	NULL	NULL	eabb9a92-fa07-4eab-8955-f0517b4a4ca7	3/11/2004 10:01:36 AM
319	HL Crankarm	CA-7457	False	False	Black	500	375	NULL	NULL	NULL	Null	0	NULL	NULL	NULL	NULL	NULL	6/1/1998 12:00:00 AM	NULL	NULL	7d3fd384-4f29-484b-86fa-4206e276fe58	3/11/2004 10:01:36 AM
320	Chainring Bolts	CB-2903	False	False	Silver	1000	750	NULL	NULL	NULL	Null	0	NULL	NULL	NULL	NULL	NULL	6/1/1998 12:00:00 AM	NULL	NULL	7be38e48-b7d6-4486-888e-f53c26735101	3/11/2004 10:01:36 AM
321	Chainring Nut	CN-6137	False	False	Silver	1000	750	NULL	NULL	NULL	Null	0	NULL	NULL	NULL	NULL	NULL	6/1/1998 12:00:00 AM	NULL	NULL	3314b1d7-ef69-4431-b6dd-dc75268bd5df	3/11/2004 10:01:36 AM

Code

CREATE TABLE [Production].[Product](
    [ProductID] [int] IDENTITY(1,1) NOT NULL,
    [Name] [dbo].[Name] NOT NULL,
    [ProductID] [int] IDENTITY(1,1) NOT NULL,
    [ProductNumber] [nvarchar](25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    [MakeFlag] [dbo].[Flag] NOT NULL,
    [FinishedGoodsFlag] [dbo].[Flag] NOT NULL,
    [Color] [nvarchar](15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [SafetyStockLevel] [smallint] NOT NULL,
    [ReorderPoint] [smallint] NOT NULL,
    [StandardCost] [money] NOT NULL,
    [ListPrice] [money] NOT NULL,
    [Size] [nvarchar](5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [SizeUnitMeasureCode] [nchar](3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [WeightUnitMeasureCode] [nchar](3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [Weight] [decimal](8, 2) NULL,
    [DaysToManufacture] [int] NOT NULL,
    [ProductLine] [nchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [Class] [nchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [Style] [nchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [ProductSubcategoryID] [int] NULL,
    [ProductModelID] [int] NULL,
    [SellStartDate] [datetime] NOT NULL,
    [SellEndDate] [datetime] NULL,
    [DiscontinuedDate] [datetime] NULL,
    [rowguid] [uniqueidentifier] ROWGUIDCOL  NOT NULL,
    [ModifiedDate] [datetime] NOT NULL,
 CONSTRAINT [PK_Product_ProductID] 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]

ALTER TABLE [Production].[Product]  WITH CHECK ADD  CONSTRAINT [FK_Product_ProductModel_ProductModelID] FOREIGN KEY([ProductModelID])
REFERENCES [Production].[ProductModel] ([ProductModelID])
ALTER TABLE [Production].[Product] CHECK CONSTRAINT [FK_Product_ProductModel_ProductModelID]
ALTER TABLE [Production].[Product]  WITH CHECK ADD  CONSTRAINT [FK_Product_ProductSubcategory_ProductSubcategoryID] FOREIGN KEY([ProductSubcategoryID])
REFERENCES [Production].[ProductSubcategory] ([ProductSubcategoryID])
ALTER TABLE [Production].[Product] CHECK CONSTRAINT [FK_Product_ProductSubcategory_ProductSubcategoryID]
ALTER TABLE [Production].[Product]  WITH CHECK ADD  CONSTRAINT [FK_Product_UnitMeasure_SizeUnitMeasureCode] FOREIGN KEY([SizeUnitMeasureCode])
REFERENCES [Production].[UnitMeasure] ([UnitMeasureCode])
ALTER TABLE [Production].[Product] CHECK CONSTRAINT [FK_Product_UnitMeasure_SizeUnitMeasureCode]
ALTER TABLE [Production].[Product]  WITH CHECK ADD  CONSTRAINT [FK_Product_UnitMeasure_WeightUnitMeasureCode] FOREIGN KEY([WeightUnitMeasureCode])
REFERENCES [Production].[UnitMeasure] ([UnitMeasureCode])
ALTER TABLE [Production].[Product] CHECK CONSTRAINT [FK_Product_UnitMeasure_WeightUnitMeasureCode]
ALTER TABLE [Production].[Product]  WITH CHECK ADD  CONSTRAINT [CK_Product_Class] CHECK  ((upper([Class])='H' OR upper([Class])='M' OR upper([Class])='L' OR [Class] IS NULL))
ALTER TABLE [Production].[Product] CHECK CONSTRAINT [CK_Product_Class]
ALTER TABLE [Production].[Product]  WITH CHECK ADD  CONSTRAINT [CK_Product_DaysToManufacture] CHECK  (([DaysToManufacture]>=(0)))
ALTER TABLE [Production].[Product] CHECK CONSTRAINT [CK_Product_DaysToManufacture]
ALTER TABLE [Production].[Product]  WITH CHECK ADD  CONSTRAINT [CK_Product_ListPrice] CHECK  (([ListPrice]>=(0.00)))
ALTER TABLE [Production].[Product] CHECK CONSTRAINT [CK_Product_ListPrice]
ALTER TABLE [Production].[Product]  WITH CHECK ADD  CONSTRAINT [CK_Product_ProductLine] CHECK  ((upper([ProductLine])='R' OR upper([ProductLine])='M' OR upper([ProductLine])='T' OR upper([ProductLine])='S' OR [ProductLine] IS NULL))
ALTER TABLE [Production].[Product] CHECK CONSTRAINT [CK_Product_ProductLine]
ALTER TABLE [Production].[Product]  WITH CHECK ADD  CONSTRAINT [CK_Product_ReorderPoint] CHECK  (([ReorderPoint]>(0)))
ALTER TABLE [Production].[Product] CHECK CONSTRAINT [CK_Product_ReorderPoint]
ALTER TABLE [Production].[Product]  WITH CHECK ADD  CONSTRAINT [CK_Product_SafetyStockLevel] CHECK  (([SafetyStockLevel]>(0)))
ALTER TABLE [Production].[Product] CHECK CONSTRAINT [CK_Product_SafetyStockLevel]
ALTER TABLE [Production].[Product]  WITH CHECK ADD  CONSTRAINT [CK_Product_SellEndDate] CHECK  (([SellEndDate]>=[SellStartDate] OR [SellEndDate] IS NULL))
ALTER TABLE [Production].[Product] CHECK CONSTRAINT [CK_Product_SellEndDate]
ALTER TABLE [Production].[Product]  WITH CHECK ADD  CONSTRAINT [CK_Product_StandardCost] CHECK  (([StandardCost]>=(0.00)))
ALTER TABLE [Production].[Product] CHECK CONSTRAINT [CK_Product_StandardCost]
ALTER TABLE [Production].[Product]  WITH CHECK ADD  CONSTRAINT [CK_Product_Style] CHECK  ((upper([Style])='U' OR upper([Style])='M' OR upper([Style])='W' OR [Style] IS NULL))
ALTER TABLE [Production].[Product] CHECK CONSTRAINT [CK_Product_Style]
ALTER TABLE [Production].[Product]  WITH CHECK ADD  CONSTRAINT [CK_Product_Weight] CHECK  (([Weight]>(0.00)))
ALTER TABLE [Production].[Product] CHECK CONSTRAINT [CK_Product_Weight]
ALTER TABLE [Production].[Product] ADD  CONSTRAINT [DF_Product_MakeFlag]  DEFAULT ((1)) FOR [MakeFlag]
ALTER TABLE [Production].[Product] ADD  CONSTRAINT [DF_Product_FinishedGoodsFlag]  DEFAULT ((1)) FOR [FinishedGoodsFlag]
ALTER TABLE [Production].[Product] ADD  CONSTRAINT [DF_Product_rowguid]  DEFAULT (newid()) FOR [rowguid]
ALTER TABLE [Production].[Product] ADD  CONSTRAINT [DF_Product_ModifiedDate]  DEFAULT (getdate()) FOR [ModifiedDate]

AdventureWorks - tables - [Production].[Product]

Description

Table properties

Columns

Indexes

References

Referenced by

Foreign key graph

Foreign keys

Check constraints

Defaults

Dependency graph

Objects that [Production].[Product] depends on

Objects that depend on [Production].[Product]

Sample rows

Code