Person.PersonPhone Table

Description

Telephone number and type of a person.

Properties

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

Columns

Column Name	Description	Datatype	Length	Default
BusinessEntityID	Business entity identification number. Foreign key to Person.BusinessEntityID.	Integer	4
PhoneNumber	Telephone number identification number.	dbo.Phone	25
PhoneNumberTypeID	Kind of phone number. Foreign key to PhoneNumberType.PhoneNumberTypeID.	Integer	4
ModifiedDate	Date and time the record was last updated.	DBTimeStamp	4	(getdate())

Indexes

Index	Description	Primary	Unique
IX_PersonPhone_PhoneNumber	Nonclustered index.
PK_PersonPhone_BusinessEntityID_PhoneNumber_PhoneNumberTypeID	Primary key (clustered) constraint

Relationships

Relationship	Description
FK_PersonPhone_Person_BusinessEntityID	Foreign key constraint referencing Person.BusinessEntityID.
FK_PersonPhone_PhoneNumberType_PhoneNumberTypeID	Foreign key constraint referencing PhoneNumberType.PhoneNumberTypeID.

Objects that depend on Person.PersonPhone

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.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.PersonPhone 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.	2
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
dbo.Phone	User Defined Data Type		1
Person.PhoneNumberType	Table	Type of phone number of a person.	1

SQL

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE TABLE [Person].[PersonPhone](
    [BusinessEntityID] [int] NOT NULL,
    [PhoneNumber] [dbo].[Phone] NOT NULL,
    [PhoneNumberTypeID] [int] NOT NULL,
    [ModifiedDate] [datetime] NOT NULL,
 CONSTRAINT [PK_PersonPhone_BusinessEntityID_PhoneNumber_PhoneNumberTypeID] PRIMARY KEY CLUSTERED 
(
    [BusinessEntityID] ASC,
    [PhoneNumber] ASC,
    [PhoneNumberTypeID] 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].[PersonPhone] ADD  CONSTRAINT [DF_PersonPhone_ModifiedDate]  DEFAULT (getdate()) FOR [ModifiedDate]
ALTER TABLE [Person].[PersonPhone]  WITH CHECK ADD  CONSTRAINT [FK_PersonPhone_Person_BusinessEntityID] FOREIGN KEY([BusinessEntityID])
REFERENCES [Person].[Person] ([BusinessEntityID])
ALTER TABLE [Person].[PersonPhone] CHECK CONSTRAINT [FK_PersonPhone_Person_BusinessEntityID]
ALTER TABLE [Person].[PersonPhone]  WITH CHECK ADD  CONSTRAINT [FK_PersonPhone_PhoneNumberType_PhoneNumberTypeID] FOREIGN KEY([PhoneNumberTypeID])
REFERENCES [Person].[PhoneNumberType] ([PhoneNumberTypeID])
ALTER TABLE [Person].[PersonPhone] CHECK CONSTRAINT [FK_PersonPhone_PhoneNumberType_PhoneNumberTypeID]

Related Objects

Person Schema
AdventureWorks Database