AdventureWorks

Basic Index   Expanded Index

Table Name:Production.BillOfMaterials
Description:Items required to make bicycles and bicycle subassemblies. It identifies the heirarchical relationship between a parent product and its components.
Primary Keys:BillOfMaterialsID
FieldTypeDefaultsNulls?Comments
BillOfMaterialsIDint - IdentityNoneNoPrimary key for BillOfMaterials records.
ProductAssemblyIDintNoneYesParent product identification number. Foreign key to Product.ProductID.
ComponentIDintNoneNoComponent identification number. Foreign key to Product.ProductID.
StartDatedatetime(getdate())NoDate the component started being used in the assembly item.
EndDatedatetimeNoneYesDate the component stopped being used in the assembly item.
UnitMeasureCodenchar (3)NoneNoStandard code identifying the unit of measure for the quantity.
BOMLevelsmallintNoneNoIndicates the depth the component is from its parent (AssemblyID).
PerAssemblyQtydecimal (8, 2)((1.00))NoQuantity of the component needed to create the assembly.
ModifiedDatedatetime(getdate())NoDate and time the record was last updated.

IndexClustered?Unique?Fields
AK_BillOfMaterials_ProductAssemblyID_ComponentID_StartDateYesYesProductAssemblyID
ComponentID
StartDate
PK_BillOfMaterials_BillOfMaterialsIDNoYesBillOfMaterialsID
IX_BillOfMaterials_UnitMeasureCodeNoNoUnitMeasureCode

Check ConstraintText
CK_BillOfMaterials_EndDate([EndDate]>[StartDate] OR [EndDate] IS NULL)
CK_BillOfMaterials_ProductAssemblyID([ProductAssemblyID]<>[ComponentID])
CK_BillOfMaterials_BOMLevel([ProductAssemblyID] IS NULL AND [BOMLevel]=(0) AND [PerAssemblyQty]=(1.00) OR [ProductAssemblyID] IS NOT NULL AND [BOMLevel]>=(1))
CK_BillOfMaterials_PerAssemblyQty([PerAssemblyQty]>=(1.00))

Internal Foreign Key ConstraintAffected FieldSource Table
FK_BillOfMaterials_Product_ComponentIDComponentIDProduction.Product
FK_BillOfMaterials_Product_ProductAssemblyIDProductAssemblyIDProduction.Product
FK_BillOfMaterials_UnitMeasure_UnitMeasureCodeUnitMeasureCodeProduction.UnitMeasure

No primary keys as foreign key constraints
No triggers