Troubleshooting: Receive SQL error when you enter the same state or province abbreviation for different countries in the STATEPROV table

If you want to display state or province information for multiple countries in the storefront, countries that share state or province abbreviations cause a conflict when inputted in the STATEABBR value in the table.

Problem

When you insert STATEABBR values into the STATEPROV table, if a state or province abbreviation is a duplicate of another country's, you receive the following error:
  • Apache DerbyDB2SQL0803N (SQLSTATE 23505)
  • OracleSQLSTATE 23000 diagnostic

Solution

To solve this problem:

Use countryabbr_stateabbr as the value of STATEABBR.
For example, for BC, Canada and BC, Mexico, use Canada_BC and Mexico_BC respectively.