Sales.CustomerAddress Table

Description

Cross-reference table mapping customers to their address(es).

Properties

Creation Date	08/01/2010 08:40
File Group	PRIMARY
Text File Group
System Object
Published for Replication
Rows	19220
Data Space Used	864.00 KB
Index Space Used	552.00 KB

Columns

Column Name	Description	Datatype	Length	Default
CustomerID	Primary key. Foreign key to Customer.CustomerID.	int	4
AddressID	Primary key. Foreign key to Address.AddressID.	int	4
AddressTypeID	Address type. Foreign key to AddressType.AddressTypeID.	int	4
rowguid	ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample.	uniqueidentifier	16	(newid())
ModifiedDate	Date and time the record was last updated.	datetime	4	(getdate())

Indexes

Index	Description	Primary	Unique
AK_CustomerAddress_rowguid	Unique nonclustered index. Used to support replication samples.
PK_CustomerAddress_CustomerID_AddressID	Primary key (clustered) constraint

Relationships

Relationship	Description
FK_CustomerAddress_Address_AddressID	Foreign key constraint referencing Address.AddressID.
FK_CustomerAddress_AddressType_AddressTypeID	Foreign key constraint referencing AddressType.AddressTypeID.
FK_CustomerAddress_Customer_CustomerID	Foreign key constraint referencing Customer.CustomerID.

Objects that depend on Sales.CustomerAddress

	Database Object	Object Type	Description	Dep Level
	Sales.vIndividualCustomer	View	Individual customers (names and addresses) that purchase Adventure Works Cycles products online.	1
	Sales.vStoreWithDemographics	View	Stores (names and addresses) that sell Adventure Works Cycles products to consumers.	1

Objects that Sales.CustomerAddress 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.CountryRegion	Table	Lookup table containing the ISO standard codes for countries and regions.	3
Sales.Customer	Table	Current customer information. Also see the Individual and Store tables.	1
dbo.Flag	User Defined Data Type		3
Person.StateProvince	Table	State and province lookup table.	2
dbo.ufnLeadingZeros	User Defined Function	Scalar function used by the Sales.Customer table to help set the account number.	2

SQL

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE TABLE [Sales].[CustomerAddress](
	[CustomerID] [int] NOT NULL,
	[AddressID] [int] NOT NULL,
	[AddressTypeID] [int] NOT NULL,
	[rowguid] [uniqueidentifier] ROWGUIDCOL  NOT NULL,
	[ModifiedDate] [datetime] NOT NULL,
 CONSTRAINT [PK_CustomerAddress_CustomerID_AddressID] PRIMARY KEY CLUSTERED 
(
	[CustomerID] ASC,
	[AddressID] 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 [Sales].[CustomerAddress]  WITH CHECK ADD  CONSTRAINT [FK_CustomerAddress_Address_AddressID] FOREIGN KEY([AddressID])
REFERENCES [Address] ([AddressID])
ALTER TABLE [Sales].[CustomerAddress] CHECK CONSTRAINT [FK_CustomerAddress_Address_AddressID]
ALTER TABLE [Sales].[CustomerAddress]  WITH CHECK ADD  CONSTRAINT [FK_CustomerAddress_AddressType_AddressTypeID] FOREIGN KEY([AddressTypeID])
REFERENCES [AddressType] ([AddressTypeID])
ALTER TABLE [Sales].[CustomerAddress] CHECK CONSTRAINT [FK_CustomerAddress_AddressType_AddressTypeID]
ALTER TABLE [Sales].[CustomerAddress]  WITH CHECK ADD  CONSTRAINT [FK_CustomerAddress_Customer_CustomerID] FOREIGN KEY([CustomerID])
REFERENCES [Customer] ([CustomerID])
ALTER TABLE [Sales].[CustomerAddress] CHECK CONSTRAINT [FK_CustomerAddress_Customer_CustomerID]
ALTER TABLE [Sales].[CustomerAddress] ADD  CONSTRAINT [DF_CustomerAddress_rowguid]  DEFAULT (newid()) FOR [rowguid]
ALTER TABLE [Sales].[CustomerAddress] ADD  CONSTRAINT [DF_CustomerAddress_ModifiedDate]  DEFAULT (getdate()) FOR [ModifiedDate]

Related Objects

Sales Schema
AdventureWorks Database