Database Reference
In-Depth Information
This will cause the
bird_id
to be set to 10 for the next row of data on a bird that we
enter into the
birds
table. Changing the auto-increment value is not usually necessary,
but it's good to know that you can do even this with
ALTER TABLE
.
Another Method to Alter and Create a Table
There may be times when yourealize that you've created a table that is too wide, with too
many columns. Perhaps some columns would be handled better in a separate table. Or
perhaps you started adding new columns to an existing table and found it became unruly
over time. In either case, you could create a smaller table and then move data from the lar-
ger table into the new, smaller one. To do this, you can create a new table with the same
settings for the columns you want to move, then copy the data from the first table to the
new table, and then delete the columns you no longer need from the first table. If you
wanted to make this transition by the method just described, the individual column set-
tings will need to be same in the new table to prevent problems or loss of data.
An easier method for creating atable based on another table is to use the
CREATE
TABLE
with the
LIKE
clause. Let's try that to create a copy of the
birds
table. Enter the
following in
mysql
on your server:
CREATE TABLE
birds_new
LIKE
birds
;
This creates an identical table like the
birds
table, but with the name
birds_new
. If
you enter the
SHOW TABLES
statement in
mysql
, you will see that you now have a
birds
table and a new table,
birds_new
.
NOTE
You can use an underscore (i.e.,
_
) in atable name, but you may want to avoid using hyphens. MySQL
interprets a hyphen as a minus sign and tries to do a calculation between the two words given, which
causes an error. If you want to use a hyphen, you must always reference the table name within quotes.
Execute the following three SQL statements to see what you now have:
DESCRIBE birds;
DESCRIBE birds_new;
SELECT * FROM birds_new;
Empty set (0.00 sec)
