Creating Tables with Queries | 287

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 (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) | YES | | NULL | |
+-------------+-------------+------+-----+---------+-------+
2 rows inset 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) 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 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.

Download 4.24 Mb.

Share with your friends: