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 inset 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 allocated 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 NULLis required for
AUTO_INCREMENT
columns; when you insert
NULL
(or though this isn’t
recommended, the MySQL server automatically finds the next available 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 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 (Share with your friends: 
