The AUTO_INCREMENT data type
Sometimes you need to ensure that every row in your database is guaranteed to be unique. You could do this in your program by carefully checking the data you enter and making sure that there is at least one value that differs in any two rows, but this approach is error-prone and works only in certain circumstances. In the classics table, for instance, an author may appear multiple times. Likewise, the year of publication
will also be frequently duplicated, and so on. It would be hard to guarantee that you have no duplicate rows.
The general solution is to use an extra row just for this purpose. In a while, we’ll look at using a publication’s ISBN (International Standard Book Number), but first I’d like to introduce the AUTO_INCREMENT data type.
As its name implies, a column given this data type will set the value of its contents to that of the column entry in the previously inserted row, plus 1. Following Example shows how to add a new column called id to the table classics with auto-incrementing:
ALTER TABLE classics ADD id INT UNSIGNED NOT NULL AUTO_INCREMENT KEY;
This is your introduction to the ALTER command, which is very similar to CREATE. ALTER operates on an existing table, and can add, change, or delete columns. Our example adds a column named id with the following characteristics:
| INT UNSIGNED | Makes the column take an integer large enough for you to store more than 4 billion records in the table. |
| NOT NULL | Ensures that every column has a value. Many programmers use NULL in a field to indicate that the field doesn’t have any value. But that would allow duplicates, which would violate the whole reason for this column’s existence. So we disallow NULL values. |
| AUTO_INCREMENT | Causes MySQL to set a unique value for this column in every row, as described earlier. We don’t really have control over the value that this column will take in each row, but we don’t care: all we care about is that we are guaranteed a unique value. |
| KEY | An auto-increment column is useful as a key, because you will tend to search for rows based on this column. |
Each entry in the column id will now have a unique number, with the first starting at 1 and the others counting upward from there. And whenever a new row is inserted, its id column will automatically be given the next number in sequence. Rather than applying the column retroactively, you could have included it by issuing the CREATE command in slightly different format.
Consider the auto-incrementing id column at table creation example :
CREATE TABLE classics ( author VARCHAR(128), title VARCHAR(128), type VARCHAR(16), year CHAR(4), id INT UNSIGNED NOT NULL AUTO_INCREMENT KEY) ENGINE MyISAM;
If you wish to check whether the column has been added, use the following command to view the table’s columns and data types:
DESCRIBE classics;
