Databases > AdventureWorks2019 > Tables > Production.BillOfMaterials

AdventureWorks2019

Production.BillOfMaterials

//
Items required to make bicycles and bicycle subassemblies. It identifies the heirarchical relationship between a parent product and its components.

Created: Modified:

Columns

  • //
    ${escapify(column.description)}
    BillOfMaterialsID
    [int] IDENTITY (1,1) NOT NULL
  • //
    ${escapify(column.description)}
    ProductAssemblyID
    [int] NULL
  • //
    ${escapify(column.description)}
    ComponentID
    [int] NOT NULL
  • //
    ${escapify(column.description)}
    StartDate
    [datetime] NOT NULL DEFAULT (getdate())
  • //
    ${escapify(column.description)}
    EndDate
    [datetime] NULL
  • //
    ${escapify(column.description)}
    UnitMeasureCode
    [nchar](3) NOT NULL
  • //
    ${escapify(column.description)}
    BOMLevel
    [smallint] NOT NULL
  • //
    ${escapify(column.description)}
    PerAssemblyQty
    [decimal](8, 2) NOT NULL DEFAULT ((1.00))
    //
    CONSTRAINT: Check constraint [PerAssemblyQty] >= (1.00)
    ([PerAssemblyQty]>=(1.00))
  • //
    ${escapify(column.description)}
    ModifiedDate
    [datetime] NOT NULL DEFAULT (getdate())

Indexes

  • //
    Clustered index.
    AK_BillOfMaterials_ProductAssemblyID_ComponentID_StartDate
    UNIQUE CLUSTERED
    ProductAssemblyID , ComponentID , StartDate
  • //
    Nonclustered index created by a primary key constraint.
    PK_BillOfMaterials_BillOfMaterialsID
    UNIQUE NONCLUSTERED
    BillOfMaterialsID
  • //
    Nonclustered index.
    IX_BillOfMaterials_UnitMeasureCode
    NONCLUSTERED
    UnitMeasureCode

Foreign Keys

  • //
    Foreign key constraint referencing Product.ComponentID.
    FK_BillOfMaterials_Product_ComponentID
    Production.Product
    ComponentID ⥱ ProductID
  • //
    Foreign key constraint referencing Product.ProductAssemblyID.
    FK_BillOfMaterials_Product_ProductAssemblyID
    Production.Product
    ProductAssemblyID ⥱ ProductID
  • //
    Foreign key constraint referencing UnitMeasure.UnitMeasureCode.
    FK_BillOfMaterials_UnitMeasure_UnitMeasureCode
    Production.UnitMeasure
    UnitMeasureCode ⥱ UnitMeasureCode

Check Constraints

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

Sample Data

BillOfMaterialsID
ProductAssemblyID
ComponentID
StartDate
EndDate
UnitMeasureCode
BOMLevel
PerAssemblyQty
ModifiedDate
893
NULL
749
NULL
EA
NULL
1
271
NULL
750
EA
NULL
1
34
NULL
750
NULL
EA
NULL
1
830
NULL
751
NULL
EA
NULL
1
2074
NULL
752
NULL
EA
NULL
1
1950
NULL
753
EA
NULL
1
1761
NULL
753
NULL
EA
NULL
1
3088
NULL
754
NULL
EA
NULL
1
3351
NULL
755
NULL
EA
NULL
1
3246
NULL
756
NULL
EA
NULL
1
2760
NULL
757
NULL
EA
NULL
1
2395
NULL
758
NULL
EA
NULL
1
3087
NULL
759
NULL
EA
NULL
1
3350
NULL
760
NULL
EA
NULL
1
2822
NULL
761
NULL
EA
NULL
1
3245
NULL
762
NULL
EA
NULL
1
2759
NULL
763
NULL
EA
NULL
1
2394
NULL
764
NULL
EA
NULL
1
3341
NULL
765
NULL
EA
NULL
1
2815
NULL
766
NULL
EA
NULL
1
2449
NULL
767
NULL
EA
NULL
1
2899
NULL
768
EA
NULL
1
2738
NULL
768
NULL
EA
NULL
1
2363
NULL
769
NULL
EA
NULL
1
1265
NULL
770
NULL
EA
NULL
1