Northwind Database (Document! X Sample)
Purchasing.Vendor Table
AdventureWorks Database > Purchasing Schema : Purchasing.Vendor Table
Description
Companies from whom Adventure Works Cycles purchases parts or other goods.
Properties
Creation Date27/10/2017 14:33
File GroupPRIMARY
Text File Group
System Object
Published for Replication
Rows104
Data Space Used16.00 KB
Index Space Used32.00 KB
Columns
 Column NameDescriptionDatatypeLengthAllow NullsDefaultFormula
Primary Key Primary key for Vendor records. Foreign key to BusinessEntity.BusinessEntityIDInteger4   
 Vendor account (identification) number.dbo.AccountNumber15   
 Company name.dbo.Name50   
 1 = Superior, 2 = Excellent, 3 = Above average, 4 = Average, 5 = Below averageUnsignedTinyInt1   
 0 = Do not use if another vendor is available. 1 = Preferred over other vendors supplying the same product.dbo.Flag1 
((1))
 
 0 = Vendor no longer used. 1 = Vendor is actively used.dbo.Flag1 
((1))
 
 Vendor URL.VarWChar1024  
 Date and time the record was last updated.DBTimeStamp4 
(getdate())
 
Indexes
IndexDescriptionPrimaryUnique
Unique nonclustered index. 
Primary key (clustered) constraint
Check Constraints
NameDescriptionExpression
Check constraint [CreditRating] BETWEEN (1) AND (5)
([CreditRating]>=(1) AND [CreditRating]<=(5))
Triggers
TriggerDescription
INSTEAD OF DELETE trigger which keeps Vendors from being deleted.
Relationships
RelationshipDescription
Foreign key constraint referencing BusinessEntity.BusinessEntityID
Objects that depend on Purchasing.Vendor
 Database ObjectObject TypeDescriptionDep Level
dVendor triggerdVendorTriggerINSTEAD OF DELETE trigger which keeps Vendors from being deleted.1
iPurchaseOrderDetail triggeriPurchaseOrderDetailTriggerAFTER INSERT trigger that inserts a row in the TransactionHistory table and updates the PurchaseOrderHeader.SubTotal column.3
Purchasing.ProductVendor tablePurchasing.ProductVendorTable

Vendors are added to the table before any POs are processed.

1
Purchasing.PurchaseOrderDetail tablePurchasing.PurchaseOrderDetailTableIndividual products associated with a specific purchase order. See PurchaseOrderHeader.2
Purchasing.PurchaseOrderHeader tablePurchasing.PurchaseOrderHeaderTableGeneral purchase order information. See PurchaseOrderDetail.1
dbo.ufnGetContactInformation functiondbo.ufnGetContactInformationUser Defined FunctionTable value function returning the first name, last name, job title and contact type for a given contact.1
uPurchaseOrderDetail triggeruPurchaseOrderDetailTriggerAFTER UPDATE trigger that inserts a row in the TransactionHistory table, updates ModifiedDate in PurchaseOrderDetail and updates the PurchaseOrderHeader.SubTotal column.3
uPurchaseOrderHeader triggeruPurchaseOrderHeaderTriggerAFTER UPDATE trigger that updates the RevisionNumber and ModifiedDate columns in the PurchaseOrderHeader table.2
Purchasing.vVendorWithAddresses viewPurchasing.vVendorWithAddressesViewVendor (company) names and addresses .1
Purchasing.vVendorWithContacts viewPurchasing.vVendorWithContactsViewVendor (company) names and the names of vendor employees to contact.1
Objects that Purchasing.Vendor depends on
 Database ObjectObject TypeDescriptionDep Level
dbo.AccountNumber datatypedbo.AccountNumberUser Defined Data Type 1
Person.BusinessEntity tablePerson.BusinessEntityTableSource of the ID that connects vendors, customers, and employees with address and contact information.1
dbo.ErrorLog tabledbo.ErrorLogTableAudit 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 datatypedbo.FlagUser Defined Data Type 1
dbo.Name datatypedbo.NameUser Defined Data Type 1
dbo.uspLogError proceduredbo.uspLogErrorStored ProcedureLogs 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 proceduredbo.uspPrintErrorStored ProcedurePrints 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](
    [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]
See Also

Related Objects

Purchasing Schema
AdventureWorks Database