How to properly create composite primary keys - MYSQL

Question

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 1

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.

Solution 2

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

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?

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.

Solution 3

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

CONSTRAINT pk_PersonID PRIMARY KEY (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)

Solution 4

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

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

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.

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

View more solutions

Comments

[dotancohen]

Possible duplicate of Multi-Column Primary Key in MySQL 5

[filip]

Oh wow so THAT'S how you make a composite key! looks like i've been totally misunderstanding the concept. Thank you!! So something like this is entirely for indexing purposes then correct? As in I wouldn't be able to reference a record by using this composite, I would still have to so do an UPDATE info ... WHERE t1ID=11209 AND t2ID=437 ?

[filip]

You are absolutely right about keeping distinct columns. I was unaware that you could have two-column unique index and I think that may actually be a good option for me. Could I ask though why you would still prefer to keep the Primary Key as auto-increment?

[Xavier Young]

correct. Although since both columns should be unique, where t1ID = 11209 would probably be sufficient.

[wmorse]

I don't have really compelling reasons, and I concede this is a point of contention between me and some of my colleagues, because it is more economical to have fewer columns. I find it easier to write joins on a single foreign key. Sometimes the significance of these tables "Mappings between two tables" becomes as important as the original tables, and its primary key becomes a foreign key column in yet other tables.

[filip]

thank you. I think what your saying makes much sense and I will try it as a two-column unique index + auto-increment primary key

[JoomGuy]

I agree with @wmorse, having a single field as an auto-incrementing primary key with a unique index on your 2 (or more) foreign key fields seems to fit most of my use-cases more readily than composite keys.

[Christopher Thomas]

I guess a reason from the top of my head is that you want to create a relationship table. you have three tables, say market place, currency and provider, a provider can ONLY exist in a market place ONCE, so your relationship table can only provide a single currency, so you'd composite (id_market,id_provider) meaning you can only make that connection once, attempting to add again the same market and provider together will fail, meaning they are unique, then you'd have a second column, say id_currency, meaning the currency is singular in the entire table, does that make sense?

[smcjones]

For anyone who sees this thread later, please note that the reason this is bad practice is because it violates a very basic principle of database design. 1st Normal Form requires that every piece of information have its own column. There is virtually no reason to violate this and multiple benefits to normalizing your database structure.

[e18r]

@AlexCuse the combination of both columns is unique, but for t1ID = 11209 there can be any number of t2IDs.

[Pavel P]

its called composite key

[sactiw]

@PavelP my reply is w.r.t Alex's comment "Although since both columns should be unique, where t1ID = 11209 would probably be sufficient." ... I agree that using composite key is correct but to identify exact match you will need both t1ID and t2ID ... I hope it is clear now.

[temuri]

@AlexCuse If I have composite key (field1, field2) does it make sense to create separate index of field1 on top, or it being in composite is enough?

[Xavier Young]

@temuri that really depends on data size and access patterns in your application. For a very large table that is often queried with a where clause on only the one column a separate index could be worth it.