SQL Scripts - Countries

Written on June 17, 2009

A new project has me writing up the same old country/state/province reference tables. My feeling is that these static (fairly static) entities should be normalized and referenced by foreign key. I had asked a StackOverflow question on whether other developers had this prebuilt set of country/state/province create scripts in their toolbelt.

Create The Schema

Create your Country and State tables with this SQL script. As always, name the State table whatever you like (ProvState, tblState, whatever). Some folks don’t like table names to be the same as reserved keywords.

country_state_erd

Populating

Here is a collection of insert scripts to get the data populated quickly for you.

Have Fun

Some developers go further down the normalization path by creating a City table, but I usually pass on that. As always, that decision is largely dependant on the problem domain or task at hand. Yippee for me - that’ll be fun trying to reconcile data when someone enters their city location incorrectly as “St. Paul” / “Tuscon” / “Pittsburg” instead of Saint Paul / Tucson / Pittsburgh. If you would like to contribute an insert script or two for a country that you would like to see here, just tweet at me with your script!

Written on June 17, 2009