Database Reference
In-Depth Information
do this, you would still use the
CREATE TABLE
statement, but with a slightly different
syntax.
Let's suppose that we have decided that we want to create a new table for details about
each bird (e.g., migratory patterns, habitats, etc.). Looking at the
birds
table, though, we
decide that the
description
column and its data belong in this new table. So we'll cre-
ate a new table and copy that column's settings and data, as well as the
bird_id
into the
new table. We can do that by entering the following from
mysql
to get the table started:
CREATE TABLE
birds_details
SELECT
bird_id
,
description
FROM
birds
;
This creates the
birds_details
table with two columns, based on the same columns
in the
birds
table. It also copies the data from the two columns in the
birds
table into
the
birds_details
table. There is one minor, but necessary, difference in one of the
columns in the new table. The difference has to do with
AUTO_INCREMENT
again, but
not in the same way as earlier examples. Enter the
DESCRIBE
statement to see the differ-
ence:
DESCRIBE birds_details;
+-------------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+---------+------+-----+---------+-------+
| bird_id | int(11) | NO | | 0 | |
| description | text | YES | | NULL | |
+-------------+---------+------+-----+---------+-------+
The difference here is that the
bird_id
does not use
AUTO_INCREMENT
. This is good
because we have to manually set the value of the
bird_id
for each row that we enter.
We won't have details for each bird, though, and we won't necessarily be entering them in
the same order as we will in the
birds
table. We could change the
bird_id
column in
this table to an
AUTO_INCREMENT
column, but that would cause problems — trying to
keep it in line with the
birds
table would be maddening. We could, however, make an
index for the
bird_id
column in the
birds_details
table by using the
ALTER
TABLE
statement and setting the column to a
UNIQUE
key. That would allow only one
entry per bird, which may be a good idea. This is covered in
Indexes
.
The
CREATE TABLE...SELECT
statementcreated the
birds_details
table with
only two columns. We said, though, that we want more columns for keeping other inform-
ation on birds. We'll add those additional columns later with the
ALTER TABLE
state-
