表中主键的最佳实践是什么？

在设计表时,我养成了一个习惯,就是让一个列是唯一的,并且我制作了主键.这取决于要求以三种方式实现:

自动递增的标识整数列.

唯一标识符(GUID)

可用作行标识符列的短字符(x)或整数(或其他相对较小的数字类型)列

数字3将用于相当小的查找,主要是可能具有唯一静态长度字符串代码的读取表,或者诸如年份或其他数字的数字值.

在大多数情况下,所有其他表将具有自动递增整数或唯一标识符主键.

问题:-)

我最近开始使用没有一致行标识符的数据库,主键目前在各个列上进行聚类.一些例子:

日期时间/字符

日期时间/整数

日期时间/ VARCHAR

炭/ NVARCHAR/nvarchar的

这有一个有效的案例吗？我总是会为这些案例定义一个标识或唯一标识符列.

此外,还有许多表没有主键.这有什么正当理由？

我试图理解为什么桌子的设计原样,对我来说这似乎是一个很大的混乱,但也许有充分的理由.

第三个问题可以帮助我解释答案:在使用多列构成复合主键的情况下,这种方法与代理/人工密钥相比有特定的优势吗？我主要考虑的是性能,维护,管理等方面？

1> Logicalmind..：

---

我遵循一些规则:

主键应尽可能小.首选数字类型,因为数字类型以比字符格式更紧凑的格式存储.这是因为大多数主键将是另一个表中的外键以及多个索引中使用的外键.密钥越小,索引越小,您将使用的缓存中的页面越少.

主键永远不会改变.更新主键始终是不可能的.这是因为它最有可能在多个索引中使用并用作外键.更新单个主键可能会导致更改产生连锁反应.

请勿使用"您的问题主键"作为逻辑模型主键.例如护照号码,社会安全号码或员工合同号码,因为这些"主键"可以改变现实世界的情况.

在代理与自然键上,我参考上面的规则.如果自然键很小并且永远不会改变,则它可以用作主键.如果自然键很大或可能改变,我使用代理键.如果没有主键,我仍然会制作代理键,因为经验显示您将始终将表添加到架构中,并希望您将主键置于适当的位置.

---

只需记住在使用人工键时将该唯一索引放在自然键上(如果实际存在,通常不是这种情况).

---

不,只是体验.在处理"小"数据库时,这些东西并不重要.但是当你处理大型数据库时,所有重要的事情都很重要.想象一下,如果你有10亿行int或long pk与使用text或guid相比.有一个巨大的差异!

---

即使你"知道""自然键很小而且永远不会改变",请三思而后行."我们从不重复使用这些代码"是着名的最后一句话....关于唯一属于小型,永不改变的类别的是iso和其他标准(国家代码,iata机场代码).诸如"这个内部品牌的2个字母代表是什么"之类的事情......在假设"它"永远不会改变之前要三思而后行,这是一个远离数据库重建的财务决策.

---

我喜欢!你有"规则"基础的任何文件吗？谢谢!

---

@Lloyd Cotten:这是大数据引擎提供商所说的支持规则1的内容:http://skyfoundry.com/forum/topic/24.它说服我回到`Int`s

2> Tony Andrews..：

---

自然诗句人工键是数据库社区中的一种宗教辩论 - 请参阅本文及其链接的其他文章.我既不赞成总是拥有人工钥匙,也不赞成永远不拥有它们.我会根据具体情况做出决定,例如:

美国各州:我会选择state_code(德克萨斯等的'TX'),而不是德州的state_id = 1

员工:我通常会创建一个artifical employee_id,因为很难找到其他有用的东西.SSN或同等版本可能有效,但可能会出现一些问题,比如还没有提供他/她的SSN的新加入者.

员工薪资历史:(employee_id,start_date).我不会创建一个artifical employee_salary_history_id.它会起什么作用(除了"愚蠢的一致性")

无论何处使用人工密钥,您都应该始终在自然键上声明唯一约束.例如,如果必须,请使用state_id,但是最好在state_code上声明一个唯一约束,否则你肯定最终得到:

state_id    state_code   state_name
137         TX           Texas
...         ...          ...
249         TX           Texas

---

在SQL Server 2005/2008的某些情况下,自然(文本)键可能比int键更快.我有一个带有7-8个字符友好代码的应用程序,我们将其用作主键,并且比int代理更快(通常更方便).我们无论如何都需要代码,以便我们可以拥有一个人类可读/令人难忘的代码,我们可以安全地将其转移到不同的应用程序实例(聚合到更大站点的多个站点).

