表中主键的最佳实践是什么?
在设计表格时，我养成了让一列独一无二并作为主键的习惯.这取决于需求，可以通过三种方式实现:
When designing tables, I've developed a habit of having one column that is unique and that I make the primary key. This is achieved in three ways depending on requirements:
- 自动递增的标识整数列.
- 唯一标识符 (GUID)
- 可用作行标识符列的短字符 (x) 或整数(或其他相对较小的数字类型)列
数字 3 将用于相当小的查找，主要是读取可能具有唯一静态长度字符串代码或数字值(例如年份或其他数字)的表.
Number 3 would be used for fairly small lookup, mostly read tables that might have a unique static length string code, or a numeric value such as a year or other number.
在大多数情况下，所有其他表将具有自动递增的整数或唯一标识符主键.
For the most part, all other tables will either have an auto-incrementing integer or unique identifier primary key.
我最近开始使用没有一致行标识符且主键当前聚集在各个列中的数据库.一些例子:
I have recently started working with databases that have no consistent row identifier and primary keys are currently clustered across various columns. Some examples:
- 日期时间/字符
- 日期时间/整数
- 日期时间/varchar
- char/nvarchar/nvarchar
对此有有效的案例吗?对于这些情况，我总是会定义一个身份或唯一标识符列.
Is there a valid case for this? I would have always defined an identity or unique identifier column for these cases.
另外还有很多表根本没有主键.这样做的正当理由是什么(如果有的话)?
In addition there are many tables without primary keys at all. What are the valid reasons, if any, for this?
我试图理解为什么表格设计成这样，这对我来说似乎是一团糟，但也许有很好的理由.
I'm trying to understand why tables were designed as they were, and it appears to be a big mess to me, but maybe there were good reasons for it.
第三个问题可以帮助我解读答案:在使用多列组成复合主键的情况下，这种方法与代理/人工键相比是否有特定优势?我主要考虑性能、维护、管理等方面的问题?
A third question to sort of help me decipher the answers: In cases where multiple columns are used to comprise the compound primary key, is there a specific advantage to this method vs. a surrogate/artificial key? I'm thinking mostly in regards to performance, maintenance, administration, etc.?
推荐答案
我遵循一些规则:
- 主键应尽可能小.首选数字类型，因为数字类型以比字符格式更紧凑的格式存储.这是因为大多数主键将是另一个表中的外键以及在多个索引中使用.您的键越小，索引越小，您将使用的缓存中的页面就越少.
- 主键永远不应该改变.更新主键应该永远是不可能的.这是因为它最有可能在多个索引中使用并用作外键.更新单个主键可能会导致更改的连锁反应.
- 请勿使用您的问题主键"；作为您的逻辑模型主键.例如，作为这些自然密钥"的护照号码、社会安全号码或雇员合同号码将被使用.可以在现实世界的情况下改变.确保在必要时为这些添加 UNIQUE 约束以确保一致性.
关于代理与自然键，我参考了上面的规则.如果自然键很小并且永远不会改变，则可以将其用作主键.如果自然键很大或可能会改变，我会使用代理键.如果没有主键，我仍然会创建一个代理键，因为经验表明您总是会向架构中添加表，并希望将主键放置到位.
On surrogate vs natural key, I refer to the rules above. If the natural key is small and will never change it can be used as a primary key. If the natural key is large or likely to change I use surrogate keys. If there is no primary key I still make a surrogate key because experience shows you will always add tables to your schema and wish you'd put a primary key in place.
相关文章