Databases Reference
In-Depth Information
The third way is to use the
UNIQUE
(or
PRIMARY KEY
or
KEY
) keyword in combination with
the
CREATE TABLE
and
SELECT
to add a primary-key index. Here's an example of this
approach:
mysql>
CREATE TABLE artist_2 (UNIQUE(artist_id))
-> SELECT * FROM artist;
Query OK, 7 rows affected (0.27 sec)
Records: 7 Duplicates: 0 Warnings: 0
mysql>
DESCRIBE artist_2;
+-------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+-------+
| artist_id | smallint(5) | | PRI | 0 | |
| artist_name | char(128) | YES | | NULL | |
+-------------+-------------+------+-----+---------+-------+
2 rows in set (0.26 sec)
The
UNIQUE
keyword is applied to the
artist_id
column, making it the primary key in
the newly created table. The keywords
UNIQUE
and
PRIMARY KEY
can be interchanged.
You can use different modifiers when you're creating tables using these techniques. For
example, here's a table created with defaults and other settings:
mysql>
CREATE TABLE artist_3
-> (artist_id SMALLINT(5) NOT NULL AUTO_INCREMENT,
-> artist_name CHAR(128) NOT NULL DEFAULT "New Order",
-> PRIMARY KEY (artist_id), KEY (artist_name))
-> SELECT * FROM artist;
Query OK, 7 rows affected (0.31 sec)
Records: 7 Duplicates: 0 Warnings: 0
Here, we've set
NOT NULL
for the new columns, used the
AUTO_INCREMENT
feature on
artist_id
, and created two keys. Anything you can do in a regular
CREATE TABLE
state-
ment can be done in this variant; just remember to add those indexes explicitly!
Updates and Deletes with Multiple Tables
In Chapter 5, we showed you how to update and delete data. In the examples there,
each update and delete affected one table and used properties of that table to decide
what to modify. This section shows you more complex updates and deletes, with which
you can delete or update rows from more than one table in one statement and can use
those or other tables to decide what rows to change.
Deletion
Imagine you've just run out of disk space or you're sick of browsing unwanted data in
your music collection. One way to solve this problem is to remove some data, and it'd
make sense to remove tracks you've never listened to. Unfortunately, this means you
need to remove data from the
track
table using information from the
played
table.
