Is it necessary to use an integer ID for the Country table when the name is already univocal?

Question

Since the country name is univocal, can be a good choice to use it as a primary key?

CREATE TABLE IF NOT EXISTS CookingDB.Country ( CountryName VARCHAR(50) NOT NULL, PRIMARY KEY (CountryName) )

The examples that I found online use an integer ID as the primary key. Is it necessary?

Answer 1

It's not necessary to have an integer ID, although it is common. Having a country name as key will result in a large key, with (sometimes) spaces or special characters in it. This means that using them is slower, and if you use them in, say, an HTML/JavaScript application, you may need to escape them.

Also, you may want to localize these once, or change a decision whether country names should be in their own language, or have their English name, or a phonetic notation in Latin script.. Or the name itself may change (although that probably won't happen much for countries), or you may have a typo which you want to correct, preferably without having to update all tables that refer to Country.

So having a surrogate key is not a bad idea at all. If you really want to have a textual key for country, I'd choose a country ISO code over the full name.

Related question: When not to use surrogate keys

Answer 2

It is not wrong! However, we use to have a columnn with integer numbers or the iso of each country related to each country record. That helps in many ways but it depends on what you want to create. I would suggest to use another column as id which will be also the primary key. In that way, you will be more accurate with your queries and you would not waste time debugging or trying to find an error which it can easily be a typographic error. So, it's up to you, you are the creator!