gotchasqlMinor
What is the difference between uniqe index/unique/unique key/unique constraint keywords?
Viewed 0 times
uniqueuniqethewhatdifferencebetweenkeywordsconstraintindexkey
Problem
I am using mysql 5.5.16
What is the difference between the following DDL statements?
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.
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.
Solution
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,
Just pick the name you prefer for the unique key and feel free to use any of your queries. :)
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. :)
Context
StackExchange Database Administrators Q#63211, answer score: 7
Revisions (0)
No revisions yet.