Creating Databases and Tables - Learning MySQL and MariaDB - page 60

More Perspectives on Tables
Besides the DESCRIBE statement, there'sanother way to look at how a table is structured.
You can use the SHOW CREATE TABLE statement. Thisbasically shows how you might
enterthe CREATE TABLE to create an existing table, perhaps in a different database.
What's particularly interesting and useful about the SHOW CREATE TABLE statement is
that it shows the default settings assumed by the server, ones that you might not have speci-
fied when you ran the CREATE TABLE statement. Here's how you would enter this state-
ment, with the results shown after it:
SHOW CREATE TABLE birds \G
*************************** 1. row ***************************
Table: birds
Create Table: CREATE TABLE `birds` (
`bird_id` int(11) NOT NULL AUTO_INCREMENT,
`scientific_name` varchar(255) COLLATE latin1_bin DEFAULT NULL,
`common_name` varchar(50) COLLATE latin1_bin DEFAULT NULL,
`family_id` int(11) DEFAULT NULL,
`description` text COLLATE latin1_bin,
PRIMARY KEY (`bird_id`),
UNIQUE KEY `scientific_name` (`scientific_name`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_bin
As mentioned earlier, there are more options that you can set for each column; if you don't
specify them, the server will use the default choices. Here you can see those default set-
tings. Notice that we did not set a default value for any of the fields (except the first one
when we said to use an automatically incremented number), so it set each column to a de-
fault of NULL. For the third column, the common_name column, the server set the set of
characters (i.e., the alphabet, numbers, and other characters) by which it will collate the
data in that column to latin1_bin (i.e., Latin binary characters). The server did the same for
three other columns. That's because of how we set the database at the beginning of this
chapter, in thesecond CREATE DATABASE statement. This is where that comes into play.
We could set a column to a different one from the one we set for the database default, but
it's usually not necessary.
You may have noticed in looking at the results that the options for the bird_id column
don't indicate that it's a primary key, although we specified that in CREATE TABLE . In-
stead, the list of columns is followed by a list of keys or indexes used in the table. Here it
lists the primary key and specifies that that index is based on bird_id . It then shows a
unique key. For that kind of key, it gives a name of the index, scientific_name ,