Mysql Table Key

Question

Can anyone tell me what is the meaning of KEY fk_pickup_method (pickup_method_id), KEY fk_deliv_method (delivery_method_id) lines. As pickup_method and deliv_method are not the tables. So what is the use of these lines.

CREATE TABLE `test` (
`idTest` int(11) NOT NULL AUTO_INCREMENT,
`Name` mediumtext NOT NULL,
`email` varchar(45) NOT NULL,
`password` varchar(45) NOT NULL,
`firstname` varchar(45) NOT NULL,
`lastname` varchar(45) NOT NULL,
`phone` bigint(20) unsigned NOT NULL,
`address_street` varchar(128) NOT NULL,
`address_apt` varchar(45) DEFAULT NULL,
`address_city` varchar(128) NOT NULL,
`address_state` varchar(2) NOT NULL,
`address_zip` int(11) NOT NULL,
`fax` bigint(20) unsigned DEFAULT NULL,
`account_balance` float NOT NULL DEFAULT '0',
`delivery_radius` float DEFAULT NULL,
`pickup_method_id` int(11) NOT NULL DEFAULT '0',
`delivery_method_id` int(11) NOT NULL,
`is_deleted` tinyint(1) NOT NULL DEFAULT '0',
`lat` float NOT NULL DEFAULT '0',
`lng` float NOT NULL DEFAULT '0',
`timezone` varchar(45) NOT NULL,
PRIMARY KEY (`idTest`),
UNIQUE KEY `phone_UNIQUE` (`phone`),
KEY `fk_pickup_method` (`pickup_method_id`),
KEY `fk_deliv_method` (`delivery_method_id`)
)

Please remove the `java` tag as this does not seem to relate to Java... — home, Dec 09 '11 at 13:13
@vikasTheJavaDeveloper: Does this answer your question? http://stackoverflow.com/questions/924265/mysqls-key-keyword — home, Dec 09 '11 at 13:16

score 4 · Answer 1 · answered Dec 09 '11 at 13:15

4

They're INDEXes on the columns in the ()'s. But the fields are not constrained to being UNIQUE.

Look for {INDEX|KEY} in this MySQL document link.

answered Dec 09 '11 at 13:15

MatBailie

83,401
18
103
137

1

Note: As the NAMEs includes `fk_` they may be INTENDED to be FOREIGN KEYS, but they've been created as INDEXes. – MatBailie Dec 09 '11 at 13:21

score 3 · Accepted Answer · answered Dec 09 '11 at 13:21

Line KEY fk_pickup_method (pickup_method_id) defines an index named fk_pickup_method on table column pickup_method_id.

When you run EXPLAIN, under possible_keys column you'll see the name of the index.

The usual practice is to call the key as the column it indexes. That's the default behaviour if you don't specify the key name.

score 2 · Answer 3 · answered Dec 09 '11 at 13:20

These are indexes on the fields pickup_method_id and delivery_method_id.

How mysql uses indexes : http://dev.mysql.com/doc/refman/5.0/en/mysql-indexes.html

The name fk_ points to the intention of the creator to create a foreign key. But this is not a foreign key, and won't care about referential integrity for you.

Foreign keys in mysql (innodb) : http://dev.mysql.com/doc/refman/5.5/en/innodb-foreign-key-constraints.html

score 1 · Answer 4 · answered Dec 09 '11 at 13:40

My guess is that these were intended to be FOREIGN KEY constraints and reference other tables.

InnoDB engine creates an index (if there isn't one) when a FOREIGN KEY constraint is defined.

MyISAM engine ignores FOREIGN KEY constraints but it still creates the index. Example:

CREATE TABLE test 
( test_id int NOT NULL AUTO_INCREMENT,
  delivery_method_id int NOT NULL,
  PRIMARY KEY (test_id),
  FOREIGN KEY fk_deliv_method (delivery_method_id)
    REFERENCES delivery_method(delivery_method_id)
) ENGINE=MyISAM 
  DEFAULT CHARSET=utf8 ;

And then:

SHOW CREATE TABLE test ;

CREATE TABLE `test` (
  `test_id` int(11) NOT NULL AUTO_INCREMENT,
  `delivery_method_id` int(11) NOT NULL,
  PRIMARY KEY (`test_id`),
  KEY `fk_deliv_method` (`delivery_method_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8

Mysql Table Key

4 Answers4