MySQL Tables Want Integer ID Columns

Most Tables Need Unique Columns

• Database rows must be uniquely identifiable in order to SELECT, UPDATE, or DELETE them individually.

• Database rows often need to be referred to externally, such as in web URLs, or between humans over the phone.

Why Unique Columns Should be Meaningless, Auto-Incrementing Integers

• Real-world data, such as Social Security Numbers, are poor choices for unique IDs because:

• They might change. Even if you think they can't change, they can change (at the least, the data may be entered incorrectly). When the data changes, you will need to update all rows in all tables that refer to this table.

• They might be duplicated. Don't let "broken" real-world data break your database.

• They might not exist. A person may not have a SSN, a new book may not yet have an ISBN, and a new employee might not yet have been assigned an Employee ID number, but you might still want to add them to your database. Whatever real-world data you choose, eventually someone will not have it, not remember it, or not want to share it.

• They take more storage space than an integer ID column (which can be 1, 2, 3, 4, or 8 bytes, depending on how many rows you will store), meaning wasted space in other tables that refer to this table.

• Sensitive data such as SSNs should not be used externally. For example, in a web URL for editing that record.

• When choosing a data type for a meaningless unique column, there is little reason not to choose integers. They take less store space (and index space) than strings, are faster to compare, and are easy to increment automatically with MySQL's built-in AUTO_INCREMENT feature.

When an ID Column is Not Needed

A unique integer ID is not needed when the row has other unique, internal data, usually a combination of other table IDs, and there is no need to reference the row externally.

This is often the case with joiner tables.

For example, a system where users submit ratings on stories might have a table like this:

CREATE TABLE 'story_ratings' (
  'story_id' INT4 NOT NULL default 0,
  'user_id' INT4 NOT NULL default 0,
  'rating' INT1 NOT NULL default 0,
  UNIQUE 'story_user' ('story_id','user_id'),
  KEY 'user' ('user_id')
) ENGINE=MyISAM;

Rows in this table can be referred to by the UNIQUE index on (story_id, user_id). The index will also enforce the business rule that users cannot submit two ratings for the same story.

How to Create Unique Auto-Incrementing Integer Indexes

The standard is to name unique ID columns "id", "ID", or after the table name, such as "book_id". Choose one and be consistent across your database tables.

When creating your table, make this the first field:

id INT4  NOT NULL AUTO_INCREMENT

When inserting new rows, either set the 'id' column to NULL, or do not set the 'id' column at all, and it will automatically receive a unique number.