Databases Reference
In-Depth Information
Suppose you want to insert two albums for The Shamen, the artist we added earlier
with an
artist_id
of 1. Here's how you do it:
mysql>
INSERT INTO album VALUES (1, NULL, "Boss Drum");
Query OK, 1 row affected (0.00 sec)
mysql>
INSERT INTO album VALUES (1, NULL, "Entact");
Query OK, 1 row affected (0.00 sec)
Now, let's inspect the results:
mysql>
SELECT * FROM album WHERE artist_id = 1;
+-----------+----------+------------+
| artist_id | album_id | album_name |
+-----------+----------+------------+
| 1 | 1 | Boss Drum |
| 1 | 2 | Entact |
+-----------+----------+------------+
2 rows in set (0.00 sec)
You can see that the correct
album_id
values are assigned; this is just as we'd expect.
Now, consider what happens when we add two albums for the artist “The Cult”:
mysql>
INSERT INTO album VALUES (3, NULL, "Electric");
Query OK, 1 row affected (0.01 sec)
mysql>
INSERT INTO album VALUES (3, NULL, "Sonic Temple");
Query OK, 1 row affected (0.00 sec)
Here are the results:
mysql>
SELECT * FROM album WHERE artist_id = 3;
+-----------+----------+--------------+
| artist_id | album_id | album_name |
+-----------+----------+--------------+
| 3 | 1 | Electric |
| 3 | 2 | Sonic Temple |
+-----------+----------+--------------+
2 rows in set (0.00 sec)
You can see how the feature works with two columns in the primary key: it's reused
the
artist_id
value that was used for The Cult, and the weak key (
album_id
) is incre-
mented automatically. This ensures that the album primary key (the combination of
artist_id
and
album_id
) is unique for each album. We now have albums 1 and 2 for
The Shamen (with an
artist_id
of 1), and albums 1 and 2 for The Cult (with an
artist_id
of 3).
While the
AUTO_INCREMENT
feature is useful, it isn't portable to other database environ-
ments, and it hides the logical steps to creating new identifiers. It can also lead to
ambiguity; for example, dropping or truncating a table will reset the counter, but de-
leting selected rows (with a
WHERE
clause) doesn't reset the counter. Consider an ex-
ample; let's create the table
count
that contains an auto-incrementing field
counter
:
mysql>
CREATE TABLE count (counter INT AUTO_INCREMENT KEY);
Query OK, 0 rows affected (0.13 sec)
