The best kind of primary key is one that has absolutely no meaning in the database except to act as a primary key. Primary keys are the tools used to identify rows uniquely in a relational database. When you use information such as a username or an email address as a primary key, you are in effect saying that the username or email address is somehow an intrinsic part of who that person is. If that person ever changes his username or email address, you will have to go to great lengths to ensure the integrity of the data in the database. Consequently, it is a better design principle to use meaningless numbers as primary keys.

To achieve this, simply make a numeric primary key that increments every time you insert a new row. Looking at the cities table shown earlier, the first city you insert would have an id of 1, the second 2, the third 3, and so on. To successfully manage this primary key sequencing, you need some way to guarantee that a number can be read and incremented by only one client at a time. You accomplish this task by making the primary key field AUTO_INCREMENT.

When you create a table in MySQL, you can specify at most one column as AUTO_INCREMENT. When you do this, you can have this column automatically insert the highest current value plus 1 for that column when you insert a row and specify NULL or 0 for that row’s value. The AUTO_INCREMENT columns must be indexed. The following command creates the cities table with an AUTO_INCREMENT id field: ^[4]

CREATE TABLE cities (id      INT  NOT NULL PRIMARY KEY AUTO_INCREMENT,
                     name    VARCHAR(100),
                     pop     INT,
                     founded DATE)

The first time you insert a row, the id field for your first row will be 1 as long as you use NULL or 0 for that field in the INSERT statement. For example, this command takes advantage of the AUTO_INCREMENT feature:

INSERT INTO cities (id, name, pop)
VALUES (NULL, 'Houston', 3000000)

If no other values are in that table when you issue this command, MySQL will set this field to 1, not NULL (remember, it cannot be NULL). If other values are present in the table, the value inserted will be one greater than the largest current value for id.

You can also implement sequences by referring to the value returned by the LAST_INSERT_ID( ) function and doing your own incrementing:

UPDATE table_name SET id=LAST_INSERT_ID( id+1 );

The AUTO_INCREMENT attribute may be supplied for at most one column of an integer type in a table. In addition to being an integer type, the column must be either a primary key or the sole column in a unique index. When you attempt an insert into a table with such an integer field and fail to specify a value for that field (or specify a NULL value), a value of one greater than the column’s current maximum value will be automatically inserted.

Chapter 17 contains reference material on the LAST_INSERT_ID( ) function.