3> WW...：

---

只是对经常被忽视的事情做出额外评论.有时不使用代理键在子表中有好处.假设我们的设计允许您在一个数据库中运行多个公司(可能是托管解决方案,或者其他任何东西).

假设我们有这些表和列:

Company:
  CompanyId   (primary key)

CostCenter:
  CompanyId   (primary key, foreign key to Company)
  CostCentre  (primary key)

CostElement
  CompanyId   (primary key, foreign key to Company)
  CostElement (primary key)

Invoice:
  InvoiceId    (primary key)
  CompanyId    (primary key, in foreign key to CostCentre, in foreign key to CostElement)
  CostCentre   (in foreign key to CostCentre)
  CostElement  (in foreign key to CostElement)

如果最后一位没有意义,Invoice.CompanyId则是两个外键的一部分,一个是CostCentre表,另一个是CostElement表.主键是(InvoiceId,CompanyId).

在这个模型中,这是不可能的螺丝,并引用CostElement从一个公司和一个CostCentre从另一家公司.如果在CostElement和CostCentre表上使用了代理键,那么它就是.

搞砸的机会越少越好.

---

当使用代理键时,这是一个未被引用的缺点.如果表具有代理键,我仍然可以将它用于这些类型的约束.不幸的是,虽然约束需要索引,但是当(surrogate_key)本身是唯一的时,在(surrogate_key,other_column)上创建唯一索引是很奇怪的.此外,(other_column)在映射表中通常是完全冗余的,因为(surrogate_key)在外部表中是唯一的.代理人可以真正搞砸了.

4> 小智..：

---

我避免使用自然键有一个简单的原因 - 人为错误.尽管通常可以使用自然唯一标识符(SSN,VIN,帐号等),但它们需要人员正确输入它们.如果您使用SSN作为主键,有人会在数据输入过程中转换几个数字,并且不会立即发现错误,那么您将面临更改主键的问题.

我的主键全部由后台的数据库程序处理,用户从不知道它们.

5> DonOctavioDe..：

---

从各个领域制作主键没有问题,这是一个自然键.

您可以使用Identity列(与候选字段上的唯一索引关联)来生成Surrogate Key.

这是一个古老的讨论.在大多数情况下,我更喜欢代理键.

但没有任何借口可以缺少一把钥匙.

RE:编辑

是的,有很多争议:D

除了事实上它们是自然选择之外,我没有看到任何关于自然键的明显优势.你总是会想到Name,SocialNumber - 或类似的东西 - 而不是idPerson.

代理键是自然键具有的一些问题的答案(例如,传播变化).

当你习惯了代理时,它似乎更干净,更易于管理.

但最终,你会发现这只是一种品味 - 或心态 - .人们用自然键"思考得更好",而其他人则不然.

---

人们用自然键"思考得更好".机器和数据库,不.

---

是的,但是谁设计了它们？这才是重点.

6> Patrick Desj..：

---

表应始终具有主键.当它不是它应该是一个AutoIncrement字段.

有时人们会省略主键,因为它们会传输大量数据,并且可能会减慢(依赖于数据库)进程.但是,它应该在它之后添加.

有关链接表中的一些一条评论,这是对的,这是一个例外,但字段应该是FK保持完整,一些情况下,这些领域可能是主键太多,如果在链接复制未经授权...而是保持在一个简单形式,因为异常是编程中常见的东西,应该存在主键以保持数据的完整性.

7> RayLuo..：

---

除了所有这些好的答案之外,我只想分享一篇我刚读过的好文章,这是一场伟大的主要辩论.

只是引用几点:

在为每个表选择主键时,开发人员必须应用一些规则:

主键必须唯一标识每条记录.

记录的主键值不能为空.

创建记录时必须存在主键值.

主键必须保持稳定 - 您无法更改主键字段.

主键必须紧凑并包含尽可能少的属性.

主键值无法更改.

自然键(倾向于)违反规则.代理键符合规则.(你最好仔细阅读那篇文章,值得你花时间!)

8> 小智..：

---

主键有什么特别之处？

模式中表的用途是什么？桌子钥匙的目的是什么？主键有什么特别之处？围绕主键的讨论似乎忽略了主键是表的一部分,并且该表是模式的一部分.表和表关系的最佳选择应该是驱动所使用的密钥.

