Interpreting DESCRIBE Output

You should understand how to interpret the output of the DESCRIBE table_name statement. This is of particular importance both for this study guide and for taking certification exams. In both cases, when it's necessary that you know the structure of a table, it will be shown as the output of a DESCRIBE statement in the same format as that displayed by the mysql program. For example, assume that a question requires you to know about a table named City. The table's structure will be presented as follows:

mysql> DESCRIBE City;

+-------------+----------+------+-----+---------+----------------+

| Field       | Type     | Null | Key | Default | Extra          |

+-------------+----------+------+-----+---------+----------------+

| ID          | int(11)  |      | PRI | NULL    | auto_increment |

| Name        | char(35) |      |     |         |                |

| CountryCode | char(3)  |      |     |         |                |

| District    | char(20) |      |     |         |                |

| Population  | int(11)  |      |     | 0       |                |

+-------------+----------+------+-----+---------+----------------+

The output of the DESCRIBE statement contains one row for each column in the table. The most important features of the output are as follows:

• The Field value indicates the name of the column.

• The Type value shows the column datatype.

• The Null indicator is the word YES if the column can contain NULL values and is empty if it cannot. In the example shown, Null is empty for all columns of the City table. This indicates that none of that table's columns can contain NULL values.

• The Key indicator may be empty or contain one of three nonempty values:

  • An empty Key value indicates that the column in question either isn't indexed or is indexed only as a secondary column in a multiple-column, nonunique index. For purposes of the exam, you should assume that if Key is empty, it's because the column is not indexed at all.

  • If the Key value is the keyword PRI (as in the output shown for the ID column), this indicates that the column is a PRIMARY KEY or is one of the columns in a multiple-column PRIMARY KEY.

  • If the Key value is the keyword UNI, this indicates that the column is the first column of a unique-valued index that cannot contain NULL values.

  • If the Key value is the keyword MUL, this indicates that the column is the first column of a nonunique index or a unique-valued index that can contain NULL values.

  It's possible that more than one of the Key values may apply to a given column of a table. For example, a column that is a PRIMARY KEY might also be part of other indexes. When it's possible for more than one of the Key values to describe an index, DESCRIBE displays the one with the highest priority, in the order PRI, UNI, MUL.

  Because a column can be part of several indexes, the Key values do not necessarily provide an exhaustive description of a table's indexes. However, for purposes of the exam, you should assume that the table descriptions given provide all the information needed to correctly answer the question.

• Default shows the column's default value. This is the value that MySQL assigns to the column when a statement that creates a new record does not provide an explicit value for the column. (For example, this can happen with the INSERT, REPLACE, and LOAD DATA INFILE statements.)

• The Extra value displays other details about the column. The only Extra detail about which you need be concerned for the exam is the value auto_increment. This value indicates that the column has the AUTO_INCREMENT attribute. (The ID column shown in the example is such an instance.)

You can read more about indexing, column datatypes, default values, and the AUTO_INCREMENT column option in Chapter 4, "Data Definition Language."