Skip to Main Content
MySQL Cookbook
book

MySQL Cookbook

by Paul DuBois
October 2002
Intermediate to advanced content levelIntermediate to advanced
1024 pages
27h 26m
English
O'Reilly Media, Inc.
Content preview from MySQL Cookbook

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 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 ...

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.
Start your free trial

You might also like

MySQL Reference Manual

MySQL Reference Manual

Michael Widenius, David Axmark, Kaj Arno
High Performance MySQL

High Performance MySQL

Jeremy D. Zawodny, Derek J. Balling
MySQL Stored Procedure Programming

MySQL Stored Procedure Programming

Guy Harrison, Steven Feuerstein

Publisher Resources

ISBN: 0596001452Catalog PageErrata