mysqlprimary-keymyisamcomposite-keycomposite-primary-key

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?


Solution

  • 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.