UNIQUE KEY

[etsted]

how should the UNIQUE KEY syntax look?

 

// is this right?

UNIQUE KEY username (username, email)?

 

// or this??

because i think this is the right syntax, but i have seen some use the above. Why?

UNIQUE KEY(username, email)??

 

Is both legit?

Edited March 28, 2014 by etsted

[Ingolme]

There's a full reference for MySQL syntax here: http://dev.mysql.com/doc/refman/5.1/en/create-table.htmlAccording to it, this syntax is valid:col_name INT UNIQUE KEY

col_name column_definition column_definition:data_type [NOT NULL | NULL] [DEFAULT default_value][AUTO_INCREMENT] [UNIQUE [KEY] | [PRIMARY] KEY][COMMENT 'string'][COLUMN_FORMAT {FIXED|DYNAMIC|DEFAULT}][sTORAGE {DISK|MEMORY|DEFAULT}][reference_definition]

And this syntax is valid as well:col_name INT,UNIQUE KEY index_name (col_name)

col_name column_definition [CONSTRAINT [symbol]] UNIQUE [iNDEX|KEY][index_name] [index_type] (index_col_name,...)[index_option] ...

The square brackets indicate that a symbol is optional. [index_name] can be omitted, resulting in this:

UNIQUE KEY (col_name)

[etsted]

so the line "username" after KEY just points out where mysql should look?

[Ingolme]

It creates an index called "username" which also happens to be the same name as the column.

 

Indexing is a bit complicated, I can't explain it in detail from memory but if you're interested you can look it up. An index is a column you're expecting to use to search for data in the table. Tables are optimized to search for results fastest for columns that are indexed.