Difference between UNIQUE and UNIQUE KEY mySQL

Question

What is the difference between UNIQUE and UNIQUE KEY? Which is better?

For Example:

CREATE TABLE `table1` (
attr1 INT PRIMARY KEY AUTO_INCREMENT,
attr2 INT,
attr3 INT,
attr4 INT,
UNIQUE (attr3,attr4)
);

AND

CREATE TABLE `table1` (
attr1 INT PRIMARY KEY AUTO_INCREMENT,
attr2 INT,
attr3 INT,
attr4 INT,
UNIQUE KEY `constraintName` (attr3,attr4)
);

My question is particularly about usage of UNIQUE and UNIQUE KEY keywords and the difference of results in execution of each.

@VincentOrback I read answer to that question before posting this question. It tells about UNIQUE but not about UNIQUE KEY. — Zohaib Aslam, Apr 28 '16 at 21:05
Ah sorry misread the answer, I think you can find your solution by combining answers from different threads. http://stackoverflow.com/questions/707874/differences-between-index-primary-unique-fulltext-in-mysql, http://stackoverflow.com/questions/10908561/mysql-meaning-of-primary-key-unique-key-and-key-when-used-together, http://dev.mysql.com/doc/refman/5.0/en/create-index.html — Vincent Orback, Apr 28 '16 at 21:10
@VincentOrback I know policies of StackOverflow and I truly respect them. I've spent quite a time in researching before posting this question as I couldn't reach to a satisfactory conclusion. Please suggest some threads where I can find the answer that will be a huge favor. — Zohaib Aslam, Apr 28 '16 at 21:14
Googling isn't research. Did you consider reading the [reference manual](http://dev.mysql.com/doc/refman/5.1/en/create-table.html)? It is the first and indeed the only thing you should have looked at. You don't need Google or StackOverflow to resolve syntax questions. — user207421, Apr 28 '16 at 23:15
@EJP actually reference manual does not suggest which is better in usage, only the people with experience can tell it. I was confused in the usage of these keywords because some people use UNIQUE and some use UNIQUE KEY in same context and so I posted this question. — Zohaib Aslam, Apr 29 '16 at 10:11

score 2 · Accepted Answer · answered Apr 28 '16 at 21:42

UNIQUE and UNIQUE KEY are precisely the same. The KEY operator is a non-essential key word in the syntax definition:

create_definition:
    col_name column_definition
  | [CONSTRAINT [symbol]] PRIMARY KEY [index_type] (index_col_name,...)
      [index_option] ...
  | {INDEX|KEY} [index_name] [index_type] (index_col_name,...)
      [index_option] ...
  | [CONSTRAINT [symbol]] UNIQUE [INDEX|KEY]
      [index_name] [index_type] (index_col_name,...)
      [index_option] ...
  | {FULLTEXT|SPATIAL} [INDEX|KEY] [index_name] (index_col_name,...)
      [index_option] ...
  | [CONSTRAINT [symbol]] FOREIGN KEY
      [index_name] (index_col_name,...) reference_definition
  | CHECK (expr)

See the documentation for further details.

Difference between UNIQUE and UNIQUE KEY mySQL

1 Answers1