MySQL Unique Constraints – Difference Between Unique Index, Unique Key, and Unique Constraint

MySQLmysql-5.5

I am using mysql 5.5.16

What is the difference between the following DDL statements?

1)
create table sql_query (
    id int not null
    , query_text varchar(21826) not null
    , query_md5 binary(16) not null
    , primary key (id)
    , unique index query_md5_index (query_md5)
) engine=InnoDB;

2) 
create table sql_query (
    id int not null
    , query_text varchar(21826) not null
    , query_md5 binary(16) not null
    , primary key (id)
    , unique query_md5_index (query_md5)
) engine=InnoDB;

3)
create table sql_query (
    id int not null
    , query_text varchar(21826) not null
    , query_md5 binary(16) not null
    , primary key (id)
    , constraint md5_constraint unique (query_md5)
) engine=InnoDB;

4)
create table sql_query (
    id int not null
    , query_text varchar(21826) not null
    , query_md5 binary(16) not null
    , primary key (id)
    , unique key query_md5_key (query_md5)
) engine=InnoDB;

Which one should I use? I want to ensure that the there are no rows with the same value for query_md5 and also want to create an index on that column so that searches on that column are fast.

I read the manual at https://dev.mysql.com/doc/refman/5.6/en/innodb-storage-engine.html but it is not very helpful.

Best Answer

There are no differences between all the SQL statements, except the third one you're using a different name for the unique index, that's all.

All of them creates a unique key/index, constraint md5_constraint unique (query_md5) has the same effect.

Just pick the name you prefer for the unique key and feel free to use any of your queries. :)

Related Question