Grateful Grateful - 1 year ago 84
SQL Question

Why is the `MUL` keyword showing up under the `Key` column for the `show columns from Employee` mysql command?

When I execute

show columns from Employee
in MySQL for the following tables ...

CREATE TABLE Employee (
id MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
departmentId TINYINT UNSIGNED NOT NULL
COMMENT "CONSTRAINT FOREIGN KEY (departmentId) REFERENCES Department(id)",
firstName VARCHAR(20) NOT NULL,
lastName VARCHAR(40) NOT NULL,
email VARCHAR(60) NOT NULL,
ext SMALLINT UNSIGNED NULL,
hireDate TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
leaveDate DATETIME NULL,
INDEX name (lastName, firstName),
INDEX (departmentId)
)

CREATE TABLE Department (
id TINYINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(40),
UNIQUE (name)
)


... why is the keyword
MUL
appearing in the result set for the column
lastName
(as well as for
departmentId
) and not for
firstName
?

Answer Source

MUL as opposed to PRI and UNI means a non unique index and is shown for the first (or the only column) in an index when you issue a DESCRIBE or SHOW COLUMNS command.

The only exception to this is

If more than one of the Key values applies to a given column of a table, Key displays the one with the highest priority, in the order PRI, UNI, MUL.

firstName is a second column in the composite index name and therefore MUL is not being displayed for it.

Further reading SHOW COLUMNS Syntax

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download