Tuesday, April 23, 2013

Difference between PRIMARY KEY, UNIQUE KEY and key in MySQL



Primary key, unique key and key in MySQL are used as index to speed up search process.
Primary key and  unique key can not have same values for two distinct rows, while key will not require this. There is only one primary key in one table, but can have  several unique keys.
Example:
CREATE TABLE  member` (
  `member_id` mediumint(8) unsigned NOT NULL auto_increment,
  `login` varchar(20) NOT NULL default '',
  `password` varchar(20) NOT NULL default '',
  `email` varchar(50) NOT NULL default '',
  `country` varchar(50) NOT NULL default '',
  `phone` varchar(15) NOT NULL default '',
  `extension` varchar(10) NOT NULL,
  `creation_date` datetime NOT NULL default '0000-00-00 00:00:00',
  `membership_type` tinyint(4) NOT NULL default '0',
  `last_login` datetime NOT NULL default '0000-00-00 00:00:00',
  `account_control` tinyint(4) NOT NULL default '1',
  `dept_id` mediumint(8) unsigned NOT NULL default '0',
  `organization` varchar(255) NOT NULL,
  `site` varchar(255) NOT NULL,
  `unit` varchar(255) NOT NULL,
  `manager` varchar(255) NOT NULL,
  `language` varchar(5) NOT NULL default 'en',
  `preferences` text NOT NULL,
  PRIMARY KEY  (`member_id`),
  UNIQUE KEY `login` (`login`),

  KEY `email` (`email`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8;


PRIMARY KEY  (`member_id`): index name and kind are primary, using column member_id
UNIQUE KEY `login` (`login`): index name login and kind unique using column login
KEY `email` (`email`) index name email, index kind: index, using column email.

No comments:

Post a Comment