如何正确创建复合主键 - MYSQL
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5835978/
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
How to properly create composite primary keys - MYSQL
提问by filip
Here is a gross oversimplification of an intense setup I am working with. table_1and table_2both have auto-increment surrogate primary keys as the ID. infois a table that contains information about both table_1and table_2.
这是我正在使用的密集设置的粗略过度简化。table_1并且table_2都具有自动递增代理主键作为 ID。info是一个包含有关table_1和信息的表table_2。
table_1 (id, field)
table_2 (id, field, field)
info ( ???, field)
I am trying to decided if I should make the primary key of infoa composite of the IDs from table_1and table_2. If I were to do this, which of these makes most sense?
( in this example I am combining ID 11209 with ID 437 )
我正在尝试决定是否应该制作info来自table_1和的 ID 组合的主键table_2。如果我要这样做，以下哪个最有意义？
（在此示例中，我将 ID 11209 与 ID 437 结合使用）
INT(9)11209437 (i can imagine why this is bad)VARCHAR (10)11209-437DECIMAL (10,4)11209.437
INT(9)11209437 （我可以想象为什么这很糟糕）VARCHAR (10)11209-437 DECIMAL (10,4)11209.437
Or something else?
或者是其他东西？
Would this be fine to use this as the Primary Key on a MYSQL MYISAM DB?
将其用作 MYSQL MYISAM 数据库上的主键是否可以？
回答by AlexCuse
I would use a composite (multi-column) key.
我会使用复合（多列）键。
CREATE TABLE INFO (
t1ID INT,
t2ID INT,
PRIMARY KEY (t1ID, t2ID)
)
This way you can have t1ID and t2ID as foreign keys pointing to their respective tables as well.
这样，您也可以将 t1ID 和 t2ID 作为外键指向它们各自的表。
回答by wmorse
I would not make the primary key of the "info" table a composite of the two values from other tables.
我不会将“信息”表的主键作为来自其他表的两个值的组合。
Others can articulate the reasons better, but it feels wrong to have a column that is really made up of two pieces of information. What if you want to sort on the ID from the second table for some reason? What if you want to count the number of times a value from either table is present?
其他人可以更好地阐明原因，但是拥有一个真正由两条信息组成的专栏感觉是错误的。如果出于某种原因要对第二个表中的 ID 进行排序怎么办？如果您想计算任一表中某个值出现的次数怎么办？
I would always keep these as two distinct columns. You could use a two-column primay key in mysql ...PRIMARY KEY(id_a, id_b)... but I prefer using a two-column unique index, and having an auto-increment primary key field.
我总是将它们保留为两个不同的列。您可以在 mysql ...PRIMARY KEY(id_a, id_b)... 中使用两列主键...但我更喜欢使用两列唯一索引，并具有自动增量主键字段。
回答by Ritabrata Gautam
the syntax is CONSTRAINT constraint_name PRIMARY KEY(col1,col2,col3)for example ::
语法是CONSTRAINT constraint_name PRIMARY KEY(col1,col2,col3)例如 ::
CONSTRAINT pk_PersonID PRIMARY KEY (P_Id,LastName)
CONSTRAINT pk_PersonID PRIMARY KEY (P_Id,LastName)
the above example will work if you are writting it while you are creating the table for example ::
如果您在创建表格时编写它，则上面的示例将起作用，例如 ::
CREATE TABLE person (
P_Id int ,
............,
............,
CONSTRAINT pk_PersonID PRIMARY KEY (P_Id,LastName)
);
to add this constraint to an existing table you need to follow the following syntax
将此约束添加到现有表中，您需要遵循以下语法
ALTER TABLE table_name ADD CONSTRAINT constraint_name PRIMARY KEY (P_Id,LastName)
回答by Usman Yaqoob
Suppose you have already created a table now you can use this query to make composite primary key
假设您已经创建了一个表，现在您可以使用此查询来创建复合主键
alter table employee add primary key(emp_id,emp_name);
回答by KiloVoltaire
Aside from personal design preferences, there are cases where one wants to make use of composite primary keys. Tables may have two or more fields that provide a unique combination, and not necessarily by way of foreign keys.
除了个人设计偏好之外，有些情况下还希望使用复合主键。表可能有两个或多个提供唯一组合的字段，不一定通过外键。
As an example, each US state has a set of unique Congressional districts. While many states may individually have a CD-5, there will never be more than one CD-5 in any of the 50 states, and vice versa. Therefore, creating an autonumber field for Massachusetts CD-5 would be redundant.
例如，美国的每个州都有一组独特的国会选区。虽然许多州可能单独拥有一张 CD-5，但 50 个州中的任何一个州都不会超过一张 CD-5，反之亦然。因此，为马萨诸塞州 CD-5 创建自动编号字段将是多余的。
If the database drives a dynamic web page, writing code to query on a two-field combination could be much simpler than extracting/resubmitting an autonumbered key.
如果数据库驱动动态网页，则编写代码来查询两个字段的组合可能比提取/重新提交自动编号的键简单得多。
So while I'm not answering the original question, I certainly appreciate Adam's direct answer.
因此，虽然我没有回答最初的问题，但我当然感谢 Adam 的直接回答。
回答by Adam Penny
Composite primary keys are what you want where you want to create a many to many relationship with a fact table. For example, you might have a holiday rental package that includes a number of properties in it. On the other hand, the property could also be available as a part of a number of rental packages, either on its own or with other properties. In this scenario, you establish the relationship between the property and the rental package with a property/package fact table. The association between a property and a package will be unique, you will only ever join using property_id with the property table and/or package_id with the package table. Each relationship is unique and an auto_increment key is redundant as it won't feature in any other table. Hence defining the composite key is the answer.
复合主键是您想要与事实表创建多对多关系的地方。例如，您可能有一个假期租赁套餐，其中包含许多房产。另一方面，该房产也可以作为许多租赁套餐的一部分，单独或与其他房产一起使用。在此场景中，您使用属性/包事实表建立属性和租赁包之间的关系。属性和包之间的关联将是唯一的，您只能使用 property_id 与属性表和/或 package_id 与包表连接。每个关系都是唯一的，并且 auto_increment 键是多余的，因为它不会出现在任何其他表中。因此，定义复合键就是答案。
回答by Master Mind
CREATE TABLE `mom`.`sec_subsection` (
`idsec_sub` INT(11) NOT NULL ,
`idSubSections` INT(11) NOT NULL ,
PRIMARY KEY (`idsec_sub`, `idSubSections`)
);
回答by sactiw
@AlexCuse I wanted to add this as comment to your answer but gave up after making multiple failed attempt to add newlines in comments.
@AlexCuse 我想将此作为评论添加到您的答案中，但在多次尝试在评论中添加换行失败后放弃了。
That said, t1ID is unique in table_1 but that doesn't makes it unique in INFO table as well.
也就是说，t1ID 在 table_1 中是唯一的，但这并不使它在 INFO 表中也是唯一的。
For example:
例如：
Table_1has:
Id Field
1 A
2 B
Table_1有：
Id 字段
1 A
2 B
Table_2has:
Id Field
1 X
2 Y
Table_2有：
Id 字段
1 X
2 Y
INFOthen can have:
t1ID t2ID field
1 1 some
1 2 data
2 1 in-each
2 2 row
INFO然后可以有：
t1ID t2ID field
1 1 some
1 2 data
2 1 in-each
2 2 row
So in INFO table to uniquely identify a row you need both t1ID and t2ID
因此，在 INFO 表中，您需要 t1ID 和 t2ID 来唯一标识一行
