DESC command and key column within it

Question

I have created a table in my database and added a few constraints. When i use the DESC keyword on my table, depending on what constraints I placed, the Key column changes its record.

Here's the table definition:

CREATE TABLE t(
 sif  INT,
 sif2 INT NOT NULL,
 sif3 INT          UNIQUE,
 sif4 INT NOT NULL UNIQUE
);

and Here's the DESC result:


    mysql> desc t;
    +-------+---------+------+-----+---------+-------+
    | Field | Type    | Null | Key | Default | Extra |
    +-------+---------+------+-----+---------+-------+
    | sif   | int(11) | YES  |     | NULL    |       |
    | sif2  | int(11) | NO   |     | NULL    |       |
    | sif3  | int(11) | YES  | UNI | NULL    |       |
    | sif4  | int(11) | NO   | PRI | NULL    |       |
    +-------+---------+------+-----+---------+-------+
    4 rows in set (0.01 sec)

Why did my column sif4 get the key value PRI? I never mentioned a primary key definition and yet the DESC keyword shows that the column is set up as a primary key.

Robby Cornelissen Robby Cornelissen · Accepted Answer · 2014-07-06T08:09:45

DESC t or DESCRIBE t is equivalent to SHOW COLUMNS FROM t. In the output from the SHOW COLUMNS command, a unique index may be displayed as PRI if it cannot contain null values and there's no primary key in the table.

In your case, the sif4 column qualifies, as it's declared as

sif4 INT NOT NULL UNIQUE

From the documentation:

A UNIQUE index may be displayed as PRI if it cannot contain NULL values and there is no PRIMARY KEY in the table. A UNIQUE index may display as MUL if several columns form a composite UNIQUE index; although the combination of the columns is unique, each column can still hold multiple occurrences of a given value.

DESC command and key column within it

1 Answers