Purchasing.ProductVendor Table

Description

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

Properties

Creation Date	08/01/2010 08:40
File Group	PRIMARY
Text File Group
System Object
Published for Replication
Rows	460
Data Space Used	40.00 KB
Index Space Used	48.00 KB

Columns

Column Name	Description	Datatype	Length	Default
ProductID	Primary key. Foreign key to Product.ProductID.	int	4
VendorID	Primary key. Foreign key to Vendor.VendorID.	int	4
AverageLeadTime	The average span of time (in days) between placing an order with the vendor and receiving the purchased product.	int	4
StandardPrice	The vendor's usual selling price.	money	8
LastReceiptCost	The selling price when last purchased.	money	8
LastReceiptDate	Date the product was last received by the vendor.	datetime	4
MinOrderQty	The maximum quantity that should be ordered.	int	4
MaxOrderQty	The minimum quantity that should be ordered.	int	4
OnOrderQty	The quantity currently on order.	int	4
UnitMeasureCode	The product's unit of measure.	nchar	3
ModifiedDate	Date and time the record was last updated.	datetime	4	(getdate())

Indexes

Index	Description	Primary	Unique
IX_ProductVendor_UnitMeasureCode	Nonclustered index.
IX_ProductVendor_VendorID	Nonclustered index.
PK_ProductVendor_ProductID_VendorID	Primary key (clustered) constraint

Check Constraints

Name	Description	Expression
CK_ProductVendor_AverageLeadTime	Check constraint [AverageLeadTime] >= (1)	([AverageLeadTime]>=(1))
CK_ProductVendor_LastReceiptCost	Check constraint [LastReceiptCost] > (0.00)	([LastReceiptCost]>(0.00))
CK_ProductVendor_MaxOrderQty	Check constraint [MaxOrderQty] >= (1)	([MaxOrderQty]>=(1))
CK_ProductVendor_MinOrderQty	Check constraint [MinOrderQty] >= (1)	([MinOrderQty]>=(1))
CK_ProductVendor_OnOrderQty	Check constraint [OnOrderQty] >= (0)	([OnOrderQty]>=(0))
CK_ProductVendor_StandardPrice	Check constraint [StandardPrice] > (0.00)	([StandardPrice]>(0.00))

Relationships

Relationship	Description
FK_ProductVendor_Product_ProductID	Foreign key constraint referencing Product.ProductID.
FK_ProductVendor_UnitMeasure_UnitMeasureCode	Foreign key constraint referencing UnitMeasure.UnitMeasureCode.
FK_ProductVendor_Vendor_VendorID	Foreign key constraint referencing Vendor.VendorID.

Objects that Purchasing.ProductVendor depends on

Database Object	Object Type	Description	Dep Level
dbo.AccountNumber	User Defined Data Type		2
dbo.ErrorLog	Table	Audit table tracking errors in the the AdventureWorks database that are caught by the CATCH block of a TRY...CATCH construct. Data is inserted by stored procedure dbo.uspLogError when it is executed from inside the CATCH block of a TRY...CATCH construct.	3
dbo.Flag	User Defined Data Type		2
dbo.Name	User Defined Data Type		2
Production.Product	Table	Products sold or used in the manfacturing of sold products.	1
Production.ProductCategory	Table	High-level product categorization.	3
Production.ProductModel	Table	Product model classification.	2
Production.ProductSubcategory	Table	Product subcategories. See ProductCategory table.	2
Production.UnitMeasure	Table	Unit of measure lookup table.	1
dbo.uspLogError	Stored Procedure	Logs error information in the ErrorLog table about the error that caused execution to jump to the CATCH block of a TRY...CATCH construct. Should be executed from within the scope of a CATCH block otherwise it will return without inserting error information.	2
dbo.uspPrintError	Stored Procedure	Prints error information about the error that caused execution to jump to the CATCH block of a TRY...CATCH construct. Should be executed from within the scope of a CATCH block otherwise it will return without printing any error information.	2
Purchasing.Vendor	Table	Companies from whom Adventure Works Cycles purchases parts or other goods.	1

SQL

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
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,
	[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 [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 [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 [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]

Related Objects