SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Member
    Join Date
    Apr 2012
    Posts
    3
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,251
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    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

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Member
    Join Date
    Apr 2012
    Posts
    3
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,251
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Member
    Join Date
    Apr 2012
    Posts
    3
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,251
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Dr Dave View Post
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •