如何正确创建复合主键-MYSQL [英] How to properly create composite primary keys - MYSQL

问题描述

这里是我正在使用的密集设置的过度简化. table_1和table_2都具有自动增量代理主键作为ID. info是一个包含有关table_1和table_2的信息的表.

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 (id, field)  
table_2 (id, field, field)
info ( ???, field)

我试图确定是否应该将info的主键组合为table_1和table_2的ID.如果我要这样做，哪一个最有意义?
(在此示例中，我将ID 11209与ID 437组合在一起)

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 )

INT(9) 11209437 (我可以想象为什么这很糟糕)
VARCHAR (10) 11209-437
DECIMAL (10,4) 11209.437

INT(9) 11209437 (i can imagine why this is bad)
VARCHAR (10) 11209-437
DECIMAL (10,4) 11209.437

还是其他?

将其用作MYSQL MYISAM DB上的主键是否可以?

Would this be fine to use this as the Primary Key on a MYSQL MYISAM DB?

推荐答案

我将使用复合(多列)键.

I would use a composite (multi-column) key.

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

这样，您还可以将t1ID和t2ID作为指向它们各自表的外键.

This way you can have t1ID and t2ID as foreign keys pointing to their respective tables as well.

这篇关于如何正确创建复合主键-MYSQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！