SQL keys MUL vs PRI vs UNI

Question

What is the difference between MUL  PRI and UNI in MySQL   I m working on a MySQL query  using the command   desc mytable     One of the fields is shown as being a MUL key  others show up as UNI or PRI   I know that if a key is PRI  only one record per table can be associated with that key  If a key is MUL  does that mean that there could be more than one associated record   Here s the response of mytable    ----------- --------- ------ ----- --------- -------    Field       Type      Null   Key   Default   Extra    ----------- --------- ------ ----- --------- -------    courseid    int 11    YES    MUL   NULL                 dept        char 3    YES          NULL                 coursenum   char 4    YES          NULL                ----------- --------- ------ ----- --------- -------

User · Accepted Answer

It means that the field is  part of  a non-unique index  You can issue   show create table  lt table gt     To see more information about the table structure

User · Answer

For Mul  this was also helpful documentation to me - http   grokbase com t mysql mysql 9987k2ew41 key-field-mul-newbie-question   MUL means that the key allows multiple rows to have the same value  That is  it s not a UNIque key    For example  let s say you have two models  Post and Comment  Post has a has many relationship with Comment  It would make sense then for the Comment table to have a MUL key Post id  because many comments can be attributed to the same Post

User · Answer

UNI  For UNIQUE   It is a set of one or more columns of a table to uniquely identify the record  A table can have multiple UNIQUE key  It is quite like primary key to allow unique values but can accept one null value which primary key does not   PRI  For PRIMARY   It is also a set of one or more columns of a table to uniquely identify the record  A table can have only one PRIMARY key  It is quite like UNIQUE key to allow unique values but does not allow any null value   MUL  For MULTIPLE   It is also a set of one or more columns of a table which does not identify the record uniquely  A table can have more than one MULTIPLE key  It can be created in table on index or foreign key adding  it does not allow null value  It allows duplicate entries in column  If we do not specify MUL column type then it is quite like a normal column but can allow null entries too hence  to restrict such entries we need to specify it  If we add indexes on column or add foreign key then automatically MUL key type added

User · Answer

