AdventureWorks
| Table Name: | Person.StateProvince |
| Description: | State and province lookup table. |
| Primary Keys: | StateProvinceID |
| Field | Type | Defaults | Nulls? | Comments |
|---|---|---|---|---|
| StateProvinceID | int - Identity | None | No | Primary key for StateProvince records. |
| StateProvinceCode | nchar (3) | None | No | ISO standard state or province code. |
| CountryRegionCode | nvarchar (3) | None | No | ISO standard country or region code. Foreign key to CountryRegion.CountryRegionCode. |
| IsOnlyStateProvinceFlag | bit | ((1)) | No | 0 = StateProvinceCode exists. 1 = StateProvinceCode unavailable, using CountryRegionCode. |
| Name | nvarchar (50) | None | No | State or province description. |
| TerritoryID | int | None | No | ID of the territory in which the state or province is located. Foreign key to SalesTerritory.SalesTerritoryID. |
| rowguid | uniqueidentifier | (newid()) | No | ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample. |
| ModifiedDate | datetime | (getdate()) | No | Date and time the record was last updated. |
| Index | Clustered? | Unique? | Fields |
|---|---|---|---|
| PK_StateProvince_StateProvinceID | Yes | Yes | StateProvinceID |
| AK_StateProvince_Name | No | Yes | Name |
| AK_StateProvince_StateProvinceCode_CountryRegionCode | No | Yes | StateProvinceCode CountryRegionCode |
| AK_StateProvince_rowguid | No | Yes | rowguid |
| Internal Foreign Key Constraint | Affected Field | Source Table |
|---|---|---|
| FK_StateProvince_CountryRegion_CountryRegionCode | CountryRegionCode | Person.CountryRegion |
| FK_StateProvince_SalesTerritory_TerritoryID | TerritoryID | Sales.SalesTerritory |
| Primary Key as Foreign Key Constraint | Affected Table | Affected Field |
|---|---|---|
| FK_Address_StateProvince_StateProvinceID | Person.Address | StateProvinceID |
| FK_SalesTaxRate_StateProvince_StateProvinceID | Sales.SalesTaxRate | StateProvinceID |