Person.BusinessEntityAddress Table

Description

Cross-reference table mapping customers, vendors, and employees to their addresses.

Properties

Creation Date	27/10/2017 14:33
File Group	PRIMARY
Text File Group
System Object
Published for Replication
Rows	19614
Data Space Used	880.00 KB
Index Space Used	1,432.00 KB

Columns

Column Name	Description	Datatype	Length	Default
BusinessEntityID	Primary key. Foreign key to BusinessEntity.BusinessEntityID.	Integer	4
AddressID	Primary key. Foreign key to Address.AddressID.	Integer	4
AddressTypeID	Primary key. Foreign key to AddressType.AddressTypeID.	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_BusinessEntityAddress_rowguid	Unique nonclustered index. Used to support replication samples.
IX_BusinessEntityAddress_AddressID	Nonclustered index.
IX_BusinessEntityAddress_AddressTypeID	Nonclustered index.
PK_BusinessEntityAddress_BusinessEntityID_AddressID_AddressTypeID	Primary key (clustered) constraint

Relationships

Relationship	Description
FK_BusinessEntityAddress_Address_AddressID	Foreign key constraint referencing Address.AddressID.
FK_BusinessEntityAddress_AddressType_AddressTypeID	Foreign key constraint referencing AddressType.AddressTypeID.
FK_BusinessEntityAddress_BusinessEntity_BusinessEntityID	Foreign key constraint referencing BusinessEntity.BusinessEntityID.

Objects that depend on Person.BusinessEntityAddress

Database Object	Object Type	Description	Dep Level
HumanResources.vEmployee	View	Employee names and addresses.	1
Sales.vIndividualCustomer	View	Individual customers (names and addresses) that purchase Adventure Works Cycles products online.	1
Sales.vSalesPerson	View	Sales representiatives (names and addresses) and their sales-related information.	1
Sales.vStoreWithAddresses	View	Stores (including store addresses) that sell Adventure Works Cycles products to consumers.	1
Purchasing.vVendorWithAddresses	View	Vendor (company) names and addresses .	1

Objects that Person.BusinessEntityAddress depends on

Database Object	Object Type	Description	Dep Level
Person.Address	Table	Street address information for customers, employees, and vendors.	1
Person.AddressType	Table	Types of addresses stored in the Address table.	1
Person.BusinessEntity	Table	Source of the ID that connects vendors, customers, and employees with address and contact information.	1
Person.CountryRegion	Table	Lookup table containing the ISO standard codes for countries and regions.	3
dbo.Flag	User Defined Data Type		3
Sales.SalesTerritory	Table	Sales territory lookup table.	3
Person.StateProvince	Table	State and province lookup table.	2

SQL

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE TABLE [Person].[BusinessEntityAddress](
    [BusinessEntityID] [int] NOT NULL,
    [AddressID] [int] NOT NULL,
    [AddressTypeID] [int] NOT NULL,
    [rowguid] [uniqueidentifier] ROWGUIDCOL  NOT NULL,
    [ModifiedDate] [datetime] NOT NULL,
 CONSTRAINT [PK_BusinessEntityAddress_BusinessEntityID_AddressID_AddressTypeID] PRIMARY KEY CLUSTERED 
(
    [BusinessEntityID] ASC,
    [AddressID] ASC,
    [AddressTypeID] 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].[BusinessEntityAddress] ADD  CONSTRAINT [DF_BusinessEntityAddress_rowguid]  DEFAULT (newid()) FOR [rowguid]
ALTER TABLE [Person].[BusinessEntityAddress] ADD  CONSTRAINT [DF_BusinessEntityAddress_ModifiedDate]  DEFAULT (getdate()) FOR [ModifiedDate]
ALTER TABLE [Person].[BusinessEntityAddress]  WITH CHECK ADD  CONSTRAINT [FK_BusinessEntityAddress_Address_AddressID] FOREIGN KEY([AddressID])
REFERENCES [Person].[Address] ([AddressID])
ALTER TABLE [Person].[BusinessEntityAddress] CHECK CONSTRAINT [FK_BusinessEntityAddress_Address_AddressID]
ALTER TABLE [Person].[BusinessEntityAddress]  WITH CHECK ADD  CONSTRAINT [FK_BusinessEntityAddress_AddressType_AddressTypeID] FOREIGN KEY([AddressTypeID])
REFERENCES [Person].[AddressType] ([AddressTypeID])
ALTER TABLE [Person].[BusinessEntityAddress] CHECK CONSTRAINT [FK_BusinessEntityAddress_AddressType_AddressTypeID]
ALTER TABLE [Person].[BusinessEntityAddress]  WITH CHECK ADD  CONSTRAINT [FK_BusinessEntityAddress_BusinessEntity_BusinessEntityID] FOREIGN KEY([BusinessEntityID])
REFERENCES [Person].[BusinessEntity] ([BusinessEntityID])
ALTER TABLE [Person].[BusinessEntityAddress] CHECK CONSTRAINT [FK_BusinessEntityAddress_BusinessEntity_BusinessEntityID]

Related Objects

Person Schema
AdventureWorks Database