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

User · Accepted Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

CREATE  TABLE  mom   sec subsection        idsec sub  INT 11  NOT NULL       idSubSections  INT 11  NOT NULL      PRIMARY KEY   idsec sub    idSubSections

[mysql] How to properly create composite primary keys - MYSQL

Examples related to mysql

Examples related to primary-key

Examples related to myisam

Examples related to composite-key

Examples related to composite-primary-key