Purchasing.Vendor Table

In This Topic

Description

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

Properties

Creation Date	27/10/2017 14:33
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
BusinessEntityID	Primary key for Vendor records. Foreign key to BusinessEntity.BusinessEntityID	Integer	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	UnsignedTinyInt	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.	VarWChar	1024
ModifiedDate	Date and time the record was last updated.	DBTimeStamp	4	(getdate())

Indexes

Index	Description	Primary	Unique
AK_Vendor_AccountNumber	Unique nonclustered index.
PK_Vendor_BusinessEntityID	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_Vendor_BusinessEntity_BusinessEntityID	Foreign key constraint referencing BusinessEntity.BusinessEntityID

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	Vendors are added to the table before any POs are processed.	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.	1
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.vVendorWithAddresses	View	Vendor (company) names and addresses .	2
Purchasing.vVendorWithContacts	View	Vendor (company) names 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		2
Person.BusinessEntity	Table	Source of the ID that connects vendors, customers, and employees with address and contact information.	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		4
dbo.Name	User Defined Data Type		2
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

SQL

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE TABLE [Purchasing].[Vendor](
    [BusinessEntityID] [int] 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_BusinessEntityID] PRIMARY KEY CLUSTERED 
(
    [BusinessEntityID] 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] 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]
ALTER TABLE [Purchasing].[Vendor]  WITH CHECK ADD  CONSTRAINT [FK_Vendor_BusinessEntity_BusinessEntityID] FOREIGN KEY([BusinessEntityID])
REFERENCES [Person].[BusinessEntity] ([BusinessEntityID])
ALTER TABLE [Purchasing].[Vendor] CHECK CONSTRAINT [FK_Vendor_BusinessEntity_BusinessEntityID]
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]

Send comments on this topic.

Related Objects