AdventureWorks

Basic Index   Expanded Index

Table Name:Person.StateProvince
Description:State and province lookup table.
Primary Keys:StateProvinceID
FieldTypeDefaultsNulls?Comments
StateProvinceIDint - IdentityNoneNoPrimary key for StateProvince records.
StateProvinceCodenchar (3)NoneNoISO standard state or province code.
CountryRegionCodenvarchar (3)NoneNoISO standard country or region code. Foreign key to CountryRegion.CountryRegionCode.
IsOnlyStateProvinceFlagbit((1))No0 = StateProvinceCode exists. 1 = StateProvinceCode unavailable, using CountryRegionCode.
Namenvarchar (50)NoneNoState or province description.
TerritoryIDintNoneNoID of the territory in which the state or province is located. Foreign key to SalesTerritory.SalesTerritoryID.
rowguiduniqueidentifier(newid())NoROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample.
ModifiedDatedatetime(getdate())NoDate and time the record was last updated.

IndexClustered?Unique?Fields
PK_StateProvince_StateProvinceIDYesYesStateProvinceID
AK_StateProvince_NameNoYesName
AK_StateProvince_StateProvinceCode_CountryRegionCodeNoYesStateProvinceCode
CountryRegionCode
AK_StateProvince_rowguidNoYesrowguid

No check constraints
Internal Foreign Key ConstraintAffected FieldSource Table
FK_StateProvince_CountryRegion_CountryRegionCodeCountryRegionCodePerson.CountryRegion
FK_StateProvince_SalesTerritory_TerritoryIDTerritoryIDSales.SalesTerritory

Primary Key as Foreign Key ConstraintAffected TableAffected Field
FK_Address_StateProvince_StateProvinceIDPerson.AddressStateProvinceID
FK_SalesTaxRate_StateProvince_StateProvinceIDSales.SalesTaxRateStateProvinceID

No triggers