Should I use ISO country code for primary key, or integer ID?

Question

I have or am going to have a database with countries and their emissions. Countries in the API have a 3-letter ISO Code. Should I use this for identifying the countries in database, or just a plain numeric id?

    CREATE TABLE country
        (
            countryid integer PRIMARY KEY NOT NULL, 
        ),

    CREATE TABLE country_emissions
        (
            id integer PRIMARY KEY NOT NULL,
            countryid id NOT NULL references country(countryid),
            year smallint NOT NULL, 
            emissions integer 
        ),

So, could countryid in this case be replaced with countries' respectable ISO code?

Reason why I'm not sure is cause numbers seem more logical as you can't really typo them, where as you can have the ISO code written with lower case letters, etc. But then again, you wouldn't have to fetch the numeric ID of a country before adding emissions, you could just straight add them with the country ISO code.

Possible duplicate of [Is it good practice to always have an autoincrement integer primary key?](https://softwareengineering.stackexchange.com/questions/328458/is-it-good-practice-to-always-have-an-autoincrement-integer-primary-key) — gnat, Jan 14 '19 at 08:01
ISO 3166 specifies both numeric and alphanumeric codes for countries. — Pieter B, Jan 14 '19 at 10:08
@gnat its the same question, but the answer totally ignores the int vs something else thrust of it — Ewan, Jan 14 '19 at 11:04
Never use, as a primary key, values controlled by another party. Sooner or later you will have a good reason to add your own values, possibly also to delete existing ones. Sooner or later the other party will make changes which you don't want to track. — High Performance Mark, Jan 14 '19 at 13:11

score 6 · Answer 1 · answered Jan 14 '19 at 14:51

When you ask specifically about the ISO 3166 country codes and using them for a primary key in the database containing emissions data you should consider how static these codes are.

As an example (I haven't been able to verify that Sudan has not changed code as I have failed to look up any older specifications) Sudan (SDN) was in 2011 split up in two countries Sudan (SDN) and South Sudan (SSD).

If you in this case use the country code you may see that Sudan in 2011 suddenly drops in emissions, and your database do not give any explanation. There isn't anything in your model that can show you what has happened.

A different example is The Czech Republic (CZE) and Slovakia (SVK). In this case one country code (CSK) was split up into two new codes.

What you really need is to model how countries can merge and split. And in that case I would model Sudan prior to 2011 as a different entry in the database as Sudan after 2011, though of course there would be a clear reference between the two, both having the same country code.

You can then use the same references to model the break up of Czechoslovakia.

As an example of how complex a break up can be you can look at how Yugoslavia was broken up and when each part got their country code and how "Macedonia" has changed names and still is changing.

Wouldn't the subdivisions and relationships between countries would remain an issue regardless of whether the country id is a ISO code or a int? Either way you'd need to introduce references to other countries and form some kind of tree/graph/timeline. — Dev243, Jan 14 '19 at 19:03
But you can't use the same ISO code for more than one entry in the database if it is the primary key. — Bent, Jan 14 '19 at 22:01
The country codes, al thought very rare, can change, so I think I will stand by your point and not use the ISO code for identifying countries, as was also in the other linked thread. — user3553653, Jan 14 '19 at 23:47

score 0 · Accepted Answer · answered Apr 18 '19 at 04:00

According to pure database concepts, you should not use ISO country code as a primary key. You should use integer id as a primary key in your database tables. You can not implement auto increment in ISO country code as it may differ from country to country.

score -1 · Answer 3 · answered Jan 14 '19 at 11:07

As has been pointed in the comments, there are specifications for both numeric and alphanumeric codes. You could use both.

But in the end, it depends on what you are doing, the scope of your project and who will use the code.

If you are in doubt, I would use the alphanumeric code since it tends to be more clear. You could check the case of the argument setting everything to upper case before making the call to the database. You should sanitize the arguments anyway right? My two cents.

Should I use ISO country code for primary key, or integer ID?

3 Answers3