?
Solved

MySQL Composite, Primary, Unique Keys

Posted on 2011-03-16
1
Medium Priority
?
900 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

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

Creating and Managing Databases with phpMyAdmin in cPanel.
This post contains step-by-step instructions for setting up alerting in Percona Monitoring and Management (PMM) using Grafana.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
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…
Suggested Courses

771 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