Database reference - AdventureWorks

AdventureWorks -  tables -  [Purchasing].[ProductVendor]

Description

Cross-reference table mapping vendors with the products they supply. 

Table properties

namevalue
name[Purchasing].[ProductVendor]
createdMar 19 2009 9:08PM
modifiedMar 19 2009 9:08PM 
ansi nullson 
quoted identifieron 
row count460
Size of data40 kb
Size of indexes48 kb
Maximum size of a single row68 bytes

Columns

columndatatypelengthbytesdefaultnullsPKFKUQcomputedcomment
ProductIDint104 no  composite PK  Product.ProductID      no  Primary key. Foreign key to Product.ProductID. 
VendorIDint104 no  composite PK  Vendor.VendorID      no  Primary key. Foreign key to Vendor.VendorID. 
AverageLeadTimeint104 no      no  The average span of time (in days) between placing an order with the vendor and receiving the purchased product. 
StandardPricemoney198 no      no  The vendor's usual selling price. 
LastReceiptCostmoney198 yes      no  The selling price when last purchased. 
LastReceiptDatedatetime238 yes      no  Date the product was last received by the vendor. 
MinOrderQtyint104 no      no  The maximum quantity that should be ordered. 
MaxOrderQtyint104 no      no  The minimum quantity that should be ordered. 
OnOrderQtyint104 yes      no  The quantity currently on order. 
UnitMeasureCodenchar(3)312 no    UnitMeasure.UnitMeasureCode      no  The product's unit of measure. 
ModifiedDatedatetime238(getdate())no      no  Date and time the record was last updated. 

Indexes

namedescriptioncolumncomment
IX_ProductVendor_UnitMeasureCodenonclustered located on PRIMARYUnitMeasureCodeNonclustered index. 
IX_ProductVendor_VendorIDnonclustered located on PRIMARYVendorIDNonclustered index. 
PK_ProductVendor_ProductID_VendorIDclustered, unique, primary key located on PRIMARYProductID, VendorIDClustered index created by a primary key constraint. 

References

name
Product
UnitMeasure
Vendor

Foreign key graph

ProductUnitMeasureVendorProductVendor

Foreign keys

namecolumnsforeign columnstypecomment
FK_ProductVendor_UnitMeasure_UnitMeasureCodeUnitMeasureCodeUnitMeasure.UnitMeasureCode   Foreign key constraint referencing UnitMeasure.UnitMeasureCode. 
FK_ProductVendor_Vendor_VendorIDVendorIDVendor.VendorID   Foreign key constraint referencing Vendor.VendorID. 
FK_ProductVendor_Product_ProductIDProductIDProduct.ProductID   Foreign key constraint referencing Product.ProductID. 

Check constraints

namecolumncomment
CK_ProductVendor_AverageLeadTimeAverageLeadTime    Check constraint [AverageLeadTime] >= (1) 
CK_ProductVendor_StandardPriceStandardPrice    Check constraint [StandardPrice] > (0.00) 
CK_ProductVendor_LastReceiptCostLastReceiptCost    Check constraint [LastReceiptCost] > (0.00) 
CK_ProductVendor_MinOrderQtyMinOrderQty    Check constraint [MinOrderQty] >= (1) 
CK_ProductVendor_MaxOrderQtyMaxOrderQty    Check constraint [MaxOrderQty] >= (1) 
CK_ProductVendor_OnOrderQtyOnOrderQty    Check constraint [OnOrderQty] >= (0) 

Defaults

default namecolumncomment
DF_ProductVendor_ModifiedDateModifiedDateDefault constraint value of GETDATE() 

Dependency graph

DF_ProductVendor_ModifiedDateProductVendorCK_ProductVendor_AverageLeadTimeCK_ProductVendor_LastReceiptCostCK_ProductVendor_MaxOrderQtyCK_ProductVendor_MinOrderQtyCK_ProductVendor_OnOrderQtyCK_ProductVendor_StandardPriceIX_ProductVendor_UnitMeasureCode...IX_ProductVendor_VendorID...PK_ProductVendor_ProductID_VendorID...

Sample rows

ProductIDVendorIDAverageLeadTimeStandardPriceLastReceiptCostLastReceiptDateMinOrderQtyMaxOrderQtyOnOrderQtyUnitMeasureCodeModifiedDate
1  83  17  47.8700  50.2635  9/29/2001 12:00:00 AM  1  5  3  CS   9/29/2001 12:00:00 AM 
2  57  19  39.9200  41.9160  9/29/2001 12:00:00 AM  1  5  3  CTN  9/29/2001 12:00:00 AM 
4  85  17  54.3100  57.0255  9/29/2001 12:00:00 AM  1  5  NULL  CTN  9/29/2001 12:00:00 AM 
317  50  19  28.1700  29.5785  9/29/2001 12:00:00 AM  100  1000  300  EA   9/29/2001 12:00:00 AM 
317  84  17  25.7700  27.0585  9/25/2001 12:00:00 AM  100  1000  NULL  EA   9/25/2001 12:00:00 AM 
318  50  19  34.3800  36.0990  9/29/2001 12:00:00 AM  100  1000  NULL  EA   9/29/2001 12:00:00 AM 
318  84  17  31.9800  33.5790  9/25/2001 12:00:00 AM  100  1000  300  EA   9/25/2001 12:00:00 AM 
319  40  19  44.2100  46.4205  9/29/2001 12:00:00 AM  100  1000  300  EA   9/29/2001 12:00:00 AM 
319  50  19  46.2700  48.5835  9/29/2001 12:00:00 AM  100  1000  NULL  EA   9/29/2001 12:00:00 AM 
319  84  17  43.8700  46.0635  9/25/2001 12:00:00 AM  100  1000  NULL  EA   9/25/2001 12:00:00 AM 

