the use of KEY in a CREATE TABLE instruction

Discussion in 'MySQL' started by barricades, Mar 10, 2011.

0
  1. #1
    Hi there, I've been following a tutorial and in it a Categories table has to be created, the tutorial gives the following sql

    CREATE TABLE IF NOT EXISTS 'categories' (
    'id' int (10) unsigned NOT NULL auto_increment,
    'parent_id' int(11) NOT NULL default '0',
    'name' varchar(50) character NOT NULL,
    'description' varchar(200) character NOT NULL,
    PRIMARY KEY ('id'),
    KEY 'cat_parent_id' ('parent_id'),
    KEY 'cat_name' ('name')
    );

    The last two lines (eg KEY 'cat_name' ('name')) I'm not too sure about what that means. I've googled and only found out that it might have something to do with indexes. From what I've read I understand indexes (sort of) but I can't find anywhere which uses that KEY instruction when creating a table. Any other article I find about indexes uses something like: CREATE INDEX IDX_CUSTOMER_LAST_NAME
    on CUSTOMER (Last_Name)

    Can anyone explain what exactly KEY does, and what the 'cat_name' ('name') beside it means, is cat_name and alias? Obviuosly 'name' is the column in the table that the instruction is dealing with.

    confused...
     
    barricades, Mar 10, 2011 IP
  2. AstarothSolutions

    AstarothSolutions Peon

    Messages:
    2,680
    Likes Received:
    77
    Best Answers:
    0
    Trophy Points:
    0
    #2
    AstarothSolutions, Mar 10, 2011 IP
  3. jkl6

    jkl6 Peon

    Messages:
    70
    Likes Received:
    1
    Best Answers:
    0
    Trophy Points:
    0
    #3
    KEY and INDEX are interchangeable keywords, they mean the same thing.
    KEY 'cat_parent_id' ('parent_id') = Create an index on the 'parent_id' column. Name this index 'cat_parent_id'.
    KEY 'cat_name' ('name') = Create an index on the 'name' column named. Name this index 'cat_name'.

    Here's the syntax for the create statement, look for {INDEX|KEY} in the text: http://dev.mysql.com/doc/refman/5.1/en/create-table.html

    This explains what an index is for: http://dev.mysql.com/doc/refman/5.0/en/mysql-indexes.html
     
    jkl6, Mar 10, 2011 IP