这里是我正在使用的一个紧张设置的一个粗略的过度简化。table_1和table_2都有自动递增的代理主键作为ID。Info是一个包含table_1和table_2信息的表。
table_1 (id, field)
table_2 (id, field, field)
info ( ???, field)
我试图决定我是否应该使信息的主键从table_1和table_2的id的组合。如果我要这么做，哪个更有意义? (在本例中，我将ID 11209与ID 437结合在一起)
INT(9) 11209437(我可以想象为什么这是坏的) Varchar (10) 11209-437 十进制(10,4)11209.437
还是别的什么?
这将是很好的使用这作为一个MYSQL MYISAM DB的主键?
当前回答
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.
其他回答
除了个人的设计偏好外，还有一些情况需要使用复合主键。表可以有两个或多个字段，提供唯一的组合，而且不一定是通过外键。
例如，美国每个州都有一组独特的国会选区。虽然许多州可能单独拥有一张CD-5，但在50个州中，永远不会有超过一张CD-5，反之亦然。因此，为Massachusetts CD-5创建一个自动编号字段是多余的。
如果数据库驱动一个动态网页，编写代码查询两个字段的组合可能比提取/重新提交一个自动编号的键要简单得多。
所以，虽然我没有回答最初的问题，但我很欣赏亚当的直接回答。
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.
CREATE TABLE `mom`.`sec_subsection` (
`idsec_sub` INT(11) NOT NULL ,
`idSubSections` INT(11) NOT NULL ,
PRIMARY KEY (`idsec_sub`, `idSubSections`)
);
我不会让“info”表的主键是来自其他表的两个值的组合。
其他人可以更好地阐述原因，但让一个实际上由两条信息组成的专栏感觉是错误的。如果出于某种原因想对来自第二个表的ID进行排序呢?如果想要计算两个表中的值出现的次数，该怎么办?
我总是把这两列分开。你可以在mysql中使用双列primay键…主键(id_a, id_b)…但我更喜欢使用两列唯一索引，并有一个自动递增的主键字段。
假设您已经创建了一个表，现在可以使用这个查询来创建复合主键
alter table employee add primary key(emp_id,emp_name);
