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

问题描述

与我合作的团队决定创建一个带有 varchar 主键的表.这个表被这个主键上的另一个表引用.

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.

我假设 varchar 是某种自然键 - 例如实体名称、电子邮件地址或序列号.如果您使用自然键，那么有时会发生需要更改键的情况，例如:

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:

• 数据输入错误，需要更正.
• 用户更改了他们的姓名或电子邮件地址.
• 管理层突然决定必须将所有客户参考编号更改为另一种格式，原因在您看来完全不合逻辑，但即使您解释了这会给您带来的问题，他们仍坚持进行更改.
• 甚至可能一个国家或州决定更改其名称的拼写 - 不太可能，但并非不可能.

通过使用代理键，您可以避免因必须更改主键而导致的问题.

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

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