Primary key not unique on InnoDB table

--0016364ec9bc20ec2f049280876c
Content-Type: text/plain; charset=ISO-8859-1

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

--0016364ec9bc20ec2f049280876c--

Re: Primary key not unique on InnoDB table

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" escreveu na mensagem
news:AANLkTi=-1wVUxDFsQ4KM6RfZ0wsRLpPHuG1bnt4X9RhK@mail.gmai l.com...
> 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=gcdmg-mysql-2@m.gmane.org

Re: Primary key not unique on InnoDB table

Sorry, the word is counpound instead of composed.

--
João Cândido de Souza Neto

""João Cândido de Souza Neto"" escreveu na
mensagem news:20101013144314.9787.qmail@lists.mysql.com...
> 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" escreveu na mensagem
> news:AANLkTi=-1wVUxDFsQ4KM6RfZ0wsRLpPHuG1bnt4X9RhK@mail.gmai l.com...
>> 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=gcdmg-mysql-2@m.gmane.org

Re: Primary key not unique on InnoDB table

--0016364990872916c2049280a823
Content-Type: text/plain; charset=ISO-8859-1

Hi Neil,

Yes, primary key is always unique.

In your case, you are using composite key (players_id,default_teams_id).

_Krishna

On Wed, Oct 13, 2010 at 8:07 PM, Tompkins Neil > wrote:

> 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
>

--0016364990872916c2049280a823--

Re: Primary key not unique on InnoDB table

--20cf300fb293a57bde049280b9e4
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

Sorry Joao, I thought that was pretty standard to have a primary key with
auto_increment ??


2010/10/13 Jo=E3o C=E2ndido de Souza Neto

> Sorry, the word is counpound instead of composed.
>
> --
> Jo=E3o C=E2ndido de Souza Neto
>
> ""Jo=E3o C=E2ndido de Souza Neto"" escreveu na
> mensagem news:20101013144314.9787.qmail@lists.mysql.com...
> > I=B4d never seen before a composed primary key that has an auto_increme=
nt
> > 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=E3o C=E2ndido de Souza Neto
> >
> > "Tompkins Neil" escreveu na mensagem
> > news:AANLkTi=3D-1wVUxDFsQ4KM6RfZ0wsRLpPHuG1bnt4X9RhK@mail.gm ail.com...
> >> 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 N=
O
> >> ACTION
> >> ) ENGINE=3DInnoDB AUTO_INCREMENT=3D1001 DEFAULT CHARSET=3Dutf8
> >> COLLATE=3Dutf8_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=3Dneil.tompkins@googlemai l.com
>
>

--20cf300fb293a57bde049280b9e4--

Re: Primary key not unique on InnoDB table

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" escreveu na mensagem
news:AANLkTi=xNJCAiq7BmOXg-q+4nOWdhV8uAj9DcqroLB_O@mail.gmai l.com...
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

> Sorry, the word is counpound instead of composed.
>
> --
> João Cândido de Souza Neto
>
> ""João Cândido de Souza Neto"" escreveu na
> mensagem news:20101013144314.9787.qmail@lists.mysql.com...
> > 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" escreveu na mensagem
> > news:AANLkTi=-1wVUxDFsQ4KM6RfZ0wsRLpPHuG1bnt4X9RhK@mail.gmai l.com...
> >> 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=neil.tompkins@googlemail. com
>
>



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Primary key not unique on InnoDB table

--20cf3010edd122b3fa049280eba2
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

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

2010/10/13 Jo=E3o C=E2ndido de Souza Neto

> 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=E3o C=E2ndido de Souza Neto
>
> "Tompkins Neil" escreveu na mensagem
> news:AANLkTi=3DxNJCAiq7BmOXg-q+4nOWdhV8uAj9DcqroLB_O@mail.gm ail.com...
> Sorry Joao, I thought that was pretty standard to have a primary key with
> auto_increment ??
>
>
> 2010/10/13 Jo=E3o C=E2ndido de Souza Neto
>
> > Sorry, the word is counpound instead of composed.
> >
> > --
> > Jo=E3o C=E2ndido de Souza Neto
> >
> > ""Jo=E3o C=E2ndido de Souza Neto"" escreveu =
na
> > mensagem news:20101013144314.9787.qmail@lists.mysql.com...
> > > I=B4d never seen before a composed primary key that has an auto_incre=
ment
> > > 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=E3o C=E2ndido de Souza Neto
> > >
> > > "Tompkins Neil" escreveu na mensagem
> > > news:AANLkTi=3D-1wVUxDFsQ4KM6RfZ0wsRLpPHuG1bnt4X9RhK@mail.gm ail.com..=
..
> > >> 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 UPDAT=
E
> > >> NO
> > >> ACTION,
> > >> CONSTRAINT `FK_players_master_positions_id` FOREIGN KEY
> > (`positions_id`)
> > >> REFERENCES `positions` (`positions_id`) ON DELETE NO ACTION ON UPDAT=
E
> > >> 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=3DInnoDB AUTO_INCREMENT=3D1001 DEFAULT CHARSET=3Dutf8
> > >> COLLATE=3Dutf8_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=3Dneil.tompkins@googlemai l.com
> >
> >
>
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/mysql?unsub=3Dneil.tompkins@googlemai l.com
>
>

