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

在设计表时，我养成了一个习惯，即有一个唯一的列，并将其作为主键。这可以通过三种方式实现，具体取决于需求:

  1. 自动递增的标识整数列。
  2. 唯一标识符(GUID)
  3. 短字符(x)或整数(或其他相对较小的数字类型)列，可作为行标识符列

数字3将用于相当小的查找，主要是读取表，这些表可能有一个唯一的静态长度字符串代码，或一个数值，如年或其他数字。

在大多数情况下，所有其他表都有一个自动递增的整数或唯一标识符主键。

问题:-)

我最近开始使用一些数据库，这些数据库没有一致的行标识符，而且主键目前聚集在各个列之间。一些例子:

  • datetime /字符
  • datetime /整数
  • datetime / varchar
  • 字符/ nvarchar / nvarchar

这有有效的理由吗?我总是为这些情况定义一个标识符或唯一标识符列。

此外，还有许多根本没有主键的表。如果有的话，合理的理由是什么?

我试图理解为什么桌子被设计成这样，对我来说，它似乎是一个很大的混乱，但也许有很好的理由。

第三个问题在某种程度上帮助我解析答案:在使用多个列组成复合主键的情况下，与代理/人工键相比，这种方法是否有特定的优势?我主要考虑的是性能、维护、管理等方面。

185262 次浏览
小开

我怀疑原始数据结构的设计者需要Steven A. Lowe的卷起报纸疗法。

顺便说一句，guid作为主键可能会影响性能。我不推荐。

小开

表应该一直有一个主键。如果没有，它应该是一个自动递增字段。

有时人们会省略主键，因为他们要传输大量数据，这可能会减慢(取决于数据库)进程。但是，它应该加在它之后。

一些关于链接表的评论，这是正确的，这是一个例外，但是字段应该是FK以保持完整性，并且在某些情况下，如果链接中的重复未被授权，这些字段也可以是主键…但是要保持简单的形式，因为异常在编程中经常出现，所以应该提供主键来保持数据的完整性。

小开

如果有天然钥匙，通常是最好的。因此，如果datetime/char 独特的标识了行，并且这两部分对该行都有意义，那就很好。

如果只有datetime是有意义的，并且只是附加了char以使其唯一，那么您不妨使用一个identify字段。

小开

您应该使用由多个字段组成的“复合”或“复合”主键。

这是一个完全可以接受的解决方案，更多信息请访问在这里:)

小开

我也总是使用数字ID列。在oracle中，我使用数字(18,0)没有真正的原因高于数字(12,0)(或任何int而不是long)，也许我只是不想担心在db中获得数十亿行!

我还包括了一个用于基本跟踪的已创建和修改的列(类型时间戳)，在这里它似乎很有用。

我不介意在其他列的组合上设置唯一的约束，但我非常喜欢我的id、创建和修改的基线需求。

小开

我们做了很多连接，复合主键已经成为性能的累赘。简单的int或long即使引入第二个候选键也可以解决许多问题，但是在一个字段上连接比在三个字段上连接要容易得多，也更容易理解。

小开

我总是使用自动编号或标识字段。

我曾经为一个客户工作，他使用SSN作为主键，然后由于HIPAA法规被迫更改为“MemberID”，这在更新相关表中的外键时引起了大量问题。坚持一致的标识列标准帮助我在所有项目中避免了类似的问题。

小开

从不同的字段中创建主键没有问题，这是自然的关键。

可以使用Identity列(与候选字段上的唯一索引相关联)来创建代理键。

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

但是没有钥匙是没有理由的。

再保险:编辑

是的，关于这一点有很多争议:D

我没有看到任何明显的优势自然键，除了他们是自然的选择。你总是会用名字,SocialNumber——或者类似的东西——而不是idPerson来思考。

代理键可以解决自然键存在的一些问题(例如传播更改)。

当你习惯了代理，它似乎更干净，更易于管理。

但最终，你会发现这只是一个品味或心态的问题。有些人用自然的钥匙能“更好地思考”，而其他人则不然。

小开

