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

Here is a gross oversimplification of an intense setup I am working with. table_1 and table_2 both have auto-increment surrogate primary keys as the ID. info is a table that contains information about both table_1 and 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 info a composite of the IDs from table_1 and 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 )

我正在尝试确定是否应该从table_1和table_2中提取信息的主键。如果我这样做，哪个更有意义?(在本例中，我将ID 11209与ID 437结合在一起)

INT(9) 11209437 (i can imagine why this is bad)
VARCHAR (10) 11209-437
DECIMAL (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 DB的主键可以吗?

8 个解决方案

#1

264

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作为外键指向各自的表。

#2

I would not make the primary key of the "info" table a composite of the two values from other tables.

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

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中使用两列的primay键…主键(id_a id_b)…但是我更喜欢使用两列唯一索引，并使用一个自动递增的主键字段。

#3

the syntax is CONSTRAINT constraint_name PRIMARY KEY(col1,col2,col3) for example ::

语法是CONSTRAINT constraint_name主键(col1、col2、col3)，例如:

CONSTRAINT pk_PersonID PRIMARY KEY (P_Id,LastName)

约束pk_PersonID主键(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)

#4

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键是多余的，因为它在任何其他表中都不存在。因此，定义组合键就是答案。

#5

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.

如果数据库驱动一个动态的web页面，那么编写代码在两个字段的组合上进行查询要比提取/重新提交一个自动编号的键简单得多。

So while I'm not answering the original question, I certainly appreciate Adam's direct answer.

所以，虽然我没有回答最初的问题，但我确实很欣赏亚当的直接回答。

#6

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);

#7

CREATE  TABLE `mom`.`sec_subsection` (

  `idsec_sub` INT(11) NOT NULL ,

  `idSubSections` INT(11) NOT NULL ,

  PRIMARY KEY (`idsec_sub`, `idSubSections`) 

);

#8

@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_1 has:
Id Field
1 A
2 B

表1有:Id字段1 A 2 B

Table_2 has:
Id Field
1 X
2 Y

表2有:Id字段1 X 2y

INFO then can have:
t1ID t2ID field
1 1 some
1 2 data
2 1 in-each
2 2 row

信息然后可以有:t1ID t2ID字段1 1 2数据2 1每2行

So in INFO table to uniquely identify a row you need both t1ID and t2ID

在INFO表中，要惟一地标识一行，需要t1ID和t2ID

#1

264