Working with Database Structures - Learning MySQL - page 209

When you view the data in this table you can see that each artist has a meaningful
artist_id :
mysql> SELECT * FROM artist;
+-----------+-------------+
| artist_id | artist_name |
+-----------+-------------+
| 1 | The Shamen |
| 2 | Probot |
| 3 | The Cult |
+-----------+-------------+
3 rows in set (0.01 sec)
Each time an artist is inserted, a unique artist_id is created for the new row.
Let's consider how the new feature works. You can see that the artist_id column is
declared as an integer with the clauses NOT NULL AUTO_INCREMENT . The AUTO_INCREMENT
keyword tells MySQL that when a value isn't provided for this column, the value allo-
cated should be one more than the maximum currently stored in the table. The
AUTO_INCREMENT sequence begins at 1 for an empty table.
The NOT NULL is required for AUTO_INCREMENT columns; when you insert NULL (or 0,
though this isn't recommended), the MySQL server automatically finds the next avail-
able identifier and assigns it to the new row. You can manually insert negative values
if the column was not defined as UNSIGNED ; however, for the next automatic increment,
MySQL will simply use the largest (most positive) value in the column, or start from 1
if there are no positive values.
The AUTO_INCREMENT feature has the following requirements:
• The column it is used on must be indexed.
• The column that is it used on cannot have a DEFAULT value.
• There can be only one AUTO_INCREMENT column per table.
MySQL supports different table types; we'll learn more about these in “Table Types”
in Chapter 7. When you're using the default MyISAM table type, you can use the
AUTO_INCREMENT feature on keys that comprise multiple columns. In our music database
example, we could create the album table as follows:
mysql> CREATE TABLE album (
-> artist_id INT(5) NOT NULL,
-> album_id INT(4) NOT NULL AUTO_INCREMENT,
-> album_name CHAR(128) DEFAULT NULL,
-> PRIMARY KEY (artist_id, album_id)
-> );
Query OK, 0 rows affected (0.00 sec)
You can see that the primary key is on two columns— artist_id and album_id —and
that the AUTO_INCREMENT feature is applied to the album_id column.