Altering Tables - Learning MySQL and MariaDB - page 71

bird_orders table. You don't want automatic increments for the column being added
to bird_families , because you're just referring to existing orders, not adding new
ones.
As another example of this clause, let's add a couple of columns to the birds table to be
able to join it to the two tables you should have created in the exercises at the end of
Chapter4 (i.e., birds_wing_shapes and birds_body_shapes ). Before we do
that, let's make a copy of the table and alter the copy instead of the original. When we're
finished, we'll use the table we altered to replace the original table.
To make a copy of the birds table, we'll usethe CREATE TABLE statement with the
LIKE clause. This was covered in Chapter4 ) In fact, let's create the new table in the
test database just to work separately on it (this isn't necessary, but it's a good practice to
have a development database separate from the live one. To do this, enter the following in
mysql on your server:
CREATE TABLE test . birds_new LIKE birds ;
Next, enter the following two lines in mysql to switch the default database of the client
and to see how the new table looks:
USE test
DESCRIBE birds_new ;
This DESCRIBE statementwill show you the structure of the new table. Because we
copied only the structure of the birds table when we created the new table, there is no
data in this table. To do that, we could use an INSERT statementcoupled with a SELECT
like so:
INSERT INTO birds_new
SELECT * FROM rookery . birds ;
This will work fine. However, there's another method that creates a table based on another
table and copies over the data in the process:
CREATE TABLE birds_new_alternative
SELECT * FROM rookery . birds ;
This will create the table birds_new_alternative with the data stored in it.
However, if you execute a DESCRIBE statement for the table, you will see that it did not
set the bird_id column to a PRIMARY KEY and did not set it to AUTO_INCREMENT .
So in our situation, the first method we used to create the table is preferred, followed by