表(和表关系)包含有关您要记录的信息的事实.这些事实应该是自足的,有意义的,易于理解的和不矛盾的.从设计角度来看,在模式中添加或删除的其他表不应影响相关表.必须存储仅与信息本身相关的数据的目的.了解表中存储的内容不应要求进行科学研究项目.为同一目的存储的事实不应存储多次.密钥是记录的信息的全部或一部分,它是唯一的,主密钥是专门指定的密钥,它将成为表的主要访问点(即,应选择它用于数据一致性和使用,而不仅仅是插入性能).

ASIDE:不幸的是,应用程序员(我有时)设计和开发的大多数数据库的副作用是,对应用程序或应用程序框架最好的方法通常是驱动表的主键选择.这导致整数和GUID键(因为这些键很容易用于应用程序框架)和单片表设计(因为它们减少了表示内存中数据所需的应用程序框架对象的数量).这些应用程序驱动的数据库设计决策在大规模使用时会导致严重的数据一致性 以这种方式设计的应用程序框架自然会导致一次设计表."部分记录"在表格和数据中创建,随着时间的推移填写.避免多表交互,或者在应用程序运行不正常时使用时会导致数据不一致.这些设计导致数据无意义(或难以理解),数据扩展到表(您必须查看其他表以理解当前表)和重复数据.

据说主键应该尽可能小.我会说钥匙应该只有必要的大小.应避免随意向表中添加无意义的字段.从随机添加的无意义字段中创建密钥甚至更糟,特别是当它破坏从另一个表到非主键的连接依赖性时.如果表中没有好的候选键,这是合理的,但如果用于所有表,这种情况肯定是架构设计不佳的标志.

还有人说,主键永远不应该改变,因为更新主键始终是不可能的.但更新与删除后跟插入相同.通过这种逻辑,您不应该从具有一个键的表中删除记录,然后使用第二个键添加另一个记录.添加代理主键不会删除表中另一个键存在的事实.如果其他表通过代理键依赖于该含义,则更新表的非主键可能会破坏数据的含义(例如,具有代理键的状态表,其状态描述已从"已处理"更改为"已取消" '肯定会破坏数据).永远不可能的是破坏数据的含义.

话虽如此,我很感激今天企业中存在的许多设计不良的数据库(无意义的代理键控数据损坏的1NF庞然大物),因为这意味着对于理解正确的数据库设计的人来说,有无数的工作量. .但在悲伤的一面,它确实有时让我感觉像西西弗斯,但我打赌他有一个401k(崩溃之前).远离博客和网站,以获取重要的数据库设计问题.如果您正在设计数据库,请查看CJ Date.你也可以参考Celko for SQL Server,但前提是你先嗤之以鼻.在Oracle方面,参考Tom Kyte.

9> James Curran..：

---

如果可用,自然键通常是最好的.因此,如果datetime/char 唯一标识该行,并且这两个部分对该行有意义,那就太好了.

如果只是日期时间是有意义的,并且刚刚添加了char以使其唯一,那么您可能只需要使用标识字段.

---

通常最好？我没有任何科学依据,但我几乎肯定大多数人更喜欢自然的替代关键.在许多情况下,没有自然的关键.

---

如果你从自然界中取出钥匙,自然界就会改变,打破你的钥匙.如果您使用电话号码,您将从同一家庭获得两个用户.如果你使用姓氏,他们就结婚了.如果您使用SSN,隐私法将会更改并要求您将其删除.

---

对于数据库中的任何行,始终应该是自然键.这种"自然"键可能是在商业世界或技术系统中产生的,但它应该始终存在.

---

如果,在你的世界中,那已经被确定为识别表中一行的唯一方法,那么是的.当然,当设计师选择为PK创建GUID时,通常是因为他们没有完成查找REAL自然键的工作,所以在这种情况下GUID不是自然键.

10> Dan Williams..：

---

对我而言,自然键和人工键是您在数据库中需要多少业务逻辑的问题.社会安全号码(SSN)就是一个很好的例子.

"我的数据库中的每个客户端都必须拥有SSN." Bam,完成后,将其作为主键并完成它.请记住,当您的业务规则发生变化时,您就会被烧毁.

由于我改变业务规则的经验,我自己不喜欢自然键.但如果你确定它不会改变,它可能会阻止一些关键连接.

---

我已经看到SSN不是唯一的数据,即使它应该是.如果您从其他来源导入数据,请非常警惕自然键!

---

SSN有时也是错误的,必须予以纠正.