Should I use ISO 3166-1 Numeric codes as Primary Keys for Countries in a database table?

Question

My main concern is, will one country ever adopt a numeric code that has been used for another in the past, such as can happen with alphanumeric 3166-1 codes? If not, then it seems like a perfect identifier for a primary key in a database...

Answer 1

Coming from a pure database perspective. No you should not. For the following reasons:

1. According to this: http://en.wikipedia.org/wiki/ISO_3166-1_numeric#Deleted_codes some codes have been changed. While this will create a mess in all systems this mess will be compounded by the fact that primary keys tend to be harder to change than normal columns.
2. Security reasons: see this: https://stackoverflow.com/questions/7451348/should-primary-keys-of-mysql-tables-be-exposed
3. Have something such as a primary key under the control of any other organization can lead to trouble.
4. Strings as primary keys are less "efficient" than numeric keys. I am basing that solely on the fact that a string or character representation needs to be unboxed when used in code and am not talking database implementation. It is also a string since some country codes are prefixed with a zero

Answer 2

Opposite view here.

Summary: I will be using numeric country code (ISO-3166-1) as my smallint primary key. If I ever need to detach from ISO, I will turn it to auto-increment and add iso_numeric field for reference.

Full Reply: I am facing the same question and I will go with using ISO-3166-1 as my primary keys, though your thoughs made me rethink this again - thank you.

Why I'm going opposite? I will refer point by point to the previous answer.

1. Country numbers have been withdrawn, because some countries ceased to exist. That's completely ok. You still have those numbers under control and can use them in your DB. No matter what id system you use, you will have to handle updating the state of your information anyways. Having your proivate primary key does not make the task any easier and IMHO does not put you at any risk (unless you join-delete stuff, which you shouldn't do for countries).
2. This is bad "security by obscurity" practice to rely on someone not guessing your key. False sense of security is often worse than real threat. There is a value in obfuscating the ids, but to me it's more in making deductions harder, e.g. regarding how many users you really have. As to countries, http://www.send.parcel.to.fake.example.com/?countryID=666 won't allow your customers to get a package in "Saint Pierre and Miquelon" with the price of your next-street neighbour if you made any reasonable validation.
3. Generally agreed. Note that this external organization won't sneak onto your servers to mess up your ids. You're in control of them. You can easily detach from following their convention at any point. You should assume they will change their things. You should be prepared for this to come.
4. Agreed. Yet with currency I'll be using iso3 alpha instead of artificial numeric, as the efficiency gains in my case will never make up the time wasted making joins and lookups nor human time handling added complexity.