I couldn't help but notice you have individual indexes on nearly all the
fields of your table.  If you won't be using these fields exclusively as a
join or filter condition in a query, you are unlikely to benefit from the
extra indexes and, in fact, they could slow down your inserts and add to
your storage requirements.

-Travis

-----Original Message-----
From: Tompkins Neil [mailto:neil.tompk...@googlemail.com] 
Sent: Wednesday, October 13, 2010 8:37 AM
To: [MySQL]
Subject: Primary key not unique on InnoDB table

I've the following table.  But why isn't the primary key unique, e.g.
preventing duplicates if entered ?

CREATE TABLE `players_master` (

  `players_id` bigint(20) NOT NULL AUTO_INCREMENT,
  `default_teams_id` bigint(20) NOT NULL,
  `first_name` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
  `second_name` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
  `known_as` varchar(200) COLLATE utf8_unicode_ci DEFAULT NULL,
  `dob` date NOT NULL,
  `countries_id` char(2) COLLATE utf8_unicode_ci NOT NULL,
  `retirement_date` date DEFAULT NULL,
  `positions_id` varchar(3) COLLATE utf8_unicode_ci NOT NULL,
  `estimated_value` double NOT NULL DEFAULT '0',
  `contract_wage` double NOT NULL DEFAULT '0',
  `rating` int(11) NOT NULL,
  PRIMARY KEY (`players_id`,`default_teams_id`),
  KEY `FK_players_master_countries_id` (`countries_id`),
  KEY `FK_players_master_positions_id` (`positions_id`),
  KEY `IDX_first_name` (`first_name`),
  KEY `IDX_known_as` (`known_as`),
  KEY `IDX_second_name` (`second_name`),
  KEY `IDX_dob` (`dob`),
  KEY `IDX_estimated_value` (`estimated_value`),
  KEY `IDX_contract_wage` (`contract_wage`),
  KEY `IDX_rating` (`rating`),
  KEY `FK_players_master_teams_id` (`default_teams_id`),
  CONSTRAINT `FK_players_master_countries_id` FOREIGN KEY (`countries_id`)
REFERENCES `countries` (`countries_id`) ON DELETE NO ACTION ON UPDATE NO
ACTION,
  CONSTRAINT `FK_players_master_positions_id` FOREIGN KEY (`positions_id`)
REFERENCES `positions` (`positions_id`) ON DELETE NO ACTION ON UPDATE NO
ACTION,
  CONSTRAINT `FK_players_master_teams_id` FOREIGN KEY (`default_teams_id`)
REFERENCES `teams_master` (`teams_id`) ON DELETE NO ACTION ON UPDATE NO
ACTION
) ENGINE=InnoDB AUTO_INCREMENT=1001 DEFAULT CHARSET=utf8
COLLATE=utf8_unicode_ci

I'm confused, I thought primary keys were always unique ?

Cheers
Neil


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/mysql?unsub=arch...@jab.org

Reply via email to