Multi-Column Primary Key in MySQL 5

Question

I'm trying to learn how to use keys and to break the habit of necessarily having SERIAL type IDs for all rows in all my tables. At the same time, I'm also doing many-to-many relationships, and so requiring unique values on either column of the tables that coordinate the relationships would hamper that.

How can I define a primary key on a table such that any given value can be repeated in any column, so long as the combination of values across all columns is never repeated exactly?

Answer 1

From CREATE TABLE Syntax

A PRIMARY KEY can be a multiple-column index. However, you cannot create a multiple-column index using the PRIMARY KEY key attribute in a column specification. Doing so only marks that single column as primary. You must use a separate PRIMARY KEY(index_col_name, ...) clause.

Something like

CREATE TABLE product (category INT NOT NULL, id INT NOT NULL,
                      price DECIMAL,
                      PRIMARY KEY(category, id))

from 13.6.4.4. FOREIGN KEY Constraints

Answer 2

Primary key is a domain concept that uniquely (necessary and sufficient) identifies your entity among similar entities. Composite (multiple column) primary key make sence only when a part of the key refers to a particular instance of another domain entity.

Lets say you have a personal collection of books, as long as you are alone you can count them and assign each a number, the number is the primary key of the domain of your library.

Now you want to merge your library with that of your neighbour but still be able to destinguish to whom a book belongs. the domain is now broader and the primary key is now (owner, book_id).

Thus, making each primary key composite, should not be your strategy, your should use it when required.

Now some facts about MySQL. If you define a composite primary key and want the RDBSM to autoincrement the ids for you, you should know about the difference between MyISAM and InnoDB behaviour.

Let's say we want a table with two fields: parent_id, child_id. And we want the child_id be autoincremented.

MyISAM will autoincrement uniquely within records with the same parent_id, and InnoDB will autoincrement uniquely within the whole table.

In MyISAM you should define the primary key as (parent_id, child_id), and in InnoDB - as (child_id, parent_id), because autoincremented field should be leftmost constituent in the primary key in InnoDB.