Walkthough on what is MUL  PRI and UNI in MySQL   From the MySQL 5 7 documentation         If Key is PRI  the column is a PRIMARY KEY or is one of the columns in a multiple-column PRIMARY KEY    If Key is UNI  the column is the first column of a UNIQUE index   A UNIQUE index permits multiple NULL values  but you can tell whether the column permits NULL by checking the Null field     If Key is MUL  the column is the first column of a nonunique index in which multiple occurrences of a given value are permitted within the column       Live Examples  Control group  this example has neither PRI  MUL  nor UNI   mysql gt  create table penguins  foo INT   Query OK  0 rows affected  0 01 sec   mysql gt  desc penguins   ------- --------- ------ ----- --------- -------    Field   Type      Null   Key   Default   Extra    ------- --------- ------ ----- --------- -------    foo     int 11    YES          NULL               ------- --------- ------ ----- --------- -------  1 row in set  0 00 sec    A table with one column and an index on the one column has a MUL   mysql gt  create table penguins  foo INT  index foo    Query OK  0 rows affected  0 01 sec   mysql gt  desc penguins   ------- --------- ------ ----- --------- -------    Field   Type      Null   Key   Default   Extra    ------- --------- ------ ----- --------- -------    foo     int 11    YES    MUL   NULL               ------- --------- ------ ----- --------- -------  1 row in set  0 00 sec    A table with a column that is a primary key has PRI  mysql gt  create table penguins  foo INT primary key   Query OK  0 rows affected  0 02 sec   mysql gt  desc penguins   ------- --------- ------ ----- --------- -------    Field   Type      Null   Key   Default   Extra    ------- --------- ------ ----- --------- -------    foo     int 11    NO     PRI   NULL               ------- --------- ------ ----- --------- -------  1 row in set  0 00 sec    A table with a column that is a unique key has UNI   mysql gt  create table penguins  foo INT unique   Query OK  0 rows affected  0 01 sec   mysql gt  desc penguins   ------- --------- ------ ----- --------- -------    Field   Type      Null   Key   Default   Extra    ------- --------- ------ ----- --------- -------    foo     int 11    YES    UNI   NULL               ------- --------- ------ ----- --------- -------  1 row in set  0 00 sec    A table with an index covering foo and bar has MUL only on foo   mysql gt  create table penguins  foo INT  bar INT  index foo  bar    Query OK  0 rows affected  0 01 sec   mysql gt  desc penguins   ------- --------- ------ ----- --------- -------    Field   Type      Null   Key   Default   Extra    ------- --------- ------ ----- --------- -------    foo     int 11    YES    MUL   NULL                bar     int 11    YES          NULL               ------- --------- ------ ----- --------- -------  2 rows in set  0 00 sec    A table with two separate indexes on two columns has MUL for each one  mysql gt  create table penguins  foo INT  bar int  index foo   index bar    Query OK  0 rows affected  0 01 sec   mysql gt  desc penguins   ------- --------- ------ ----- --------- -------    Field   Type      Null   Key   Default   Extra    ------- --------- ------ ----- --------- -------    foo     int 11    YES    MUL   NULL                bar     int 11    YES    MUL   NULL               ------- --------- ------ ----- --------- -------  2 rows in set  0 00 sec    A table with an Index spanning three columns has MUL on the first   mysql gt  create table penguins  foo INT          bar INT          baz INT          INDEX name  foo  bar  baz    Query OK  0 rows affected  0 01 sec   mysql gt  desc penguins   ------- --------- ------ ----- --------- -------    Field   Type      Null   Key   Default   Extra    ------- --------- ------ ----- --------- -------    foo     int 11    YES    MUL   NULL                bar     int 11    YES          NULL                baz     int 11    YES          NULL               ------- --------- ------ ----- --------- -------  3 rows in set  0 00 sec    A table with a foreign key that references another table s primary key is MUL  mysql gt  create table penguins id int primary key   Query OK  0 rows affected  0 01 sec   mysql gt  create table skipper id int  foreign key id  references penguins id    Query OK  0 rows affected  0 01 sec   mysql gt  desc skipper   ------- --------- ------ ----- --------- -------    Field   Type      Null   Key   Default   Extra    ------- --------- ------ ----- --------- -------    id      int 11    YES    MUL   NULL               ------- --------- ------ ----- --------- -------  1 row in set  0 00 sec   mysql gt  desc penguins   ------- --------- ------ ----- --------- -------    Field   Type      Null   Key   Default   Extra    ------- --------- ------ ----- --------- -------    id      int 11    NO     PRI   NULL               ------- --------- ------ ----- --------- -------  1 row in set  0 00 sec    Stick that in your neocortex and set the dial to  frappe

User · Answer

DESCRIBE  lt table gt      This is acutally a shortcut for   SHOW COLUMNS FROM  lt table gt     In any case  there are three possible values for the  Key  attribute    PRI UNI MUL   The meaning of PRI and UNI are quite clear    PRI    primary key UNI    unique key   The third possibility  MUL   which you asked about  is basically an index that is neither a primary key nor a unique key  The name comes from  multiple  because multiple occurrences of the same value are allowed  Straight from the MySQL documentation      If Key is MUL  the column is the first column of a nonunique index in which multiple occurrences of a given value are permitted within the column    There is also a final caveat      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    As a general note  the MySQL documentation is quite good  When in doubt  check it out

User · Answer

Let s understand in simple words  PRI - It s a primary key  and used to identify records uniquely  UNI - It s a unique key  and also used to identify records uniquely  It looks similar like primary key but a table can have multiple unique keys and unique key can have one null value  on the other hand table can have only one primary key and can t store null as a primary key  MUL - It s doesn t have unique constraint and table can have multiple MUL columns   Note  These keys have more depth as a concept but this is good to start

[mysql] SQL keys, MUL vs PRI vs UNI

Examples related to mysql

Examples related to sql

Examples related to key