Re: Primary key not unique on InnoDB table

[João Cândido de Souza Neto]

field on it.

May be I can be wrong but I think it wont work properly.

As far as I know, if you have an auto_increment field it must be your single 
primary key. Am I wrong?

-- 

"Tompkins Neil" <neil.tompkins@stripped> escreveu na mensagem 
news:AANLkTi=-1wVUxDFsQ4KM6RfZ0wsRLpPHuG1bnt4X9RhK@ style="color:#666">stripped...
> 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
>