Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2006

    Unanswered: MySQL keys question

    Can anyone explain to me, what key types can I use when creating tables and how to implement those (different keywords, different coding styles).

    For example:

    What exactly do last 3 lines (
    PRIMARY KEY (`block_ID`),
    UNIQUE KEY `block_ID` (`block_ID`),
    KEY `block_ID_2` (`block_ID`) )

    do in this example:

    CREATE TABLE `blocks` (
    `block_ID` int(11) NOT NULL auto_increment,
    `block_title` varchar(255) default 'Block Title',
    `block_file` varchar(255) default '0',
    `plugin_ID` int(11) default '0',
    `group_ID` int(11) default '0',
    `site_ID` int(11) default NULL,
    `mod_ID` int(11) default NULL,
    PRIMARY KEY (`block_ID`),
    UNIQUE KEY `block_ID` (`block_ID`),
    KEY `block_ID_2` (`block_ID`)
    ) TYPE=MyISAM;

    and why are they needed? (especially lines: UNIQUE KEY `block_ID` (`block_ID`), and
    KEY `block_ID_2` (`block_ID`)) .

    I kind of understand primary key thing ( PRIMARY KEY (`block_ID`),) and maybe UNIQUE KEY `block_ID` (`block_ID`) also (though I don't understand why `block_ID` (`block_ID`) is needed, why not just UNIQUE KEY (`block_ID`), ), but why would one use KEY `block_ID_2` (`block_ID`)? What does one achieve with this KEY.... line? And are there any alternatives to this approach for implementing the same thing (that I would undestand better ) ?

    O, did I mention that I'm new to DB and MySQL (as my noob questions don't reflect that ).

    Could some one please shed some light on this key usage in MySQL or at least point me to some good source on web, where this things are well explained.

    Last edited by misc; 02-07-06 at 15:58.

  2. #2
    Join Date
    Jun 2005
    Since block_ID is defined as a primary key you do not need to declare it as unique. It is automatically made unique. Additionally you do not need the key declaration.
    When you define a column as a primary key it means that it is automatically indexed and made unique

  3. #3
    Join Date
    Feb 2006
    Thanx for the efort and reply.

    So to clear things up:

    INDEX 'block_ID_2' ('block_ID') [or INDEX ('block_ID') at least], would be equal to KEY 'block_ID_2' ('block_ID') then?
    If so, do you know why two different keyword [INDEX and KEY; legacy of previos versions maybe?].

    Also, do you have any idea why would one use UNIQE KEY and KEY in such fashion if PRIMARY KEY does that [good programming practice, maybe?]. I'm asking this because I saw some examples of CREATE TABLE, that use same syntax.

    And oh, one more thing, when I use KEY 'block_ID_2' ('block_ID') like this, I create index for block_ID field and name this index block_ID_2, right? Or am I totally wrong?
    Last edited by misc; 02-13-06 at 09:23.

Posting Permissions

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