Do you have a question? Post it now! No Registration Necessary. Now with pictures!
- Posted on
- Is KEY and INDEX the same thing?
- Phil Latio
October 28, 2005, 5:24 pm
rate this thread
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.
Re: Is KEY and INDEX 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.
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:
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.
- » Americanas.com SALDO de TVs Com at 80% de Desconto (29798)
- — Newest thread in » MySQL Database Forum