MySQL table column description help needed

CREATE TABLE I开发者_运维技巧F NOT EXISTS `users` (
  `id` int(11) NOT NULL auto_increment,
  `username` varchar(50) NOT NULL,
  `status` varchar(20) NOT NULL,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `username` (`username`)
)

Please explain me what is doing UNIQUE KEY 'username' (username') statement in the example above and why ('username') is written once again?

---

The UNIQUE_KEY line is creating a unique index called 'username' on the column 'username'. A unique index allows only a single record to have a specific value. This is useful on rows like usernames because it prevents two users from being created with the same username.

However, I think you would get an error if you ran this because you have not defined a column called username.

CREATE TABLE IF NOT EXISTS `users` (
  `id` int(11) NOT NULL auto_increment,
  `username` varchar(50) NOT NULL,
  `status` varchar(20) NOT NULL,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `username` (`username`)
)

---

This is simply the format MySQL's CREATE TABLE syntax expects. If you look at the syntax in detail, you'll see...

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

In other words, you're using an index_name of "username" which uses the "username" field/column. This might seem odd, but if you were using a compound key, you'd might have a definition something like...

UNIQUE KEY duplicate_lock (user_email, user_name)

...so you'd have different index name and column portions of the definition.