Re: Primary key not unique on InnoDB table

[Tompkins Neil]

I see what you mean.  Infact this is wrong and I will be dropping the second
field in the primary key.

2010/10/13 João Cândido de Souza Neto <joao@stripped>

> A primary key with an auto_increment is ok, but I cant think about a
> primary
> key with two fiels where one of them is autoincrement. Am I completely
> wrong?
>
> --
> João Cândido de Souza Neto
>
> "Tompkins Neil" <neil.tompkins@stripped> escreveu na mensagem
> news:AANLkTi=xNJCAiq7BmOXg-q+4nOWdhV8uAj9DcqroLB_O@ style="color:#666">stripped...
> Sorry Joao, I thought that was pretty standard to have a primary key with
> auto_increment ??
>
>
> 2010/10/13 João Cândido de Souza Neto <joao@stripped>
>
> > Sorry, the word is counpound instead of composed.
> >
> > --
> > João Cândido de Souza Neto
> >
> > ""João Cândido de Souza Neto"" <joao@stripped>
> escreveu na
> > mensagem news:20101013144314.9787.qmail@ style="color:#666">stripped...
> > > I´d never seen before a composed primary key that has an
> auto_increment
> > > 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?
> > >
> > > --
> > > João Cândido de Souza Neto
> > >
> > > "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
> > >>
> > >
> > >
> >
> >
> >
> > --
> > MySQL General Mailing List
> > For list archives: http://lists.mysql.com/mysql
> > To unsubscribe:
> > http://lists.mysql.com/mysql?unsub=1
> >
> >
>
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/mysql?unsub=1
>
>