使用char作为主键/外键是否否？ [英] Is using char as a primary/foreign key a no no?

问题描述

考虑到有一堆表链接到国家或货币表。

为了使数据更容易阅读我想要CHAR每个这两个表格中的主键，所有其他表格将使用这个CHAR作为一个原始密钥。

（包括美国，英国，澳大利亚）和货币代码（USD，AUD）

数据库是具有innodb引擎的mysql。

是否会导致性能问题？这是我应该避免的事情吗？

解决方案

性能不是真正的主要问题，至少不适合我。问题更多的是代理与自然键。

国家代码不是静态的。他们可以而且做改变。国家改名（例如埃塞俄比亚到厄立特里亚）。它们形成（例如南斯拉夫或苏联解体），它们不再存在（例如西德和东德）。当这种情况发生时，ISO标准代码更改。

更多自1990年以来的名称变更：国家，城市，和更多

代理键往往更好，因为当这些事件发生时，键不会更改，只有参考表中的列才能执行。 p>

因此，我更倾向于使用int主键创建国家和货币表。

话虽如此，varchar关键字段将使用更多的空间，并具有一定的性能缺点，除非您执行大量的查询，否则可能不会是一个问题。

为了完整起见，您可能需要参考由AppDevelopers制作的数据库开发错误。

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?

解决方案

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.

这篇关于使用char作为主键/外键是否否？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！