Database reference - AdventureWorks

AdventureWorks -  tables -  [Purchasing].[VendorContact]

Description

Cross-reference table mapping vendors and their employees. 

Table properties

namevalue
name[Purchasing].[VendorContact]
createdMar 19 2009 9:08PM
modifiedMar 19 2009 9:08PM 
ansi nullson 
quoted identifieron 
row count156
Size of data8 kb
Size of indexes40 kb
Maximum size of a single row20 bytes

Columns

columndatatypelengthbytesdefaultnullsPKFKUQcomputedcomment
VendorIDint104 no  composite PK  Vendor.VendorID      no  Primary key. 
ContactIDint104 no  composite PK  Contact.ContactID      no  Contact (Vendor employee) identification number. Foreign key to Contact.ContactID. 
ContactTypeIDint104 no    ContactType.ContactTypeID      no  Contact type such as sales manager, or sales agent. 
ModifiedDatedatetime238(getdate())no      no  Date and time the record was last updated. 

Indexes

namedescriptioncolumncomment
IX_VendorContact_ContactIDnonclustered located on PRIMARYContactIDNonclustered index. 
IX_VendorContact_ContactTypeIDnonclustered located on PRIMARYContactTypeIDNonclustered index. 
PK_VendorContact_VendorID_ContactIDclustered, unique, primary key located on PRIMARYVendorID, ContactIDClustered index created by a primary key constraint. 

References

name
Contact
ContactType
Vendor

Foreign key graph

ContactContactTypeVendorVendorContact

Foreign keys

namecolumnsforeign columnstypecomment
FK_VendorContact_Vendor_VendorIDVendorIDVendor.VendorID   Foreign key constraint referencing Vendor.VendorID. 
FK_VendorContact_Contact_ContactIDContactIDContact.ContactID   Foreign key constraint referencing Contact.ContactID. 
FK_VendorContact_ContactType_ContactTypeIDContactTypeIDContactType.ContactTypeID   Foreign key constraint referencing ContactType.ContactTypeID. 

Defaults

default namecolumncomment
DF_VendorContact_ModifiedDateModifiedDateDefault constraint value of GETDATE() 

Dependency graph

DF_VendorContact_ModifiedDateVendorContactIX_VendorContact_ContactID...IX_VendorContact_ContactTypeID...PK_VendorContact_VendorID_ContactID...ufnGetContactInformationvVendor

Objects that depend on [Purchasing].[VendorContact]

nameobject typelevel
ufnGetContactInformationmulti-statement table valued function1
vVendorview1

Sample rows

VendorIDContactIDContactTypeIDModifiedDate
1  610  2  2/25/2002 12:00:00 AM 
2  678  2  2/17/2002 12:00:00 AM 
2  947  17  2/17/2002 12:00:00 AM 
2  974  19  2/17/2002 12:00:00 AM 
3  696  18  3/5/2002 12:00:00 AM 
4  620  19  1/24/2002 12:00:00 AM 
5  664  18  3/1/2002 12:00:00 AM 
6  628  17  3/1/2002 12:00:00 AM 
7  683  18  3/5/2002 12:00:00 AM 
8  654  17  1/24/2002 12:00:00 AM 

Code

CREATE TABLE [Purchasing].[VendorContact](
    [VendorID] [int] NOT NULL,
    [ContactID] [int] NOT NULL,
    [VendorID] [int] NOT NULL,
    [ContactTypeID] [int] NOT NULL,
    [ModifiedDate] [datetime] NOT NULL,
 CONSTRAINT [PK_VendorContact_VendorID_ContactID] PRIMARY KEY CLUSTERED 
(
    [VendorID] ASC,
    [ContactID] 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].[VendorContact]  WITH CHECK ADD  CONSTRAINT [FK_VendorContact_Contact_ContactID] FOREIGN KEY([ContactID])
REFERENCES [Person].[Contact] ([ContactID])
ALTER TABLE [Purchasing].[VendorContact] CHECK CONSTRAINT [FK_VendorContact_Contact_ContactID]
ALTER TABLE [Purchasing].[VendorContact]  WITH CHECK ADD  CONSTRAINT [FK_VendorContact_ContactType_ContactTypeID] FOREIGN KEY([ContactTypeID])
REFERENCES [Person].[ContactType] ([ContactTypeID])
ALTER TABLE [Purchasing].[VendorContact] CHECK CONSTRAINT [FK_VendorContact_ContactType_ContactTypeID]
ALTER TABLE [Purchasing].[VendorContact]  WITH CHECK ADD  CONSTRAINT [FK_VendorContact_Vendor_VendorID] FOREIGN KEY([VendorID])
REFERENCES [Purchasing].[Vendor] ([VendorID])
ALTER TABLE [Purchasing].[VendorContact] CHECK CONSTRAINT [FK_VendorContact_Vendor_VendorID]
ALTER TABLE [Purchasing].[VendorContact] ADD  CONSTRAINT [DF_VendorContact_ModifiedDate]  DEFAULT (getdate()) FOR [ModifiedDate]

Powered by Yoyodyne © 1971