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?

Faience

Possible duplicate of Multi-Column Primary Key in MySQL 5 – Monkhmer

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

Catlin

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 ? – Faience

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

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

@Catlin 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? – Caldarium

@Caldarium 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. – Catlin

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

Otoscope

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? – Faience

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. – Otoscope

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 – Faience

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. – Circumfluent

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? – Nicolnicola

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. – Gabriellegabrielli

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

Lotson

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

Kuroshio

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

Blackshear

Answer 6

Composite primary keys are what you want where you want to create a many to many relationship with a fact table. For example, you might have a holiday rental package that includes a number of properties in it. On the other hand, the property could also be available as a part of a number of rental packages, either on its own or with other properties. In this scenario, you establish the relationship between the property and the rental package with a property/package fact table. The association between a property and a package will be unique, you will only ever join using property_id with the property table and/or package_id with the package table. Each relationship is unique and an auto_increment key is redundant as it won't feature in any other table. Hence defining the composite key is the answer.

Doubletime

Answer 7

CREATE  TABLE `mom`.`sec_subsection` (

  `idsec_sub` INT(11) NOT NULL ,

  `idSubSections` INT(11) NOT NULL ,

  PRIMARY KEY (`idsec_sub`, `idSubSections`) 

);

Trifoliate

Answer 8

@AlexCuse I wanted to add this as comment to your answer but gave up after making multiple failed attempt to add newlines in comments.

That said, t1ID is unique in table_1 but that doesn't makes it unique in INFO table as well.

For example:

Table_1 has:
Id Field
1 A
2 B

Table_2 has:
Id Field
1 X
2 Y

INFO then can have:
t1ID t2ID field
1 1 some
1 2 data
2 1 in-each
2 2 row

So in INFO table to uniquely identify a row you need both t1ID and t2ID

Rolfrolfe

its called composite key – Showplace

@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. – Rolfrolfe