Menu
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?
336
based on some research and tests i've come to the conclusion that CHAR (that production_x_country . country field) is no valid foreign key field type - though i did not find any hint to that assumption in the mysql docs. if i change the column type to some other character type like VARCHAR , the procedure works.
Although primary key values must be unique, foreign key values are not required to be unique. For example, a single customer at a bank might have multiple accounts.
No, the primary key does not have to be an integer; it's just very common that it is. As an example, we have User ID's here that can have leading zeroes and so must be stored in a varchar field. That field is used as a primary key in our Employee table.
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.
151
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
