CODEWITHSUNDEEP
mysqlsqldatabase
vikas27
vikas27

Reputation: 573

Mysql Table Key

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`)
)

Upvotes: 0

Views: 655

Answers (4)

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115630

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

Upvotes: 1

N.B.
N.B.

Reputation: 14091

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.

Upvotes: 3

Don
Don

Reputation: 1144

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

Upvotes: 2

MatBailie
MatBailie

Reputation: 86775

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.

Upvotes: 4

Related Questions