Person.BusinessEntityContact Table

Description

Cross-reference table mapping stores, vendors, and employees to people

Properties

Creation Date	27/10/2017 14:33
File Group	PRIMARY
Text File Group
System Object
Published for Replication
Rows	909
Data Space Used	48.00 KB
Index Space Used	144.00 KB

Columns

Column Name	Description	Datatype	Length	Default
BusinessEntityID	Primary key. Foreign key to BusinessEntity.BusinessEntityID.	Integer	4
PersonID	Primary key. Foreign key to Person.BusinessEntityID.	Integer	4
ContactTypeID	Primary key. Foreign key to ContactType.ContactTypeID.	Integer	4
rowguid	ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample.	GUID	16	(newid())
ModifiedDate	Date and time the record was last updated.	DBTimeStamp	4	(getdate())

Indexes

Index	Description	Primary	Unique
AK_BusinessEntityContact_rowguid	Unique nonclustered index. Used to support replication samples.
IX_BusinessEntityContact_ContactTypeID	Nonclustered index.
IX_BusinessEntityContact_PersonID	Nonclustered index.
PK_BusinessEntityContact_BusinessEntityID_PersonID_ContactTypeID	Primary key (clustered) constraint

Relationships

Relationship	Description
FK_BusinessEntityContact_BusinessEntity_BusinessEntityID	Foreign key constraint referencing BusinessEntity.BusinessEntityID.
FK_BusinessEntityContact_ContactType_ContactTypeID	Foreign key constraint referencing ContactType.ContactTypeID.
FK_BusinessEntityContact_Person_PersonID	Foreign key constraint referencing Person.BusinessEntityID.

Objects that depend on Person.BusinessEntityContact

Database Object	Object Type	Description	Dep Level
dbo.ufnGetContactInformation	User Defined Function	Table value function returning the first name, last name, job title and contact type for a given contact.	1
Sales.vStoreWithContacts	View	Stores (including store contacts) that sell Adventure Works Cycles products to consumers.	1
Purchasing.vVendorWithContacts	View	Vendor (company) names and the names of vendor employees to contact.	1

Objects that Person.BusinessEntityContact depends on

Database Object	Object Type	Description	Dep Level
Person.BusinessEntity	Table	Source of the ID that connects vendors, customers, and employees with address and contact information.	1
Person.ContactType	Table	Lookup table containing the types of business entity contacts.	1
dbo.Name	User Defined Data Type		2
dbo.NameStyle	User Defined Data Type		2
Person.Person	Table	Human 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]

Related Objects

Person Schema
AdventureWorks Database