List:General Discussion« Previous MessageNext Message »
From:Tompkins Neil Date:October 13 2010 7:22pm
Subject:Re: Primary key not unique on InnoDB table
View as plain text  
Hi Travis,

Thanks for your response.  The fields which have indexes on, can be used on
every other search, which is why I thought about creating them.  Would you
recommend against this ?

Cheers
Neil

On Wed, Oct 13, 2010 at 6:48 PM, Travis Ard <travis_ard@stripped> wrote:

> 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.tompkins@stripped]
> 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
>
>

Thread
Primary key not unique on InnoDB tableTompkins Neil13 Oct
  • Re: Primary key not unique on InnoDB tablejoao13 Oct
  • Re: Primary key not unique on InnoDB tablejoao13 Oct
    • Re: Primary key not unique on InnoDB tableTompkins Neil13 Oct
  • Re: Primary key not unique on InnoDB tableKrishna Chandra Prajapati13 Oct
    • Re: Primary key not unique on InnoDB tableTompkins Neil13 Oct
  • Re: Primary key not unique on InnoDB tablejoao13 Oct
    • Re: Primary key not unique on InnoDB tableTompkins Neil13 Oct
  • Re: Primary key not unique on InnoDB tableMySQL)13 Oct
  • RE: Primary key not unique on InnoDB tableTravis Ard13 Oct
    • Re: Primary key not unique on InnoDB tableTompkins Neil13 Oct
      • Fwd: Primary key not unique on InnoDB tableTompkins Neil15 Oct
        • RE: Primary key not unique on InnoDB tableTravis Ard16 Oct
Re: Primary key not unique on InnoDB tableMySQL)13 Oct
  • Re: Primary key not unique on InnoDB tableTompkins Neil13 Oct