What is the difference between UNIQUE, UNIQUE KEY and CONSTRAINT 'name' UNIQUE?

Question

I have a basic users table I want to create in MySQL.

I do not want duplicate emails or duplicate usernames appearing in the database.

What is the best way of preventing this upon table creation?
And what is the difference between the following:

1. UNIQUE (username), UNIQUE (email),

2. UNIQUE KEY (username), UNIQUE KEY (email),

3. CONSTRAINT ucons_login UNIQUE (username, email),

I assume some of these are synonymous, yet I've been reading conflicting information online and was seeking confirmation.

I hope someone can assist.

The SQL:

CREATE TABLE users (
  user_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  username VARCHAR(30) NOT NULL,
  pass CHAR(40) NOT NULL,
  first_name VARCHAR(20) NOT NULL,
  last_name VARCHAR(40) NOT NULL,
  email VARCHAR(60) NOT NULL,
  registration_date DATETIME NOT NULL,
  user_level TINYINT(1) UNSIGNED NOT NULL DEFAULT 0,
  active CHAR(32),
  PRIMARY KEY (user_id),
  UNIQUE (username),
  UNIQUE (email),
  INDEX login (email, pass),
  INDEX full_name (last_name, first_name)
) ENGINE=INNODB;

ps: the index for email,pass will probably only make things slower. The unique constraint on email can work as a normal index as well. Selects on email will return at most 1 row so there is no need to also index on pass. — Erik Ekman, Feb 08 '12 at 21:38

score 24 · Accepted Answer · answered Feb 08 '12 at 21:25

1 and 2 are identical - both create two unique indexes, one for each key. #3 only creates one unique index across both keys, so no combination of username and email can be duplicated, but for example, a username could be duplicated as long as a different email was used.

Sounds like you probably want either of the first two. UNIQUE and UNIQUE KEY are equivalent.

score 4 · Answer 2 · edited Sep 04 '19 at 15:33

4

They are all synonymous as evidenced by syntax documentation:

[CONSTRAINT [symbol]] UNIQUE [INDEX|KEY]
      [index_name] [index_type] (index_col_name,...)
      [index_option]

[] in this notation (Wirth's notation) denote optional elements

edited Sep 04 '19 at 15:33

Yves M.

29,855
23
108
144

answered Feb 08 '12 at 21:25

Mchl

61,444
9
118
120

3

The third one is a complex key across multiple fields, as opposed to two distinct keys. – kitti Feb 08 '12 at 21:26
Indeed... I didn't notice that in the question. – Mchl Feb 08 '12 at 21:27
1

Thanks for the link the MySQL create table syntax is starting to make more sense now. – leokennedy Feb 08 '12 at 21:32

What is the difference between UNIQUE, UNIQUE KEY and CONSTRAINT 'name' UNIQUE?

2 Answers2

Linked