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)}[int] IDENTITY (1,1) NOT NULL
-
//${escapify(column.description)}[int] NULL
-
//${escapify(column.description)}[int] NOT NULL
-
//${escapify(column.description)}[datetime] NOT NULL DEFAULT (getdate())
-
//${escapify(column.description)}[datetime] NULL
-
//${escapify(column.description)}[nchar](3) NOT NULL
-
//${escapify(column.description)}[smallint] NOT NULL
-
//${escapify(column.description)}[decimal](8, 2) NOT NULL DEFAULT ((1.00))//CONSTRAINT: Check constraint [PerAssemblyQty] >= (1.00)([PerAssemblyQty]>=(1.00))
-
//${escapify(column.description)}[datetime] NOT NULL DEFAULT (getdate())
Indexes
-
//Clustered index.AK_BillOfMaterials_ProductAssemblyID_ComponentID_StartDateUNIQUE CLUSTEREDProductAssemblyID , ComponentID , StartDate
-
//Nonclustered index created by a primary key constraint.PK_BillOfMaterials_BillOfMaterialsIDUNIQUE NONCLUSTEREDBillOfMaterialsID
-
//Nonclustered index.IX_BillOfMaterials_UnitMeasureCodeNONCLUSTEREDUnitMeasureCode
Foreign Keys
-
//Foreign key constraint referencing Product.ComponentID.FK_BillOfMaterials_Product_ComponentIDProduction.ProductComponentID ⥱ ProductID
-
//Foreign key constraint referencing Product.ProductAssemblyID.FK_BillOfMaterials_Product_ProductAssemblyIDProduction.ProductProductAssemblyID ⥱ ProductID
-
//Foreign key constraint referencing UnitMeasure.UnitMeasureCode.FK_BillOfMaterials_UnitMeasure_UnitMeasureCodeProduction.UnitMeasureUnitMeasureCode ⥱ 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 NULLCK_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