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

Databases
1

Hi,

In several examples now I have seen the KEY command in mysql used, for example

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.

2

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

:slight_smile:

3

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.

4

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

5

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.

6

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