自然键和人工键是数据库社区中的一种宗教争论——参见这篇文章和它链接到的其他内容。我既不赞成总是拥有人工密钥，也不赞成从来没有拥有它们。我会根据具体情况做出决定，例如:

  • 美国各州:我会使用state_code(德克萨斯州的'TX'等)，而不是德克萨斯州的state_id=1
  • 员工:我通常会创建一个人工的employee_id，因为很难找到其他任何工作。SSN或同等的工作，但可能会有问题，如新加入谁还没有提供他/她的SSN。
  • 员工薪资历史:(employee_id, start_date)。我将不创建一个人工employee_salary_history_id。它将服务于哪个点(除了“愚蠢的consistency")

无论在哪里使用人工键，都应该始终在自然键上声明唯一的约束。例如，如果你必须使用state_id，但是你最好在state_code上声明一个唯一的约束，否则你最终肯定会得到:

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

所有表应该都有一个主键。否则，您所拥有的就是一个HEAP——在某些情况下，这可能就是您想要的(当数据通过服务代理复制到另一个数据库或表时，会产生大量插入负载)。

对于行数较少的查找表，可以使用3 CHAR代码作为主键，因为这比INT占用的空间更少，但性能差异可以忽略不计。除此之外，我总是使用INT，除非您有一个引用表，它可能有一个由相关表的外键组成的复合主键。

小开

如果你真的想阅读关于这个古老争论的所有内容，可以在Stack Overflow上搜索“自然键”。你应该能拿到几页结果。

小开
最佳答案

我遵循一些规则:

  1. 主键应该尽可能小。建议使用数字类型，因为数字类型的存储格式比字符格式紧凑得多。这是因为大多数主键将是另一个表中的外键，并用于多个索引中。键越小，索引就越小，使用的缓存页就越少。
  2. 主键不应该改变。更新主键应该始终是不可能的。这是因为它最有可能在多个索引中使用，并用作外键。更新单个主键可能会导致变化的连锁反应。
  3. 不要使用“your problem”主键;作为逻辑模型的主键。例如护照号，社会保险号，或雇员合同号，这些“自然密钥”;可以在现实世界中改变。确保在必要时为这些添加UNIQUE约束，以加强一致性。

关于代理键和自然键，我参考了上面的规则。如果自然键很小并且永远不会改变，则可以将其用作主键。如果自然键很大或可能改变，我使用代理键。如果没有主键，我仍然会创建一个代理键，因为经验表明，您总是会向模式添加表，并希望在适当的位置放置一个主键。

小开

对我来说，自然键和人工键的区别在于数据库中需要多少业务逻辑。社会保险号码 (SSN)就是一个很好的例子。

“我的数据库中的每个客户都将而且必须有SSN。”搞定，把它设为主键，搞定。只要记住，当你的业务规则改变时，你就完蛋了。

我自己不喜欢自然键，因为我有改变业务规则的经验。但是如果您确定它不会改变，那么它可能会阻止一些关键的连接。

小开

我寻找自然主键，并尽可能地使用它们。

如果找不到自然的键，我更喜欢GUID而不是INT++，因为SQL Server使用树，总是在树的末尾添加键是不好的。

在多对多耦合的表上，我使用外键的复合主键。

因为我很幸运使用SQL Server，我可以用分析器和查询分析器研究执行计划和统计数据，并很容易地发现我的键是如何执行的。

小开

guid可以用作主键，但是你需要创建正确类型的GUID，这样它才能正常运行。

你需要生成COMB guid。一篇关于它和性能统计的好文章是 guid作为主键的代价 < / em >。

另外，在SQL中构建COMB guid的一些代码也在唯一标识符vs身份(archive)中。

小开

这只是对一些经常被忽视的东西的额外评论。有时不使用单个代理键作为主键对子表有好处。假设我们有一种设计，允许您在一个数据库中运行多个公司(可能是一个托管解决方案，或者其他什么)。

假设我们有这些表和列:

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表上使用一个代理键作为主键，并且在发票表中没有外键关系，则会是。

搞砸的机会越少越好。

小开

我将坦率地说明我对自然键的偏好——在可能的情况下使用它们，因为它们将使您的数据库管理工作更加容易。我在公司建立了一个标准，所有的表格都有以下列:

  • 行ID (GUID)
  • 创造者(字符串;默认为当前用户名(T-SQL中的SUSER_SNAME()))
  • 创建(DateTime)
  • 时间戳

行ID在每个表上都有一个唯一的键，并且在任何情况下都是每行自动生成的(并且权限阻止任何人编辑它)，并且合理地保证在所有表和数据库中是唯一的。如果任何ORM系统都需要一个ID密钥，那么可以使用这个密钥。

同时，如果可能的话，实际的PK是一个自然的关键。我的内部规则是这样的:

  • 人-使用代理键，例如INT。如果它是内部的，那么Active Directory用户GUID是一个可以接受的选择
  • 查找表(例如StatusCodes) -使用短的CHAR代码;它比int更容易记住，在许多情况下，纸张表格和用户也会使用它来简洁(例如，Status = "E"表示"过期"，"A"表示"已批准"，"NADIS"表示"样品中未检测到石棉")。
  • 链接表- fk的组合(例如EventId, AttendeeId)

因此，理想情况下，您最终会得到一个自然的、人类可读的和难忘的PK，以及一个orm友好的每个表一个id的GUID。

警告:我维护的数据库倾向于100,000条记录，而不是数百万或数十亿条记录，所以如果您有使用大型系统的经验，这不利于我的建议，请随意忽略我!

小开

我避免使用自然键的原因很简单——人为错误。虽然通常可以使用自然的唯一标识符(SSN、VIN、Account Number等)，但它们需要人工正确输入。如果您使用ssn作为主键，有人在数据输入期间调换了几个数字，并且没有立即发现错误，那么您将面临更改主键的问题。

我的主键都是由数据库程序在后台处理的，用户永远不会知道它们。

小开

主键有什么特别之处?

模式中表的用途是什么?表中键的作用是什么?主键有什么特别之处?围绕主键的讨论似乎忽略了一点，即主键是表的一部分，而表是模式的一部分。对表和表关系最有利的应该驱动所使用的键。

表(以及表关系)包含关于您希望记录的信息的事实。这些事实应该是独立的、有意义的、容易理解的、不矛盾的。从设计的角度来看，从模式中添加或删除的其他表不应该对相关表产生影响。存储只与信息本身相关的数据必须有一个目的。了解表中存储的内容不应该需要进行科学研究项目。为相同目的存储的事实不应存储多次。键是被记录信息的全部或部分，是唯一的，主键是专门指定的键，是表的主访问点(也就是说，应该根据数据一致性和使用情况选择主键，而不仅仅是插入性能)。

    旁白:大多数数据库正在设计的不幸的副作用 由应用程序程序员(有时我就是)开发的 这通常对应用程序或应用程序框架来说是最好的 驱动表的主键选择。这就导致了整数和 GUID键(因为这些在应用程序框架中很容易使用)和 单片表设计(因为这些减少了应用程序的数量 表示内存中的数据所需的框架对象)。这些 应用程序驱动的数据库设计决策会导致大量数据 大规模使用时的一致性问题。应用程序框架 这样的设计方式自然会导致表一次设计。 “部分记录”是在随时间填写的表和数据中创建的。 避免多表交互或使用时导致不一致 应用程序运行异常时的数据。这些设计引领 对那些毫无意义(或难以理解)的数据，数据传播开来 在表之上(您必须查看其他表才能理解 . .

据说主键应该尽可能小。我想说的是，键应该尽量大。应该避免向表中随机添加无意义的字段。从随机添加的无意义字段中生成键更糟糕，特别是当它破坏了从另一个表到非主键的连接依赖关系时。只有在表中没有好的候选键时才合理，但如果将这种情况用于所有表，则肯定是糟糕的模式设计的标志。

也有人说主键永远不应该改变，因为更新主键总是不可能的。但是更新和删除后插入是一样的。按照这种逻辑，永远不要在使用一个键的表中删除一条记录，然后再使用第二个键添加另一条记录。添加代理主键并不会删除表中其他键存在的事实。更新一个表的非主键可能会破坏数据的含义，如果其他表通过代理键依赖于这个含义(例如，一个状态表的代理键的状态描述从' Processed '改变为' Cancelled '肯定会破坏数据)。破坏数据的意义应该永远是不可能的。

说到这里，我很感激现在企业中存在的许多设计糟糕的数据库(毫无意义的代理键数据损坏1nf庞然大物)，因为这意味着理解正确数据库设计的人有无尽的工作要做。但在悲伤的一面，它有时确实让我觉得自己像西西弗斯，但我打赌他有一个该死的401k(在崩溃之前)。对于重要的数据库设计问题，请远离博客和网站。如果您正在设计数据库，请查阅CJ Date。你也可以在SQL Server中引用Celko，但前提是你必须先捏住自己的鼻子。在神谕方面，可以参考汤姆·凯特。

小开

除了这些好答案，我想分享一篇我刚读过的好文章，伟大的主键辩论。

这里引用几点:

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

  • 主键必须唯一地标识每条记录。
  • 记录的主键值不能为空。
  • 在创建记录时，主键-值必须存在。
  • 主键必须保持稳定—您不能更改主键字段。
  • 主键必须紧凑且包含尽可能少的属性。
  • 主键值不能更改。

自然键(倾向于)打破规则。代理键符合规则。(你最好通读那篇文章，它值得你花时间!)

小开

以下是我拥有25年以上开发经验后得出的经验法则。

  • 所有的表都应该有一个单独的列主键auto 李增量。< / >
  • 将它包含在任何意味着可更新的视图中
  • 主键在应用程序上下文中不应该有任何意义。这意味着它不应该是SKU、帐号、员工id或对应用程序有意义的任何其他信息。它只是一个与实体相关联的唯一键。

主键由数据库用于优化目的，应用程序除了用于标识特定实体或与特定实体相关外，不应该使用主键。

始终使用单一值主键使得执行upsert非常简单。

  • 支持单列上的多个索引，而不是多列索引。
    例如，如果您有一个两列键，最好在每列上创建一个索引，而不是创建一个两列索引。如果我们在firstname + lastname上创建了一个多列键，我们不能在没有提供名字的情况下对lastname进行索引查找。在两个列上都有索引允许优化器对其中一列或两列执行索引查找，而不管它们在WHERE子句中是如何表示的

  • 如果您的表非常庞大，请根据最突出的搜索条件将表划分为多个段。

  • 如果您有一个表，其中有大量的Id字段，请考虑删除一个表的所有主键，除了一个有Id (PK)，一个org_id (FK到原始表)和一个id_type列的表。为新表上的所有列创建索引，并将其与原始表关联起来。通过这种方式，您现在可以仅使用一个索引对任意数量的id执行索引查找。


提交答案