--20cf3010edd122b3fa049280eba2--

Re: Primary key not unique on InnoDB table

--0016364d2e313dd6000492811c68
Content-Type: text/plain; charset=ISO-8859-1

Of course, sorry totally stupid should I recognised that.

Thanks
Neil

On Wed, Oct 13, 2010 at 3:46 PM, Krishna Chandra Prajapati <
prajapatikc@gmail.com> wrote:

> Hi Neil,
>
> Yes, primary key is always unique.
>
> In your case, you are using composite key (players_id,default_teams_id).
>
> _Krishna
>
> On Wed, Oct 13, 2010 at 8:07 PM, Tompkins Neil <
> neil.tompkins@googlemail.com> wrote:
>
>> 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
>>
>
>

--0016364d2e313dd6000492811c68--

Re: Primary key not unique on InnoDB table

On 10/13/2010 10:37 AM, Tompkins Neil wrote:
> 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
>

I see no reason why this won't work. Show us some duplicate data and I
may be able to explain how to fix your definition.

--
Shawn Green
MySQL Principal Technical Support Engineer
Oracle USA, Inc.
Office: Blountville, TN

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Primary key not unique on InnoDB table

On 10/13/2010 11:37 AM, Tompkins Neil wrote:
> Shawn, sorry my error, I didn't realise I had two fields as the primary key
>

That's misinformation. You can have multiple fields as a primary key.

Show us what you think is duplicate data and I may be able to help you
fix your definition

--
Shawn Green
MySQL Principal Technical Support Engineer
Oracle USA, Inc.
Office: Blountville, TN

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Primary key not unique on InnoDB table

--20cf30050de0d69c420492818113
Content-Type: text/plain; charset=ISO-8859-1

Shawn it is fine. I thought my primary key was just 1 field.

On Wed, Oct 13, 2010 at 4:44 PM, Shawn Green (MySQL) <
shawn.l.green@oracle.com> wrote:

> On 10/13/2010 11:37 AM, Tompkins Neil wrote:
>
>> Shawn, sorry my error, I didn't realise I had two fields as the primary
>> key
>>
>>
> That's misinformation. You can have multiple fields as a primary key.
>
> Show us what you think is duplicate data and I may be able to help you fix
> your definition
>
> --
> Shawn Green
> MySQL Principal Technical Support Engineer
> Oracle USA, Inc.
> Office: Blountville, TN
>

--20cf30050de0d69c420492818113--

RE: Primary key not unique on InnoDB table

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@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=gcdmg-mysql-2@m.gmane.org

Re: Primary key not unique on InnoDB table

--00163630f47522fca304928485aa
Content-Type: text/plain; charset=ISO-8859-1

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 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@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
>
>

--00163630f47522fca304928485aa--

Fwd: Primary key not unique on InnoDB table

--0016362843147dc89b0492a4a5ff
Content-Type: text/plain; charset=ISO-8859-1

Based on my reply below, do you recommend I continue to have these indexes ?

---------- Forwarded message ----------
From: Tompkins Neil
Date: Wed, Oct 13, 2010 at 8:22 PM
Subject: Re: Primary key not unique on InnoDB table
To: Travis Ard
Cc: "[MySQL]"


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 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@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
>
>

--0016362843147dc89b0492a4a5ff--

RE: Primary key not unique on InnoDB table

------=_NextPart_000_020B_01CB6C88.AADDDD50
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: 7bit

You obviously know best how your application will query your database, and
you may have already thought through your indexing strategy. If so, please
disregard my comments.



In my experience, it is not often you need separate indexes on most or all
the columns in a table (excepting very narrow tables, perhaps), so I would
think about how you anticipate the database might use each of these indexes.
Even though you may have multiple indexes available, most of the time a
database query optimizer will only choose one when deciding how to retrieve
data for a query. So, if you have a column like first_name that is indexed,
your database engine may never use this index unless you have a query like
"select * from players_master where first_name = 'xyz'". If a column is
part of your select list, but is not used as your WHERE clause expression or
as part of a table join, indexing that column may not be a benefit. Running
EXPLAIN will tell you whether or not the index you anticipate is actually
being used for your query.



-Travis





From: Tompkins Neil [mailto:neil.tompkins@googlemail.com]
Sent: Friday, October 15, 2010 3:43 AM
To: [MySQL]; Travis Ard
Subject: Fwd: Primary key not unique on InnoDB table



Based on my reply below, do you recommend I continue to have these indexes ?

---------- Forwarded message ----------
From: Tompkins Neil
Date: Wed, Oct 13, 2010 at 8:22 PM
Subject: Re: Primary key not unique on InnoDB table
To: Travis Ard
Cc: "[MySQL]"


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 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@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






------=_NextPart_000_020B_01CB6C88.AADDDD50--