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

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

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

自然键和人工键是数据库社区中的一种宗教争论——请参阅本文及其链接的其他文章。我既不赞成一直使用人工钥匙，也不赞成永远不使用。我会根据具体情况做出决定，例如:

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

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

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

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

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

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

主键有什么特别之处?

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

Tables (and table relationships) contain facts about information you wish to record. These facts should be self-contained, meaningful, easily understood, and non-contradictory. From a design perspective, other tables added or removed from a schema should not impact on the table in question. There must be a purpose for storing the data related only to the information itself. Understanding what is stored in a table should not require undergoing a scientific research project. No fact stored for the same purpose should be stored more than once. Keys are a whole or part of the information being recorded which is unique, and the primary key is the specially designated key that is to be the primary access point to the table (i.e. it should be chosen for data consistency and usage, not just insert performance).

ASIDE: The unfortunately side effect of most databases being designed and developed by application programmers (which I am sometimes) is that what is best for the application or application framework often drives the primary key choice for tables. This leads to integer and GUID keys (as these are simple to use for application frameworks) and monolithic table designs (as these reduce the number of application framework objects needed to represent the data in memory). These application driven database design decisions lead to significant data consistency problems when used at scale. Application frameworks designed in this manner naturally lead to table at a time designs. “Partial records” are created in tables and data filled in over time. Multi-table interaction is avoided or when used causes inconsistent data when the application functions improperly. These designs lead to data that is meaningless (or difficult to understand), data spread over tables (you have to look at other tables to make sense of the current table), and duplicated data.

It was said that primary keys should be as small as necessary. I would says that keys should be only as large as necessary. Randomly adding meaningless fields to a table should be avoided. It is even worse to make a key out of a randomly added meaningless field, especially when it destroys the join dependency from another table to the non-primary key. This is only reasonable if there are no good candidate keys in the table, but this occurrence is surely a sign of a poor schema design if used for all tables.

It was also said that primary keys should never change as updating a primary key should always be out of the question. But update is the same as delete followed by insert. By this logic, you should never delete a record from a table with one key and then add another record with a second key. Adding the surrogate primary key does not remove the fact that the other key in the table exists. Updating a non-primary key of a table can destroy the meaning of the data if other tables have a dependency on that meaning through a surrogate key (e.g. a status table with a surrogate key having the status description changed from ‘Processed’ to ‘Cancelled’ would definitely corrupt the data). What should always be out of the question is destroying data meaning.

Having said this, I am grateful for the many poorly designed databases that exist in businesses today (meaningless-surrogate-keyed-data-corrupted-1NF behemoths), because that means there is an endless amount of work for people that understand proper database design. But on the sad side, it does sometimes make me feel like Sisyphus, but I bet he had one heck of a 401k (before the crash). Stay away from blogs and websites for important database design questions. If you are designing databases, look up CJ Date. You can also reference Celko for SQL Server, but only if you hold your nose first. On the Oracle side, reference Tom Kyte.

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

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