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

2021-11-20 00:00:00 mysql primary-key composite-primary-key myisam composite-key

这是我正在使用的密集设置的粗略过度简化.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
十进制 (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 数据库上的主键是否可以?

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.

相关文章