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

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

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

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

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

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

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

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

再保险:编辑

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

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

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

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

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

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

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

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

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

主键有什么特别之处?

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

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.

我遵循一些规则:

Primary keys should be as small as necessary. Prefer a numeric type because numeric types are stored in a much more compact format than character formats. This is because most primary keys will be foreign keys in another table as well as used in multiple indexes. The smaller your key, the smaller the index, the less pages in the cache you will use. Primary keys should never change. Updating a primary key should always be out of the question. This is because it is most likely to be used in multiple indexes and used as a foreign key. Updating a single primary key could cause of ripple effect of changes. Do NOT use "your problem primary key" as your logic model primary key. For example passport number, social security number, or employee contract number as these "natural keys" can change in real world situations. Make sure to add UNIQUE constraints for these where necessary to enforce consistency.

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