filip filip - 2 months ago 11
MySQL Question

How to properly create composite primary keys - 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 (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 )

INT(9)
11209437 (i can imagine why this is bad)

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?

Answer Source

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.