What is the KEY declaration in setting up foreign keys in the base table?

[Dr Dave]

Hi,

In several examples now I have seen the KEY command in mysql used, for example
http://www.sitepoint.com/mysql-forei...e-development/

I have searched in the mysql manual but I can't seem to find it. It is driving me nuts... can you help me understand what it is, what it does and the syntax to use with it?

Thanks.

[r937]

two things to (try to) remember --

first, KEY is totaly different from PRIMARY KEY and FOREIGN KEY

second, KEY is a (non-standard) mysql synonym for INDEX

you will find KEY mentioned in da manual under CREATE TABLE

[Dr Dave]

Thanks, that was great. I found

<snip>
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.
</snip>

So if it is non-standard, why have I seen in in two places people are describing how to set up mysql databases? One of them as mentioned above on Sitepoint? I am still curious.

[r937]

standard sql and mysql sql are different, that's all

mysql supports most of standard sql (not all), and also has its own proprietary extensions to standard sql

[Dr Dave]

Ah.

With mysql can I do something to add a 'KEY' to an existing table? Like...

UPDATE TABLE MyTable SET KEY 'author_id' ('author_id');

Or will that do something harmful to MyTable?

Thanks again.

[r937]

UPDATE TABLE MyTable SET KEY 'author_id' ('author_id');

that's close, you got the idea, except for the correct syntax

ALTER TABLE MyTable ADD KEY author_id (author_id);

notice no quotes around the index name or column name