By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,709 Members | 2,164 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,709 IT Pros & Developers. It's quick & easy.

Is KEY and INDEX the same thing?

P: n/a
I am following a book on PHP and MySQL and have come across the below SQL
statement.

CREATE TABLE users (
user_id MEDIUMINT(8) UNSIGNED NOT NULL AUTO_INCREMENT,
username VARCHAR(20) NOT NULL,
first_name VARCHAR(15) NOT NULL,
last_name VARCHAR(30) NOT NULL,
email VARCHAR(40) NULL,
password VARCHAR(16) NOT NULL,
registration_DATE DATETIME NOT NULL,
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.

Could someone set me straight on this.

Cheers

Phil
Oct 28 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
Yes, they are the same.

Markus
Oct 28 '05 #2

P: n/a
Phil Latio wrote:
I am following a book on PHP and MySQL and have come across the below SQL
statement.

CREATE TABLE users ( ....
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.


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

P: n/a
> 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.


Thanks very much for your excellent explanation (and to Markus aswell).

I have been searching the web this afternoon for articles relating KEY or
INDEX and nothing compares to above.

Cheers

Phil
Oct 28 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.