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

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

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

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

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

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

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

Favor multiple indices on single columns over multi-column indices. For example, if you have a two column key, favor creating an index on each column over creating a two column index. If we create a multi-column key on firstname + lastname, we can't do indexed lookups on lastname without providing a firstname as well. Having indices on both columns allows the optimizer to perform indexed lookups on either or both columns regardless of how they are expressed in your WHERE clause. If your tables are massive, explore partitioning the table into segments based on the most prominent search criteria. If you have a table that has a significant number of Id fields in it, consider removing all except the primary key to a single table which has an id (PK), an org_id (FK to original table) and an id_type column. Create indices for all columns on the new table and relate it to the original table. In this manner, you can now perform indexed lookups of any number of ids using only a single index.

我遵循一些规则:

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.

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

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

这是一个完全可以接受的解决方案，点击这里了解更多信息:)

主键有什么特别之处?

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

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.