有没有任何好的理由让数据库表没有整数主键？ [英] Are there any good reasons to have a database table without an integer primary key?

问题描述

虽然我犯了这个罪行，但在我看来，没有任何好的理由表不具有身份字段主键。

Although I'm guilty of this crime, it seems to me there can't be any good reason for a table to not have an identity field primary key.

优点：$ b​​ $ b - 无论您是否愿意，您现在可以唯一标识表格中以前无法执行的每一行
- 您不能在表上没有主键的情况下进行sql复制

Pros: - whether you want to or not, you can now uniquely identify every row in your table which previously you could not do - you can't do sql replication without a primary key on your table

缺点：$ b​​ $ b - 表格的每一行额外32位

Cons: - an extra 32 bits for each row of your table

例如，考虑您需要在数据库中的表中存储用户设置的情况。您有一个设置名称列和一个设置值列。没有主键是必要的，但是有一个整数标识列并将其用作主键似乎是任何表创建的最佳做法。

Consider for example the case where you need to store user settings in a table in your database. You have a column for the setting name and a column for the setting value. No primary key is necessary, but having an integer identity column and using it as your primary key seems like a best practice for any table you ever create.

是否有其他原因除了大小，每个表不应该只有一个整数标识字段？

Are there other reasons besides size that every table shouldn't just have an integer identity field?

推荐答案

当然，如果您有一个国家/地区表格，则可能更适合使用 ISO 3166-1-alpha-2国家代码作为主键，因为这是一个国际标准，并使查询更易读（例如 CountryCode ='GB'，而不是 CountryCode = 28 ）。类似的参数可应用于 ISO 4217货币代码。

Sure, an example in a single-database solution is if you have a table of countries, it probably makes more sense to use the ISO 3166-1-alpha-2 country code as the primary key as this is an international standard, and makes queries much more readable (e.g. CountryCode = 'GB' as opposed to CountryCode = 28). A similar argument could be applied to ISO 4217 currency codes.

在使用复制的SQL Server数据库解决方案中， UNIQUEIDENTIFIER 键会更有意义GUID是某些类型的复制所必需的（并且如果有多个源数据库，也更容易避免关键冲突！）。

In a SQL Server database solution using replication, a UNIQUEIDENTIFIER key would make more sense as GUIDs are required for some types of replication (and also make it much easier to avoid key conflicts if there are multiple source databases!).

这篇关于有没有任何好的理由让数据库表没有整数主键？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！