About INDEX on FOREIGN KEY .........

[whisher06]

Hi anyOne.
I'd like to know your opinion
about using INDEX on FOREIGN
KEY like this:

CREATE TABLE `_categories` (
  `category_id` smallint(5) unsigned NOT NULL auto_increment,
  `category_name` varchar(150) NOT NULL default '',
  PRIMARY KEY  (`category_id`),
  UNIQUE KEY `category_name` (`category_name`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
CREATE TABLE `_forums` (
`forum_id` smallint( 5 ) unsigned NOT NULL AUTO_INCREMENT ,
`category_id` smallint( 5 ) unsigned NOT NULL default '0',
`forum_name` varchar( 150 ) NOT NULL default '',
`forum_desc` text NOT NULL ,
`forum_time` int( 10 ) unsigned NOT NULL default '0',
`forum_count_post` mediumint( 8 ) unsigned NOT NULL default '0',
PRIMARY KEY ( `forum_id` ) ,
INDEX ( `category_id` ) ,
UNIQUE KEY `forum_name` ( `forum_name` )
) ENGINE = MYISAM DEFAULT CHARSET = latin1

and what's the difference between
INDEX and KEY ?
Take care.

[whisher06]

In the referencing table, there must be an index where the foreign key columns are listed as the first columns in the same order. Starting with MySQL/InnoDB 4.1.2, such an index will be created on the referencing table automatically if it does not exist.

In the referenced table, there must be an index where the referenced columns are listed as the first columns in the same order.

It isn't a opinion ! 🙂
Take care.