Northwind Database (Document! X Sample)
Person.BusinessEntityContact Table
AdventureWorks Database > Person Schema : Person.BusinessEntityContact Table
Description
Cross-reference table mapping stores, vendors, and employees to people
Properties
Creation Date27/10/2017 14:33
File GroupPRIMARY
Text File Group
System Object
Published for Replication
Rows909
Data Space Used48.00 KB
Index Space Used144.00 KB
Columns
 Column NameDescriptionDatatypeLengthAllow NullsDefaultFormula
Primary Key Primary key. Foreign key to BusinessEntity.BusinessEntityID.Integer4   
Primary Key Primary key. Foreign key to Person.BusinessEntityID.Integer4   
Primary Key Primary key. Foreign key to ContactType.ContactTypeID.Integer4   
 ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample.GUID16 
(newid())
 
 Date and time the record was last updated.DBTimeStamp4 
(getdate())
 
Indexes
IndexDescriptionPrimaryUnique
Unique nonclustered index. Used to support replication samples. 
Nonclustered index.  
Nonclustered index.  
Primary key (clustered) constraint
Relationships
RelationshipDescription
Foreign key constraint referencing BusinessEntity.BusinessEntityID.
Foreign key constraint referencing ContactType.ContactTypeID.
Foreign key constraint referencing Person.BusinessEntityID.
Objects that depend on Person.BusinessEntityContact
 Database ObjectObject TypeDescriptionDep Level
dbo.ufnGetContactInformation functiondbo.ufnGetContactInformationUser Defined FunctionTable value function returning the first name, last name, job title and contact type for a given contact.1
Sales.vStoreWithContacts viewSales.vStoreWithContactsViewStores (including store contacts) that sell Adventure Works Cycles products to consumers.1
Purchasing.vVendorWithContacts viewPurchasing.vVendorWithContactsViewVendor (company) names and the names of vendor employees to contact.1
Objects that Person.BusinessEntityContact depends on
 Database ObjectObject TypeDescriptionDep Level
Person.BusinessEntity tablePerson.BusinessEntityTableSource of the ID that connects vendors, customers, and employees with address and contact information.1
Person.ContactType tablePerson.ContactTypeTableLookup table containing the types of business entity contacts.1
dbo.Name datatypedbo.NameUser Defined Data Type 2
dbo.NameStyle datatypedbo.NameStyleUser Defined Data Type 2
Person.Person tablePerson.PersonTableHuman beings involved with AdventureWorks: employees, customer contacts, and vendor contacts.1
SQL
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE TABLE [Person].[BusinessEntityContact](
    [BusinessEntityID] [int] NOT NULL,
    [PersonID] [int] NOT NULL,
    [ContactTypeID] [int] NOT NULL,
    [rowguid] [uniqueidentifier] ROWGUIDCOL  NOT NULL,
    [ModifiedDate] [datetime] NOT NULL,
 CONSTRAINT [PK_BusinessEntityContact_BusinessEntityID_PersonID_ContactTypeID] PRIMARY KEY CLUSTERED 
(
    [BusinessEntityID] ASC,
    [PersonID] ASC,
    [ContactTypeID] 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 [Person].[BusinessEntityContact] ADD  CONSTRAINT [DF_BusinessEntityContact_rowguid]  DEFAULT (newid()) FOR [rowguid]
ALTER TABLE [Person].[BusinessEntityContact] ADD  CONSTRAINT [DF_BusinessEntityContact_ModifiedDate]  DEFAULT (getdate()) FOR [ModifiedDate]
ALTER TABLE [Person].[BusinessEntityContact]  WITH CHECK ADD  CONSTRAINT [FK_BusinessEntityContact_BusinessEntity_BusinessEntityID] FOREIGN KEY([BusinessEntityID])
REFERENCES [Person].[BusinessEntity] ([BusinessEntityID])
ALTER TABLE [Person].[BusinessEntityContact] CHECK CONSTRAINT [FK_BusinessEntityContact_BusinessEntity_BusinessEntityID]
ALTER TABLE [Person].[BusinessEntityContact]  WITH CHECK ADD  CONSTRAINT [FK_BusinessEntityContact_ContactType_ContactTypeID] FOREIGN KEY([ContactTypeID])
REFERENCES [Person].[ContactType] ([ContactTypeID])
ALTER TABLE [Person].[BusinessEntityContact] CHECK CONSTRAINT [FK_BusinessEntityContact_ContactType_ContactTypeID]
ALTER TABLE [Person].[BusinessEntityContact]  WITH CHECK ADD  CONSTRAINT [FK_BusinessEntityContact_Person_PersonID] FOREIGN KEY([PersonID])
REFERENCES [Person].[Person] ([BusinessEntityID])
ALTER TABLE [Person].[BusinessEntityContact] CHECK CONSTRAINT [FK_BusinessEntityContact_Person_PersonID]
See Also

Related Objects

Person Schema
AdventureWorks Database