MySQL Cookbook

11.4 Choosing the Type for a Sequence Column

11.4.1 Problem

You want to know more about how to define a sequence column.

11.4.2 Solution

Use the guidelines given here.

11.4.3 Discussion

You should follow certain guidelines when creating an AUTO_INCREMENT column. As an illustration, consider how the id column in the insect table was declared:

id INT UNSIGNED NOT NULL AUTO_INCREMENT,
PRIMARY KEY (id)

The AUTO_INCREMENT keyword informs MySQL that it should generate successive sequence numbers for the column's values, but the other information is important, too:

• INT is the column's basic type. You need not necessarily use INT, but the column must be one of the integer types: TINYINT, SMALLINT, MEDIUMINT, INT, or BIGINT. It's important to remember that AUTO_INCREMENT is a column attribute that should be applied only to integer types. Older versions of MySQL will allow you to create an AUTO_INCREMENT column using non-integer types such as CHAR, but bad things will happen if you do that. (Even if the initial sequence numbers appear to be generated normally, sooner or later the column will fail. A typical error is "duplicate key" after inserting a few records, even when you know the column should be able to hold more numbers.) Save yourself some trouble—always use an integer type for AUTO_INCREMENT columns.

• The column is declared as UNSIGNED. There's no need to allow negative values, because AUTO_INCREMENT sequences consist only of positive integers (normally beginning at 1). Furthermore, not declaring the column to be UNSIGNED cuts the range of your sequence in half. For example, TINYINT has a range of -128 to 127. Sequences include only positive values, so the range of a TINYINT sequence would be 1 to 127. The range of an unsigned TINYINT column is 0 to 255, which increases the upper end of the sequence to 255. The maximum sequence value is determined by the specific integer type used, so you should choose a type that is big enough to hold the largest value you'll need. The maximum unsigned value of each integer type is shown in the following table, which you can use to select an appropriate type.

  Column type	Maximum unsigned value
  TINYINT	255
  SMALLINT	65,535
  MEDIUMINT	16,777,215
  INT	4,294,967,295
  BIGINT	18,446,744,073,709,551,615

  Sometimes people omit UNSIGNED so that they can create records that contain negative numbers in the sequence column. (Using -1 to signify "has no ID" would be an instance of this.) MySQL makes no guarantees about how negative numbers will be treated, so you're playing with fire if you try to use them in an AUTO_INCREMENT column. For example, if you resequence the column, you'll find that all your negative values get turned into regular (positive) sequence numbers.

• AUTO_INCREMENT columns cannot contain NULL values, so id is declared as NOT NULL. (It's true that you can specify NULL as the column value when you insert a new record, but for an AUTO_INCREMENT column that really means "generate the next sequence value.") Current versions of MySQL automatically define AUTO_INCREMENT columns as NOT NULL if you forget to. However, it's best to indicate NOT NULL in the CREATE TABLE statement explicitly if there is a possibility that you might use it with an older version of MySQL sometime.

• The column is declared as a PRIMARY KEY to ensure that its values are unique. Tables can have only one PRIMARY KEY, so if the table already has some other PRIMARY KEY column, you can declare an AUTO_INCREMENT column to have a UNIQUE index instead:

  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  UNIQUE (id)

  If the AUTO_INCREMENT column is the only column in the PRIMARY KEY or UNIQUE index, you can declare it as such in the column definition rather than in a separate clause. For example, these definitions are equivalent:

  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (id)
  
  id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY

  As are these:

  id INT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE
  
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  UNIQUE (id)

  Using a separate clause to specify the index helps to emphasize that it's not, strictly speaking, part of the column definition. (If you read through Chapter 8, you'll notice that modifying a column's indexes is discussed separately from changing the definition of the column itself.)

When creating a table that contains an AUTO_INCREMENT column, it's also important to consider the table type (MyISAM, InnoDB, and so forth). The type affects behaviors such as reuse of values that are deleted from the top of the sequence, and whether or not you can set the initial sequence value. In general, MyISAM is the best type for tables that contain AUTO_INCREMENT columns, because it offers the most features for sequence management. This will become apparent as you continue through the chapter.