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 (example):
mysql> DESCRIBE City; +-------------+----------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------------+----------+------+-----+---------+----------------+ | ID | int(11) | NO | PRI | NULL | auto_increment | | Name | char(35) | NO | | | | | CountryCode | char(3) | NO | | | | | District | char(20) | NO | | | | | Population | int(11) | NO | | 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 column name.The
Type
value shows the column data type.The
Null
indicator is the wordYES
if the column can containNULL
values andNO
if it cannot. In the example shown,Null
isNO
for all columns of theCity
table. This indicates that none of that table's columns can containNULL
values.The
Key
indicator may be empty or contain one of three non-empty 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, non-unique index. For purposes of the exam, you should assume that ifKey
is empty, it's because the column is not indexed at all.If the
Key
value is the keywordPRI
(as in the output shown for theID
column), this indicates that the column is aPRIMARY KEY
or is one of the columns in a multiple-columnPRIMARY KEY
.If the
Key
value is the keywordUNI
, this indicates that the column is the first column of a unique-valued index that cannot containNULL
values.If the
Key
value is the keywordMUL
, this indicates that the column is the first column of a non-unique index or a unique-valued index that can containNULL
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 aPRIMARY KEY
might also be part of other indexes. When it's possible for more than one of theKey
values to describe an index,DESCRIBE
displays the one with the highest priority, in the orderPRI
,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 theINSERT
,REPLACE
, andLOAD DATA INFILE
statements.)The
ExTRa
value displays other details about the column. The onlyExTRa
detail about which you need be concerned for the exam is the valueauto_increment
. This value indicates that the column has theAUTO_INCREMENT
attribute. TheID
column shown in the example is such an instance.