-> artist_id INT) NOT NULL

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 inset 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 inset 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 incremented 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 While the
AUTO_INCREMENT
feature is useful, it isn’t portable to other database environments, 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 deleting selected rows (with a
WHERE
clause) doesn’t reset the counter. Consider an example 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)

Download 4.24 Mb.

Share with your friends: