How to properly create composite primary keys - MY

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

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 2

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 3

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 4

@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

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

CREATE  TABLE `mom`.`sec_subsection` (

  `idsec_sub` INT(11) NOT NULL ,

  `idSubSections` INT(11) NOT NULL ,

  PRIMARY KEY (`idsec_sub`, `idSubSections`) 

);