Person.StateProvince - AdventureWorks

AdventureWorks

- AdventureWorks

Documentation	AdventureWorks
Schema	Person
Name	StateProvince
Module	People

State and province lookup table.

Columns

		Key	Name	Data type	Null	Attributes	References	Description
	1		StateProvinceID	int		Identity		Primary key for StateProvince records.
Primary key for StateProvince records. Identity
	2		StateProvinceCode	nchar(3)				ISO standard state or province code.
ISO standard state or province code.
	3		CountryRegionCode	nvarchar(3)			Person.CountryRegion	ISO standard country or region code. Foreign key to CountryRegion.CountryRegionCode.
ISO standard country or region code. Foreign key to CountryRegion.CountryRegionCode. References: Person.CountryRegion
	4		IsOnlyStateProvinceFlag	bit		Default: 1		0 = StateProvinceCode exists. 1 = StateProvinceCode unavailable, using CountryRegionCode.
0 = StateProvinceCode exists. 1 = StateProvinceCode unavailable, using CountryRegionCode. Default: 1
	5		Name	nvarchar(50)				State or province description.
State or province description.
	6		TerritoryID	int			Sales.SalesTerritory	ID of the territory in which the state or province is located. Foreign key to SalesTerritory.SalesTerritoryID.
ID of the territory in which the state or province is located. Foreign key to SalesTerritory.SalesTerritoryID. References: Sales.SalesTerritory
	7		rowguid	uniqueidentifier		Default: newid()		ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample.
ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample. Default: newid()
	8		ModifiedDate	datetime		Default: getdate()		Date and time the record was last updated.
Date and time the record was last updated. Default: getdate()

Relations

Foreign table		Primary table	Join	Title / Name / Description
Person.StateProvince		Person.CountryRegion	Person.StateProvince.CountryRegionCode = Person.CountryRegion.CountryRegionCode	FK_StateProvince_CountryRegion_CountryRegionCode Foreign key constraint referencing CountryRegion.CountryRegionCode.
Person.StateProvince.CountryRegionCode = Person.CountryRegion.CountryRegionCode Name: FK_StateProvince_CountryRegion_CountryRegionCode Foreign key constraint referencing CountryRegion.CountryRegionCode.
Person.StateProvince		Sales.SalesTerritory	Person.StateProvince.TerritoryID = Sales.SalesTerritory.TerritoryID	FK_StateProvince_SalesTerritory_TerritoryID Foreign key constraint referencing SalesTerritory.TerritoryID.
Person.StateProvince.TerritoryID = Sales.SalesTerritory.TerritoryID Name: FK_StateProvince_SalesTerritory_TerritoryID Foreign key constraint referencing SalesTerritory.TerritoryID.
Person.Address		Person.StateProvince	Person.Address.StateProvinceID = Person.StateProvince.StateProvinceID	FK_Address_StateProvince_StateProvinceID Foreign key constraint referencing StateProvince.StateProvinceID.
Person.Address.StateProvinceID = Person.StateProvince.StateProvinceID Name: FK_Address_StateProvince_StateProvinceID Foreign key constraint referencing StateProvince.StateProvinceID.
Sales.SalesTaxRate		Person.StateProvince	Sales.SalesTaxRate.StateProvinceID = Person.StateProvince.StateProvinceID	FK_SalesTaxRate_StateProvince_StateProvinceID Foreign key constraint referencing StateProvince.StateProvinceID.
Sales.SalesTaxRate.StateProvinceID = Person.StateProvince.StateProvinceID Name: FK_SalesTaxRate_StateProvince_StateProvinceID Foreign key constraint referencing StateProvince.StateProvinceID.

Unique keys

	Key name	Columns	Description
	PK_StateProvince_StateProvinceID	StateProvinceID	Primary key (clustered) constraint
Primary key (clustered) constraint
	AK_StateProvince_Name	Name	Unique nonclustered index.
Unique nonclustered index.
	AK_StateProvince_rowguid	rowguid	Unique nonclustered index. Used to support replication samples.
Unique nonclustered index. Used to support replication samples.
	AK_StateProvince_StateProvinceCode_CountryRegionCode	StateProvinceCode, CountryRegionCode	Unique nonclustered index.
Unique nonclustered index.

Uses

Name
Person.StateProvince
Person.CountryRegion
Sales.SalesTerritory

Used by

Name
Person.StateProvince
HumanResources.vEmployee
Person.vStateProvinceCountryRegion
Purchasing.vVendorWithAddresses
Sales.vIndividualCustomer
Sales.vSalesPerson
Sales.vStoreWithAddresses
Person.Address
Sales.SalesTaxRate

Exported: 2019-02-04 23:13, Last imported: 2018-03-07 11:56