SQL主键：整数与varchar [英] SQL primary key: integer vs varchar

问题描述

我正在与之合作的团队决定使用varchar主键创建一个表。此表由此主键上的另一个表引用。

我有习惯创建一个整数主键，遵循我在大学学到的东西。我已经读过使用整数主键提升性能。

问题是我不知道创建整数主键的任何其他原因。您有任何提示吗？

解决方案

主键应该代表行的标识，不应随时间变化。

我假设varchar是某种自然键 - 例如实体名称，电子邮件地址或序列号。如果您使用自然键，则有时可能会发生密钥需要更改，例如：

• 数据输入错误且需要


• 用户更改其姓名或电子邮件地址。


• 管理层突然决定所有客户参考号必须更改为其他格式出于对你来说似乎完全不合逻辑的原因，但即使你解释了它会给你带来的问题，他们也坚持要做出改变。


• 甚至一个国家或州也可能决定改变拼写它的名字 - 非常不可能，但并非不可能。

通过使用代理键，您可以避免因更换主键而导致的问题。 / p>

The team I'm working with decided to create a table with a varchar primary key. This table is referenced by another table on this primary key.

I've the habit to create an integer primary key, following what I learnt at university. I've read that there is a performance boost using integer primary key.

The matter is that I don't know any other reason for creating an integer primary key. Do you have any tips?

解决方案

The primary key is supposed to represent the identity for the row and should not change over time.

I assume that the varchar is some sort of natural key - such as the name of the entity, an email address, or a serial number. If you use a natural key then it can sometimes happen that the key needs to change because for example:

• The data was incorrectly entered and needs to be fixed.
• The user changes their name or email address.
• The management suddenly decide that all customer reference numbers must be changed to another format for reasons that seem completely illogical to you, but they insist on making the change even after you explain the problems it will cause you.
• Maybe even a country or state decides to change the spelling of its name - very unlikely, but not impossible.

By using a surrogate key you avoid problems caused by having to change primary keys.

这篇关于SQL主键：整数与varchar的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！