Code

CREATE TABLE [Purchasing].[ProductVendor](
    [ProductID] [int] NOT NULL,
    [VendorID] [int] NOT NULL,
    [AverageLeadTime] [int] NOT NULL,
    [StandardPrice] [money] NOT NULL,
    [LastReceiptCost] [money] NULL,
    [LastReceiptDate] [datetime] NULL,
    [MinOrderQty] [int] NOT NULL,
    [MaxOrderQty] [int] NOT NULL,
    [OnOrderQty] [int] NULL,
    [UnitMeasureCode] [nchar](3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    [ProductID] [int] NOT NULL,
    [VendorID] [int] NOT NULL,
    [AverageLeadTime] [int] NOT NULL,
    [StandardPrice] [money] NOT NULL,
    [LastReceiptCost] [money] NULL,
    [LastReceiptDate] [datetime] NULL,
    [MinOrderQty] [int] NOT NULL,
    [MaxOrderQty] [int] NOT NULL,
    [OnOrderQty] [int] NULL,
    [ModifiedDate] [datetime] NOT NULL,
 CONSTRAINT [PK_ProductVendor_ProductID_VendorID] PRIMARY KEY CLUSTERED 
(
    [ProductID] ASC,
    [VendorID] 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 [Purchasing].[ProductVendor]  WITH CHECK ADD  CONSTRAINT [FK_ProductVendor_Product_ProductID] FOREIGN KEY([ProductID])
REFERENCES [Production].[Product] ([ProductID])
ALTER TABLE [Purchasing].[ProductVendor] CHECK CONSTRAINT [FK_ProductVendor_Product_ProductID]
ALTER TABLE [Purchasing].[ProductVendor]  WITH CHECK ADD  CONSTRAINT [FK_ProductVendor_UnitMeasure_UnitMeasureCode] FOREIGN KEY([UnitMeasureCode])
REFERENCES [Production].[UnitMeasure] ([UnitMeasureCode])
ALTER TABLE [Purchasing].[ProductVendor] CHECK CONSTRAINT [FK_ProductVendor_UnitMeasure_UnitMeasureCode]
ALTER TABLE [Purchasing].[ProductVendor]  WITH CHECK ADD  CONSTRAINT [FK_ProductVendor_Vendor_VendorID] FOREIGN KEY([VendorID])
REFERENCES [Purchasing].[Vendor] ([VendorID])
ALTER TABLE [Purchasing].[ProductVendor] CHECK CONSTRAINT [FK_ProductVendor_Vendor_VendorID]
ALTER TABLE [Purchasing].[ProductVendor]  WITH CHECK ADD  CONSTRAINT [CK_ProductVendor_AverageLeadTime] CHECK  (([AverageLeadTime]>=(1)))
ALTER TABLE [Purchasing].[ProductVendor] CHECK CONSTRAINT [CK_ProductVendor_AverageLeadTime]
ALTER TABLE [Purchasing].[ProductVendor]  WITH CHECK ADD  CONSTRAINT [CK_ProductVendor_LastReceiptCost] CHECK  (([LastReceiptCost]>(0.00)))
ALTER TABLE [Purchasing].[ProductVendor] CHECK CONSTRAINT [CK_ProductVendor_LastReceiptCost]
ALTER TABLE [Purchasing].[ProductVendor]  WITH CHECK ADD  CONSTRAINT [CK_ProductVendor_MaxOrderQty] CHECK  (([MaxOrderQty]>=(1)))
ALTER TABLE [Purchasing].[ProductVendor] CHECK CONSTRAINT [CK_ProductVendor_MaxOrderQty]
ALTER TABLE [Purchasing].[ProductVendor]  WITH CHECK ADD  CONSTRAINT [CK_ProductVendor_MinOrderQty] CHECK  (([MinOrderQty]>=(1)))
ALTER TABLE [Purchasing].[ProductVendor] CHECK CONSTRAINT [CK_ProductVendor_MinOrderQty]
ALTER TABLE [Purchasing].[ProductVendor]  WITH CHECK ADD  CONSTRAINT [CK_ProductVendor_OnOrderQty] CHECK  (([OnOrderQty]>=(0)))
ALTER TABLE [Purchasing].[ProductVendor] CHECK CONSTRAINT [CK_ProductVendor_OnOrderQty]
ALTER TABLE [Purchasing].[ProductVendor]  WITH CHECK ADD  CONSTRAINT [CK_ProductVendor_StandardPrice] CHECK  (([StandardPrice]>(0.00)))
ALTER TABLE [Purchasing].[ProductVendor] CHECK CONSTRAINT [CK_ProductVendor_StandardPrice]
ALTER TABLE [Purchasing].[ProductVendor] ADD  CONSTRAINT [DF_ProductVendor_ModifiedDate]  DEFAULT (getdate()) FOR [ModifiedDate]

Powered by Yoyodyne © 1971