MySQL 5 中的多列主键
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2642778/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Multi-Column Primary Key in MySQL 5
提问by Kaji
I'm trying to learn how to use keys and to break the habit of necessarily having SERIALtype IDs for all rows in all my tables. At the same time, I'm also doing many-to-many relationships, and so requiring unique values on either column of the tables that coordinate the relationships would hamper that.
我正在尝试学习如何使用键并打破SERIAL所有表中所有行都必须具有类型 ID的习惯。同时，我也在处理多对多关系，因此在协调关系的表的任一列上要求唯一值会妨碍这一点。
How can I define a primary key on a table such that any given value can be repeated in any column, so long as the combination of values across all columns is never repeated exactly?
如何在表上定义主键，以便任何给定值都可以在任何列中重复，只要所有列中的值组合永远不会完全重复？
回答by Adriaan Stander
Quoted from the CREATE TABLE Syntaxpage:
引用自CREATE TABLE Syntax页面：
A PRIMARY KEY can be a multiple-column index. However, you cannot create a multiple-column index using the PRIMARY KEY key attribute in a column specification. Doing so only marks that single column as primary. You must use a separate PRIMARY KEY(index_col_name, ...) clause.
PRIMARY KEY 可以是多列索引。但是，您不能使用列规范中的 PRIMARY KEY 键属性创建多列索引。这样做只会将该单列标记为主要列。您必须使用单独的 PRIMARY KEY(index_col_name, ...) 子句。
Something like this can be used for multi-column primary keys:
这样的东西可以用于多列主键：
CREATE TABLE
product (
category INT NOT NULL,
id INT NOT NULL,
price DECIMAL,
PRIMARY KEY(category, id)
);
回答by newtover
Primary key is a domain concept that uniquely (necessarily and sufficiently) identifies your entity among similar entities. A composite (multiple-column) primary key makes sense only when a part of the key refers to a particular instance of another domain entity.
主键是一个域概念，它在类似实体中唯一地（必要且充分地）标识您的实体。只有当键的一部分引用另一个域实体的特定实例时，复合（多列）主键才有意义。
Let's say you have a personal collection of books. As long as you are alone, you can count them and assign each a number. The number is the primary key of the domain of your library.
假设您有个人收藏的书籍。只要你一个人，你就可以计算它们并为每个分配一个数字。该数字是您图书馆域的主键。
Now you want to merge your library with that of your neighbor but still be able to distinguish to whom a book belongs. The domain is now broader and the primary key is now (owner, book_id).
现在您想将您的图书馆与邻居的图书馆合并，但仍然能够区分一本书属于谁。域现在更广泛，主键现在是 (owner, book_id)。
Thus, making each primary key composite should not be your strategy, but rather you should use it when required.
因此，使每个主键组合不应该是您的策略，而应该在需要时使用它。
Now some facts about MySQL. If you define a composite primary key and want the RDBSM to autoincrement the ids for you, you should know about the difference between MyISAM and InnoDB behavior.
现在有一些关于 MySQL 的事实。如果您定义了一个复合主键并希望 RDBSM 为您自动增加 id，您应该了解 MyISAM 和 InnoDB 行为之间的区别。
Let's say we want a table with two fields: parent_id, child_id. And we want the child_id to be autoincremented.
假设我们想要一个包含两个字段的表：parent_id、child_id。我们希望 child_id 自动递增。
MyISAM will autoincrement uniquely within records with the same parent_id and InnoDB will autoincrement uniquely within the whole table.
MyISAM 将在具有相同 parent_id 的记录中唯一地自动递增，而 InnoDB 将在整个表中唯一地自动递增。
In MyISAM you should define the primary key as (parent_id, child_id), and in InnoDB as (child_id, parent_id), because the autoincremented field should be the leftmost constituent in the primary key in InnoDB.
在 MyISAM 中，您应该将主键定义为 (parent_id, child_id)，在 InnoDB 中定义为 (child_id, parent_id)，因为自增字段应该是 InnoDB 中主键中最左侧的组成部分。
回答by Perry
The primary key is a unique value for the row. It makes no sense to include price, a value that could change. Imagine if you had to change a large range of prices, then all those primary key values would change. What a mess that would be!
主键是行的唯一值。包含价格是没有意义的，一个可能会改变的值。想象一下，如果您必须更改大范围的价格，那么所有这些主键值都会更改。那将是多么混乱！
