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?

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.

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.

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)

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

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.

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.

Answer 7

CREATE  TABLE `mom`.`sec_subsection` (

  `idsec_sub` INT(11) NOT NULL ,

  `idSubSections` INT(11) NOT NULL ,

  PRIMARY KEY (`idsec_sub`, `idSubSections`) 

);

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