Solved

MySQL Composite, Primary, Unique Keys

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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Join & Write a Comment

Both Easy and Powerful How easy is PHP? http://lmgtfy.com?q=how+easy+is+php (http://lmgtfy.com?q=how+easy+is+php)  Very easy.  It has been described as "a programming language even my grandmother can use." How powerful is PHP?  http://en.wikiped…
Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

760 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

21 Experts available now in Live!

Get 1:1 Help Now