sqlmysqldatabase-designdata-modelinginnodb

Is using char as a primary/foreign key a no no?


Consider that there is a bunch of tables which link to "countries" or "currencies" tables.

For making data easier to read I'd like make CHAR field with country code (eg US, GB, AU) and currency code (USD, AUD) a primary keys in each of those 2 tables and all other tables will use this CHAR as a foregin key.

Database is mysql with innodb engine.

Is it going to cause performance issues? Is it something i should avoid?


Solution

  • Performance isn't really the main issue, at least not for me. The issue is more about surrogate vs natural keys.

    Country codes aren't static. They can and do change. Countries change names (eg Ethiopia to Eritrea). They come into being (eg the breakup of Yugoslavia or the Soviet Union) and they cease to exist (eg West and East Germany). When this happens the ISO standard code changes.

    More in Name Changes Since 1990: Countries, Cities, and More

    Surrogate keys tend to be better because when these events happen the keys don't change, only columns in the reference table do.

    For that reason I'd be more inclined to create country and currency tables with an int primary key instead.

    That being said, varchar key fields will use more space and have certain performance disadvantages that probably won't be an issue unless you're performing a huge number of queries.

    For completeness, you may want to refer to Database Development Mistakes Made by AppDevelopers.