AdventureWorks

Basic Index   Expanded Index

Table Name:Production.Product
Description:Products sold or used in the manfacturing of sold products.
Primary Keys:ProductID
FieldTypeDefaultsNulls?Comments
ProductIDint - IdentityNoneNoPrimary key for Product records.
Namenvarchar (50)NoneNoName of the product.
ProductNumbernvarchar (25)NoneNoUnique product identification number.
MakeFlagbit((1))No0 = Product is purchased, 1 = Product is manufactured in-house.
FinishedGoodsFlagbit((1))No0 = Product is not a salable item. 1 = Product is salable.
Colornvarchar (15)NoneYesProduct color.
SafetyStockLevelsmallintNoneNoMinimum inventory quantity.
ReorderPointsmallintNoneNoInventory level that triggers a purchase order or work order.
StandardCostmoneyNoneNoStandard cost of the product.
ListPricemoneyNoneNoSelling price.
Sizenvarchar (5)NoneYesProduct size.
SizeUnitMeasureCodenchar (3)NoneYesUnit of measure for Size column.
WeightUnitMeasureCodenchar (3)NoneYesUnit of measure for Weight column.
Weightdecimal (8, 2)NoneYesProduct weight.
DaysToManufactureintNoneNoNumber of days required to manufacture the product.
ProductLinenchar (2)NoneYesR = Road, M = Mountain, T = Touring, S = Standard
Classnchar (2)NoneYesH = High, M = Medium, L = Low
Stylenchar (2)NoneYesW = Womens, M = Mens, U = Universal
ProductSubcategoryIDintNoneYesProduct is a member of this product subcategory. Foreign key to ProductSubCategory.ProductSubCategoryID.
ProductModelIDintNoneYesProduct is a member of this product model. Foreign key to ProductModel.ProductModelID.
SellStartDatedatetimeNoneNoDate the product was available for sale.
SellEndDatedatetimeNoneYesDate the product was no longer available for sale.
DiscontinuedDatedatetimeNoneYesDate the product was discontinued.
rowguiduniqueidentifier(newid())NoROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample.
ModifiedDatedatetime(getdate())NoDate and time the record was last updated.

IndexClustered?Unique?Fields
PK_Product_ProductIDYesYesProductID
AK_Product_ProductNumberNoYesProductNumber
AK_Product_NameNoYesName
AK_Product_rowguidNoYesrowguid

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

Internal Foreign Key ConstraintAffected FieldSource Table
FK_Product_ProductModel_ProductModelIDProductModelIDProduction.ProductModel
FK_Product_ProductSubcategory_ProductSubcategoryIDProductSubcategoryIDProduction.ProductSubcategory
FK_Product_UnitMeasure_SizeUnitMeasureCodeSizeUnitMeasureCodeProduction.UnitMeasure
FK_Product_UnitMeasure_WeightUnitMeasureCodeWeightUnitMeasureCodeProduction.UnitMeasure

Primary Key as Foreign Key ConstraintAffected TableAffected Field
FK_BillOfMaterials_Product_ComponentIDProduction.BillOfMaterialsComponentID
FK_BillOfMaterials_Product_ProductAssemblyIDProduction.BillOfMaterialsProductAssemblyID
FK_ProductCostHistory_Product_ProductIDProduction.ProductCostHistoryProductID
FK_ProductDocument_Product_ProductIDProduction.ProductDocumentProductID
FK_ProductInventory_Product_ProductIDProduction.ProductInventoryProductID
FK_ProductListPriceHistory_Product_ProductIDProduction.ProductListPriceHistoryProductID
FK_ProductProductPhoto_Product_ProductIDProduction.ProductProductPhotoProductID
FK_ProductReview_Product_ProductIDProduction.ProductReviewProductID
FK_ProductVendor_Product_ProductIDPurchasing.ProductVendorProductID
FK_PurchaseOrderDetail_Product_ProductIDPurchasing.PurchaseOrderDetailProductID
FK_ShoppingCartItem_Product_ProductIDSales.ShoppingCartItemProductID
FK_SpecialOfferProduct_Product_ProductIDSales.SpecialOfferProductProductID
FK_TransactionHistory_Product_ProductIDProduction.TransactionHistoryProductID
FK_WorkOrder_Product_ProductIDProduction.WorkOrderProductID

No triggers