如何正确创建复合主键 - MYSQL

Question

以下是我正在处理的一个复杂设置的严重简化版本。 table_1 和 table_2 都拥有自动增量代理主键作为ID。 info 是一个包含有关table_1和table_2的信息的表。

table_1 (id, field)  
table_2 (id, field, field)
info ( ???, field)

我正在考虑是否应该将info的主键设为来自table_1和table_2的ID组合。如果我这样做，哪种方式更合理？
（在此示例中，我将ID 11209与ID 437组合）

INT（9） 11209437 （我可以想象为什么这是不好的）
VARCHAR（10） 11209-437
DECIMAL（10,4） 11209.437

还是其他方法更好？

在MYSQL MYISAM数据库中使用这个作为主键可以吗？

- filip

---

可能是MySQL 5中的多列主键的重复问题。 - dotancohen

Answer 1

我会使用一个复合（多列）键。

CREATE TABLE INFO (
    t1ID INT,
    t2ID INT,
    PRIMARY KEY (t1ID, t2ID)
) 

这样你就可以将t1ID和t2ID作为外键分别指向它们各自的表。

- AlexCuse

---

哦，原来是这样制作复合键的！看起来我完全误解了这个概念。谢谢！所以这样的东西完全是为了索引目的，对吗？也就是说，我不能使用这个复合键来引用记录，我仍然需要执行 UPDATE info ... WHERE t1ID=11209 AND t2ID=437 ？ - filip

虽然两列都应该是唯一的，但t1ID = 11209可能已经足够了。 - AlexCuse

@AlexCuse 表示两列的组合是独一无二的，但对于 t1ID = 11209，可以有任意数量的 t2IDs。 - e18r

@AlexCuse 如果我有复合键（field1，field2），是否有意义在其上创建field1的单独索引，还是它作为复合键已经足够了？ - temuri

@temuri 这真的取决于你的应用程序中的数据大小和访问模式。对于一个非常大的表，如果经常使用 where 子句查询只有一个列，那么单独创建一个索引可能是值得的。 - AlexCuse

Answer 2

我不会将“info”表的主键设置为其他表中两个值的组合。

其他人可能会更好地阐述原因，但是这样做感觉不对，因为有一个列实际上由两个信息组成。如果您想以第二个表中的ID排序怎么办？如果您想计算任一表中某个值出现的次数怎么办？

我始终将它们保留为两个独立的列。您可以在MySQL中使用两列主键...PRIMARY KEY(id_a, id_b)...但我更喜欢使用两列唯一索引，并且具有自动递增的主键字段。

- wmorse

---

你关于保持不同列的观点是完全正确的。我之前不知道可以有两列唯一索引，我认为这对我来说可能是一个好选择。但我想问一下，为什么您仍然更喜欢将主键设置为自动递增？ - filip

我没有非常有说服力的理由，我承认这是我和一些同事之间存在争议的一个问题，因为使用更少的列是更经济的。我发现在单个外键上编写联接更容易。有时候，“两个表之间的映射”这些表的重要性变得与原始表一样重要，并且它的主键成为其他表中的外键列。 - wmorse

谢谢。我认为你说的很有道理，我会尝试使用双列唯一索引+自增主键。 - filip

我同意@wmorse的观点，将一个字段作为自增主键，并在您的2（或更多）外键字段上创建唯一索引似乎比复合键更适合我的大多数用例。 - JoomGuy

我猜测你想创建一个关系表。假设你有三个表，分别是市场、货币和供应商，一个供应商只能在一个市场中存在一次，因此你的关系表只能提供单一的货币，所以你需要组合(id_market,id_provider)，这意味着你只能建立一次连接，如果再次尝试添加相同的市场和供应商，则会失败，这意味着它们是唯一的，然后你需要第二列，比如id_currency，这意味着整个表中只有一个货币，明白了吗？ - Christopher Thomas

对于任何后来看到这个帖子的人，请注意，这种做法之所以不好，是因为它违反了数据库设计的一个非常基本的原则。第一范式要求每个信息都有自己的列。几乎没有理由违反这个原则，而规范化数据库结构有多重好处。 - smcjones

Answer 3

语法为CONSTRAINT constraint_name PRIMARY KEY(col1,col2,col3)，例如：

CONSTRAINT pk_PersonID PRIMARY KEY (P_Id,LastName)

以上示例适用于在创建表时编写它的情况，例如：

CREATE TABLE person (
   P_Id int ,
   ............,
   ............,
   CONSTRAINT pk_PersonID PRIMARY KEY (P_Id,LastName)
);

要将此约束添加到现有表中，您需要遵循以下语法

ALTER TABLE table_name ADD CONSTRAINT constraint_name PRIMARY KEY (P_Id,LastName)

- RbG

Answer 4

假设您已经创建了一个表，现在可以使用此查询来创建组合主键

alter table employee add primary key(emp_id,emp_name);

- Usman Yaqoob

Answer 5

除了个人设计偏好之外，有些情况下需要使用复合主键。 表格可能具有两个或多个字段，这些字段提供了一个唯一的组合，并且不一定是通过外键提供的。

例如，每个美国州都有一组独特的国会选区。 虽然许多州可能分别拥有CD-5，但在任何50个州中都不会有超过一个CD-5，反之亦然。 因此，为马萨诸塞州CD-5创建自动编号字段将是多余的。

如果数据库驱动动态网页，则编写查询两个字段组合的代码可能比提取/重新提交自动编号的关键字要简单得多。

因此，虽然我没有回答最初的问题，但我非常感谢亚当的直接回答。

- KiloVoltaire

Answer 6

当你想创建一个与事实表之间存在多对多关系的时候，你需要使用组合主键。例如，你可能有一个包含多个属性的假日租赁套餐。另一方面，这些属性也可以作为多个租赁套餐中的一部分，要么单独出租，要么与其他属性一起出租。在这种情况下，你需要使用一个属性/套餐事实表来建立属性和租赁套餐之间的关系。属性和套餐之间的关联是唯一的，你只能使用属性表中的property_id或者套餐表中的package_id进行连接。每个关系都是唯一的，因此自增键是多余的，因为它不会在任何其他表中出现。因此，定义组合键就是解决方案。

- Adam Penny

Answer 7

CREATE  TABLE `mom`.`sec_subsection` (

  `idsec_sub` INT(11) NOT NULL ,

  `idSubSections` INT(11) NOT NULL ,

  PRIMARY KEY (`idsec_sub`, `idSubSections`) 

);

- Master Mind

Answer 8

@AlexCuse 我想将这个作为评论添加到您的答案中，但在尝试在评论中添加换行符后多次失败后放弃了。

话虽如此，t1ID 在 table_1 中是唯一的，但在 INFO 表中并非如此。

例如：

Table_1 有：
Id 字段
1 A
2 B

Table_2 有：
Id 字段
1 X
2 Y

INFO 可以有：
t1ID t2ID 字段
1 1 一些
1 2 数据
2 1 每个中的
2 2 行

因此，在 INFO 表中要想唯一地标识一行，您需要同时使用 t1ID 和 t2ID。

- sactiw

---

它被称为复合键。 - Pavel P

@PavelP 我的回复是针对Alex的评论“虽然两列都应该是唯一的，但在t1ID = 11209的情况下可能已经足够了。”...我同意使用组合键是正确的，但要识别精确匹配，您需要t1ID和t2ID...现在希望清楚了。 - sactiw