为主键选择数据类型时应该考虑什么? [英] What should I consider when selecting a data type for my primary key?
问题描述
当我创建一个新的数据库表时，选择主键的数据类型时应该考虑哪些因素?
When I am creating a new database table, what factors should I take into account for selecting the primary key's data type?
推荐答案
很抱歉这样做，但我发现我给相关问题的答案(您可以查看 this 和 this) 可以适用于这个.我对它们进行了一些改造...
Sorry to do that, but I found that the answers I gave to related questions (you can check this and this) could apply to this one. I reshaped them a little bit...
您会发现很多帖子都涉及这个问题，您做出的每个选择都有其优点和缺点.这些参数通常参考关系数据库理论和数据库性能.
You will find many posts dealing with this issue, and each choice you'll make has its pros and cons. Arguments for these usually refer to relational database theory and database performance.
在这个问题上，我的观点很简单:代理主键总是有效，而自然键可能并不总是在这些日子里起作用，这有多种原因:字段太短、规则改变等.
On this subject, my point is very simple: surrogate primary keys ALWAYS work, while Natural keys MIGHT NOT ALWAYS work one of these days, and this for multiple reasons: field too short, rules change, etc.
到目前为止，你已经猜到我基本上是 uniqueIdentifier/surrogate 主键团队的成员，即使我欣赏和理解这里提出的论点，我仍然在寻找这样的情况自然"键比代理更好......
To this point, you've guessed here that I am basically a member of the uniqueIdentifier/surrogate primary key team, and even if I appreciate and understand arguments such as the ones presented here, I am still looking for the case where "natural" key is better than surrogate ...
除此之外，支持此基本规则的最重要但总是被遗忘的论点之一与代码规范化和生产力有关:
In addition to this, one of the most important but always forgotten arguments in favor of this basic rule is related to code normalization and productivity:
每次创建表，我会不会浪费时间
- 识别其主键及其物理特征(类型、大小)
- 每次我想在我的代码中引用这些特性时都记得这些特性吗?
- 向团队中的其他开发者解释我的 PK 选择?
我对所有这些问题的回答都是否定的
My answer is no to all of these questions:
- 当代理选项为我提供了万无一失的解决方案时，我没有时间尝试确定最佳自然主键".
- 我不想在编写代码时记住我的 Table_whatever 的主键是一个 10 个字符长的字符串.
- 我不想浪费时间来协商自然密钥长度:好吧，如果您需要 10 个，为什么不使用 12 个 为了安全起见?".这个在安全方面"的论点真的让我很恼火:如果你想保持安全，那说明你离不安全的地方真的不远了！选择代理:它是防弹的！
所以我在过去五年里一直遵循一个非常基本的规则:每个表(我们称之为myTable")都有它的第一个字段，称为 'id_MyTable'，它是 uniqueIdentifier 类型.即使这个表支持多对多"关系，其中字段组合提供了一个非常可接受的主键，我更喜欢创建这个 'id_myManyToManyTable' 字段作为 uniqueIdentifier，只是为了坚持规则，因为，最后，它没有伤害.
So I've been working for the last five years with a very basic rule: each table (let's call it 'myTable') has its first field called 'id_MyTable' which is of uniqueIdentifier type. Even if this table supports a "many-to-many" relation, where a field combination offers a very acceptable Primary Key, I prefer to create this 'id_myManyToManyTable' field being a uniqueIdentifier, just to stick to the rule, and because, finally, it does not hurt.
主要优点是您不必再关心代码中主键和/或外键的使用.获得表名后，您就知道 PK 名称和类型.一旦您知道数据模型中实现了哪些链接，您就会知道表中可用外键的名称.
The major advantage is that you don't have to care anymore about the use of Primary Key and/or Foreign Key within your code. Once you have the table name, you know the PK name and type. Once you know which links are implemented in your data model, you'll know the name of available foreign keys in the table.
如果您仍然希望将Natural Key"放在桌子的某处，我建议您按照标准模型构建它，例如
And if you still want to have your "Natural Key" somewhere in your table, I advise you to build it following a standard model such as
Tbl_whatever
id_whatever, unique identifier, primary key
code_whatever, whateverTypeYouWant(whateverLengthYouEstimateTheRightOne), indexed
.....
其中 id_ 是主键的前缀，code_ 用于自然"索引字段.有些人会争辩说 code_ 字段应该设置为唯一的.这是真的，并且可以通过 DDL 或外部代码轻松管理.注意很多自然"键是计算出来的(发票号)，所以已经通过代码生成了
Where id_ is the prefix for primary key, and code_ is used for "natural" indexed field. Some would argue that the code_ field should be set as unique. This is true, and it can be easily managed either through DDL or external code. Note that many "natural" keys are calculated (invoice numbers), so they are already generated through code
我不确定我的规则是最好的.但这是一种非常有效的方法！如果每个人都在应用它，我们就可以避免浪费时间回答这类问题！
I am not sure that my rule is the best one. But it is a very efficient one! If everyone was applying it, we would for example avoid time lost answering to this kind of question!
这篇关于为主键选择数据类型时应该考虑什么?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
