Choosing the Type for a Sequence Column
Problem
You want to know more about how to define a sequence column.
Solution
Use the guidelines given here.
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 useINT
, but the column must be one of the integer types:TINYINT
,SMALLINT
,MEDIUMINT
,INT
, orBIGINT
. It’s important to remember thatAUTO_INCREMENT
is a column attribute that should be applied only to integer types. Older versions of MySQL will allow you to create anAUTO_INCREMENT
column using non-integer types such asCHAR
, 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 forAUTO_INCREMENT
columns.The column is declared as
UNSIGNED
. There’s no need to allow negative values, becauseAUTO_INCREMENT
sequences consist only of positive integers (normally beginning at 1). Furthermore, not declaring the column to be ...
Get MySQL Cookbook now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.