mysql index and key same thing?

mysql index and key same thing?

http://bytes.com/topic/mysql/answers/169585-key-index-same-thing

Markus Popp

Yes, they are the same.

Markus

Oct 28 '05 #2

Bill Karwin

Phil Latio wrote:[color=blue]
> I am following a book on PHP and MySQL and have come across the below SQL
> statement.
>
> CREATE TABLE users ([/color]
....
[color=blue]
> PRIMARY KEY (user_id),
> UNIQUE KEY username (username),
> KEY first_name (first_name),
> KEY last_name (last_name),
> KEY password (password),
> );
>
> The author seems to switch from using INDEX to KEY in different chapters
> without documenting why but they appear to do the same thing.[/color]

The syntax is a bit inconsistent, in my opinion, but in some
circumstances, the INDEX and KEY keywords can be used interchangeably.
In other circumstances, either INDEX or KEY is the only word that works.

For example:

legal: PRIMARY KEY
not legal: PRIMARY INDEX

legal: FOREIGN KEY
not legal: FOREIGN INDEX

legal: UNIQUE KEY
legal: UNIQUE INDEX

legal: CREATE INDEX ...
not legal: CREATE KEY ...

Anyway, it's a bit confusing. Refer to the reference documentation if
in doubt about the syntax:
http://dev.mysql.com/doc/refman/5.0/...ate-table.html

Also, strictly speaking, "key" is a logical concept in relational
databases; it's a column or set of columns that identifies a record.
"Index" is a physical entity in a database that is frequently used to
improve performance, enforce uniqueness efficiently, perform quick
lookups, etc. It is not logically related to the concept of a key, but
they are often used together. An RDBMS could support keys without using
indexes, but it would work very slowly. For that reason, in many RDBMS
implementations, declaring a column as a key implicitly creates an index.

Regards,
Bill K.

Oct 28 '05 #3

http://dev.mysql.com/doc/refman/5.0/en/create-table.html

• KEY is normally a synonym for INDEX. The key attribute PRIMARY KEY can also be specified as just KEY when given in a column definition. This was implemented for compatibility with other database systems.
• A UNIQUE index creates a constraint such that all values in the index must be distinct. An error occurs if you try to add a new row with a key value that matches an existing row. This constraint does not apply to NULL values except for the BDB storage engine. For other engines, a UNIQUE index permits multiple NULL values for columns that can contain NULL.