MySQL Composite, Primary, Unique Keys

Posted on 2011-03-16
Medium Priority
Last Modified: 2012-05-11

I'm building a relational MySQL DB (MYISAM) and I have a question about when it's appropriate to create composite keys.

I have several tables in which each row has its own unique ID# and the unique ID is a PRIMARY KEY.  I need the ID#'s to be the primary key because the IDS#'s relate to one another through different tables using JOIN syntax.  

Trouble is, I also need a row in each table that is auto_increment, but to have an auto_increment column, it also needs to be a primary key.

So I have:

| Field       | Type                  | Null | Key | Default | Extra          |
| dbid        | mediumint(8) unsigned | NO   | PRI | NULL    | auto_increment |
| id          | varchar(35)           | NO   | PRI |         |                |
| title       | varchar(255)          | YES  | MUL | NULL    |                |
| description | text                  | YES  |     | NULL    |                |
| start       | int(12) unsigned      | YES  |     | NULL    |                |
| end         | int(12) unsigned      | YES  |     | NULL    |                |
| added       | int(12) unsigned      | YES  |     | NULL    |                |
| publish     | tinyint(3) unsigned   | YES  |     | 1       |                |

In this table, the ID is what's most important to me.  It needs to be the TRUE primary key, but I also need DBID to be auto_increment.  The way the table is set up now (see above), I'm not sure which one is PRIMARY and which one is COMPOSITE?

So, my question is:  what's the best way to go about making sure the ID the PRIMARY KEY and DBID is the COMPOSITE PRIMARY KEY -- or should I go about this a different way?

Thank you,
Question by:marcparillo
1 Comment
LVL 51

Accepted Solution

Steve Bink earned 1000 total points
ID: 35153689
Your question is not so much about composite keys as it is about the definition of a primary key.  The actual key assignments are not clear in the DESCRIBE output you posted, though it could be you have created a composite primary key.  If you post the output of "SHOW CREATE TABLE", I can explain further.

A primary key is simply a field (or fields, in the case of composites) that is guaranteed to uniquely identify a single row.  In other words, a unique index.  The MySQL manual often states that the terms "key" and "index" are interchangeable.  A primary key is given some small amount of additional functionality (for example, _rowid nomenclature, insert_id, etc.), but it otherwise functions exactly as a unique index.  Just because an index is unique, though, does not mean it must be the primary.  You can have a non-primary unique index, and I would describe that practice as common.  Since autoincrement fields in MySQL must be primary keys, the solution in your case is to create dbid as the primary key, and use the id field as a unique index.  The database engine really does not care about the importance you place on any individual field, and the semantics will not affect your data.

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
In this blog, we’ll look at how improvements to Percona XtraDB Cluster improved IST performance.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

621 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question