Purchasing.Vendor Table

Description

Companies from whom Adventure Works Cycles purchases parts or other goods.

Properties

Creation Date	08/01/2010 08:40
File Group	PRIMARY
Text File Group
System Object
Published for Replication
Rows	104
Data Space Used	16.00 KB
Index Space Used	32.00 KB

Columns

Column Name	Description	Datatype	Length	Default
VendorID	Primary key for Vendor records.	int	4
AccountNumber	Vendor account (identification) number.	dbo.AccountNumber	15
Name	Company name.	dbo.Name	50
CreditRating	1 = Superior, 2 = Excellent, 3 = Above average, 4 = Average, 5 = Below average	tinyint	1
PreferredVendorStatus	0 = Do not use if another vendor is available. 1 = Preferred over other vendors supplying the same product.	dbo.Flag	1	((1))
ActiveFlag	0 = Vendor no longer used. 1 = Vendor is actively used.	dbo.Flag	1	((1))
PurchasingWebServiceURL	Vendor URL.	nvarchar	1024
ModifiedDate	Date and time the record was last updated.	datetime	4	(getdate())

Indexes

Index	Description	Primary	Unique
AK_Vendor_AccountNumber	Unique nonclustered index.
PK_Vendor_VendorID	Primary key (clustered) constraint

Check Constraints

Name	Description	Expression
CK_Vendor_CreditRating	Check constraint [CreditRating] BETWEEN (1) AND (5)	([CreditRating]>=(1) AND [CreditRating]<=(5))

Triggers

Trigger	Description
dVendor	INSTEAD OF DELETE trigger which keeps Vendors from being deleted.

Relationships

Relationship	Description
FK_ProductVendor_Vendor_VendorID	Foreign key constraint referencing Vendor.VendorID.
FK_PurchaseOrderHeader_Vendor_VendorID	Foreign key constraint referencing Vendor.VendorID.
FK_VendorAddress_Vendor_VendorID	Foreign key constraint referencing Vendor.VendorID.
FK_VendorContact_Vendor_VendorID	Foreign key constraint referencing Vendor.VendorID.

Objects that depend on Purchasing.Vendor

Database Object	Object Type	Description	Dep Level
dVendor	Trigger	INSTEAD OF DELETE trigger which keeps Vendors from being deleted.	1
iPurchaseOrderDetail	Trigger	AFTER INSERT trigger that inserts a row in the TransactionHistory table and updates the PurchaseOrderHeader.SubTotal column.	3
Purchasing.ProductVendor	Table	Cross-reference table mapping vendors with the products they supply.	1
Purchasing.PurchaseOrderDetail	Table	Individual products associated with a specific purchase order. See PurchaseOrderHeader.	2
Purchasing.PurchaseOrderHeader	Table	General purchase order information. See PurchaseOrderDetail.	1
dbo.ufnGetContactInformation	User Defined Function	Table value function returning the first name, last name, job title and contact type for a given contact.	2
uPurchaseOrderDetail	Trigger	AFTER UPDATE trigger that inserts a row in the TransactionHistory table, updates ModifiedDate in PurchaseOrderDetail and updates the PurchaseOrderHeader.SubTotal column.	3
uPurchaseOrderHeader	Trigger	AFTER UPDATE trigger that updates the RevisionNumber and ModifiedDate columns in the PurchaseOrderHeader table.	2
Purchasing.VendorAddress	Table	Cross-reference mapping vendors and addresses.	1
Purchasing.VendorContact	Table	Cross-reference table mapping vendors and their employees.	1
Purchasing.vVendor	View	Vendor (company) names and addresses and the names of vendor employees to contact.	1

Objects that Purchasing.Vendor depends on

Database Object	Object Type	Description	Dep Level
dbo.AccountNumber	User Defined Data Type		1
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.	2
dbo.Flag	User Defined Data Type		1
dbo.Name	User Defined Data Type		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.	1
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.	1

SQL

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE TABLE [Purchasing].[Vendor](
	[VendorID] [int] IDENTITY(1,1) NOT NULL,
	[AccountNumber] [dbo].[AccountNumber] NOT NULL,
	[Name] [dbo].[Name] NOT NULL,
	[CreditRating] [tinyint] NOT NULL,
	[PreferredVendorStatus] [dbo].[Flag] NOT NULL,
	[ActiveFlag] [dbo].[Flag] NOT NULL,
	[PurchasingWebServiceURL] [nvarchar](1024) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[ModifiedDate] [datetime] NOT NULL,
 CONSTRAINT [PK_Vendor_VendorID] PRIMARY KEY CLUSTERED 
(
	[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].[Vendor]  WITH CHECK ADD  CONSTRAINT [CK_Vendor_CreditRating] CHECK  (([CreditRating]>=(1) AND [CreditRating]<=(5)))
ALTER TABLE [Purchasing].[Vendor] CHECK CONSTRAINT [CK_Vendor_CreditRating]
ALTER TABLE [Purchasing].[Vendor] ADD  CONSTRAINT [DF_Vendor_PreferredVendorStatus]  DEFAULT ((1)) FOR [PreferredVendorStatus]
ALTER TABLE [Purchasing].[Vendor] ADD  CONSTRAINT [DF_Vendor_ActiveFlag]  DEFAULT ((1)) FOR [ActiveFlag]
ALTER TABLE [Purchasing].[Vendor] ADD  CONSTRAINT [DF_Vendor_ModifiedDate]  DEFAULT (getdate()) FOR [ModifiedDate]

Related Objects