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

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

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

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

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

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

此外，在SQL中构建COMB guid的一些代码是在Uniqueidentifier vs identity(存档)中。

主键有什么特别之处?

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

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.

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

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

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

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

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