Solved

MySQL Composite, Primary, Unique Keys

Posted on 2011-03-16
1
877 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 50

Accepted Solution

by:
Steve Bink earned 250 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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Suggested Solutions

Introduction In this article, I will by showing a nice little trick for MySQL similar to that of my previous EE Article for SQLite (http://www.sqlite.org/), A SQLite Tidbit: Quick Numbers Table Generation (http://www.experts-exchange.com/A_3570.htm…
Foreword This is an old article.  Instead of using the MySQL extension that was used in the original code examples, please choose one of the currently supported database extensions instead.  More information is available here: MySQLi / PDO (http://…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…
A short film showing how OnPage and Connectwise integration works.

937 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

Need Help in Real-Time?

Connect with top rated Experts

4 Experts available now in Live!

Get 1:1 Help Now