3

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.

4

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. :)

|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.