?
Solved

MySQL Composite, Primary, Unique Keys

Posted on 2011-03-16
1
Medium Priority
?
910 Views
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,
-marc
0
Comment
Question by:marcparillo
1 Comment
 
LVL 51

Accepted Solution

by:
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.
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
Backups and Disaster RecoveryIn this post, we’ll look at strategies for backups and disaster recovery.
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…
Suggested Courses
Course of the Month16 days, 1 hour left to enroll

850 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