Northwind Database (Document! X Sample)
Production.BillOfMaterials Table
AdventureWorks Database > Production Schema : Production.BillOfMaterials Table
Description
Items required to make bicycles and bicycle subassemblies. It identifies the heirarchical relationship between a parent product and its components.
Properties
Creation Date27/10/2017 14:33
File GroupPRIMARY
Text File Group
System Object
Published for Replication
Rows2679
Data Space Used160.00 KB
Index Space Used200.00 KB
Columns
 Column NameDescriptionDatatypeLengthAllow NullsDefaultFormula
Primary Key Primary key for BillOfMaterials records.Integer4   
 Parent product identification number. Foreign key to Product.ProductID.Integer4  
 Component identification number. Foreign key to Product.ProductID.Integer4   
 Date the component started being used in the assembly item.DBTimeStamp4 
(getdate())
 
 Date the component stopped being used in the assembly item.DBTimeStamp4  
 Standard code identifying the unit of measure for the quantity.WChar3   
 Indicates the depth the component is from its parent (AssemblyID).SmallInt2   
 Quantity of the component needed to create the assembly.Numeric9 (8,2) 
((1.00))
 
 Date and time the record was last updated.DBTimeStamp4 
(getdate())
 
Indexes
IndexDescriptionPrimaryUnique
Clustered index. 
Nonclustered index.  
Primary key (clustered) constraint
Check Constraints
NameDescriptionExpression
Check constraint [ProductAssemblyID] IS NULL AND [BOMLevel] = (0) AND [PerAssemblyQty] = (1) OR [ProductAssemblyID] IS NOT NULL AND [BOMLevel] >= (1)
([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
([EndDate]>[StartDate] OR [EndDate] IS NULL)
Check constraint [PerAssemblyQty] >= (1.00)
([PerAssemblyQty]>=(1.00))
Check constraint [ProductAssemblyID] <> [ComponentID]
([ProductAssemblyID]<>[ComponentID])
Relationships
RelationshipDescription
Foreign key constraint referencing Product.ComponentID.
Foreign key constraint referencing Product.ProductAssemblyID.
Foreign key constraint referencing UnitMeasure.UnitMeasureCode.
Objects that depend on Production.BillOfMaterials
 Database ObjectObject TypeDescriptionDep Level
dbo.uspGetBillOfMaterials proceduredbo.uspGetBillOfMaterialsStored ProcedureStored procedure using a recursive query to return a multi-level bill of material for the specified ProductID.1
dbo.uspGetWhereUsedProductID proceduredbo.uspGetWhereUsedProductIDStored ProcedureStored procedure using a recursive query to return all components or assemblies that directly or indirectly use the specified ProductID.1
Objects that Production.BillOfMaterials depends on
 Database ObjectObject TypeDescriptionDep Level
dbo.Flag datatypedbo.FlagUser Defined Data Type 2
dbo.Name datatypedbo.NameUser Defined Data Type 2
Production.Product tableProduction.ProductTableProducts sold or used in the manfacturing of sold products.1
Production.ProductCategory tableProduction.ProductCategoryTableHigh-level product categorization.3
Production.ProductModel tableProduction.ProductModelTableProduct model classification.2
Production.ProductSubcategory tableProduction.ProductSubcategoryTableProduct subcategories. See ProductCategory table.2
Production.UnitMeasure tableProduction.UnitMeasureTableUnit of measure lookup table.1
SQL
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE TABLE [Production].[BillOfMaterials](
    [BillOfMaterialsID] [int] IDENTITY(1,1) NOT NULL,
    [ProductAssemblyID] [int] NULL,
    [ComponentID] [int] NOT NULL,
    [StartDate] [datetime] NOT NULL,
    [EndDate] [datetime] NULL,
    [UnitMeasureCode] [nchar](3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    [BOMLevel] [smallint] NOT NULL,
    [PerAssemblyQty] [decimal](8, 2) NOT NULL,
    [ModifiedDate] [datetime] NOT NULL,
 CONSTRAINT [PK_BillOfMaterials_BillOfMaterialsID] PRIMARY KEY NONCLUSTERED 
(
    [BillOfMaterialsID] 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].[BillOfMaterials] ADD  CONSTRAINT [DF_BillOfMaterials_StartDate]  DEFAULT (getdate()) FOR [StartDate]
ALTER TABLE [Production].[BillOfMaterials] ADD  CONSTRAINT [DF_BillOfMaterials_PerAssemblyQty]  DEFAULT ((1.00)) FOR [PerAssemblyQty]
ALTER TABLE [Production].[BillOfMaterials] ADD  CONSTRAINT [DF_BillOfMaterials_ModifiedDate]  DEFAULT (getdate()) FOR [ModifiedDate]
ALTER TABLE [Production].[BillOfMaterials]  WITH CHECK ADD  CONSTRAINT [FK_BillOfMaterials_Product_ComponentID] FOREIGN KEY([ComponentID])
REFERENCES [Production].[Product] ([ProductID])
ALTER TABLE [Production].[BillOfMaterials] CHECK CONSTRAINT [FK_BillOfMaterials_Product_ComponentID]
ALTER TABLE [Production].[BillOfMaterials]  WITH CHECK ADD  CONSTRAINT [FK_BillOfMaterials_Product_ProductAssemblyID] FOREIGN KEY([ProductAssemblyID])
REFERENCES [Production].[Product] ([ProductID])
ALTER TABLE [Production].[BillOfMaterials] CHECK CONSTRAINT [FK_BillOfMaterials_Product_ProductAssemblyID]
ALTER TABLE [Production].[BillOfMaterials]  WITH CHECK ADD  CONSTRAINT [FK_BillOfMaterials_UnitMeasure_UnitMeasureCode] FOREIGN KEY([UnitMeasureCode])
REFERENCES [Production].[UnitMeasure] ([UnitMeasureCode])
ALTER TABLE [Production].[BillOfMaterials] CHECK CONSTRAINT [FK_BillOfMaterials_UnitMeasure_UnitMeasureCode]
ALTER TABLE [Production].[BillOfMaterials]  WITH CHECK ADD  CONSTRAINT [CK_BillOfMaterials_BOMLevel] CHECK  (([ProductAssemblyID] IS NULL AND [BOMLevel]=(0) AND [PerAssemblyQty]=(1.00) OR [ProductAssemblyID] IS NOT NULL AND [BOMLevel]>=(1)))
ALTER TABLE [Production].[BillOfMaterials] CHECK CONSTRAINT [CK_BillOfMaterials_BOMLevel]
ALTER TABLE [Production].[BillOfMaterials]  WITH CHECK ADD  CONSTRAINT [CK_BillOfMaterials_EndDate] CHECK  (([EndDate]>[StartDate] OR [EndDate] IS NULL))
ALTER TABLE [Production].[BillOfMaterials] CHECK CONSTRAINT [CK_BillOfMaterials_EndDate]
ALTER TABLE [Production].[BillOfMaterials]  WITH CHECK ADD  CONSTRAINT [CK_BillOfMaterials_PerAssemblyQty] CHECK  (([PerAssemblyQty]>=(1.00)))
ALTER TABLE [Production].[BillOfMaterials] CHECK CONSTRAINT [CK_BillOfMaterials_PerAssemblyQty]
ALTER TABLE [Production].[BillOfMaterials]  WITH CHECK ADD  CONSTRAINT [CK_BillOfMaterials_ProductAssemblyID] CHECK  (([ProductAssemblyID]<>[ComponentID]))
ALTER TABLE [Production].[BillOfMaterials] CHECK CONSTRAINT [CK_BillOfMaterials_ProductAssemblyID]
See Also

Related Objects

Production Schema
AdventureWorks Database