Two Primary Keys

--00504502acf4f07ef9048a2b292f
Content-Type: text/plain; charset=ISO-8859-1

Hi;
I have the following:

create table pics (
picture_id int auto_increment primary key,
product_sku int not null primary key,
picture_num int not null,
picture_desc varchar(100),
picture_data longblob
);

which doesn't work I need to auto_increment and declare primary key on two
fields. How do?
TIA,
Victor

--00504502acf4f07ef9048a2b292f--

Re: Two Primary Keys

As far as I know, if you have an auto_increment primary key, you cant have
any other field in its primary key.


João Cândido.

"Victor Subervi" escreveu na mensagem
news:AANLkTikzkSMBx5hUe0X_Q3Hx_68gICndghpkJdRNA9xA@mail.gmai l.com...
> Hi;
> I have the following:
>
> create table pics (
> picture_id int auto_increment primary key,
> product_sku int not null primary key,
> picture_num int not null,
> picture_desc varchar(100),
> picture_data longblob
> );
>
> which doesn't work I need to auto_increment and declare primary key on two
> fields. How do?
> TIA,
> Victor
>



--
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: Two Primary Keys

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

2010/6/29 Jo=E3o C=E2ndido de Souza Neto

> As far as I know, if you have an auto_increment primary key, you cant hav=
e
> any other field in its primary key.
>

Makes sense. Actually, I was just copying what someone else gave me and
adding the auto_increment, then I got to wondering, what is the purpose of
having two primary keys?
TIA,
V

--0016368e2c6dbe85bb048a2b436b--

Re: Two Primary Keys

On 6/29/2010 9:24 AM, Victor Subervi wrote:
> Hi;
> I have the following:
>
> create table pics (
> picture_id int auto_increment primary key,
> product_sku int not null primary key,
> picture_num int not null,
> picture_desc varchar(100),
> picture_data longblob
> );
>
> which doesn't work I need to auto_increment and declare primary key on two
> fields. How do?
> TIA,
> Victor
>

Use the other syntax for defining keys:

create table pics (
picture_id int auto_increment,
product_sku int not null,
picture_num int not null,
picture_desc varchar(100),
picture_data longblob,
PRIMARY KEY(product_sku_int, picture_id)
);

--
Shawn Green
MySQL Principle 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: Two Primary Keys

I think the real question is: What´s the purpose of any other field in my
primary key if the first one is an auto_increment and will never repeat?

--
João Cândido de Souza Neto

"Victor Subervi" escreveu na mensagem
news:AANLkTinyaAPs4JmbbJALd6KDoK7lfhXLYKwQ0TmpT-PA@mail.gmai l.com...
2010/6/29 João Cândido de Souza Neto

> As far as I know, if you have an auto_increment primary key, you cant have
> any other field in its primary key.
>

Makes sense. Actually, I was just copying what someone else gave me and
adding the auto_increment, then I got to wondering, what is the purpose of
having two primary keys?
TIA,
V



--
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: Two Primary Keys

If you use innodb, primary key lookups are far faster than secondary
indexes.

Peter

On 06/29/2010 03:34 PM, João Cândido de Souza Neto wrote:
> I think the real question is: What´s the purpose of any other field in my
> primary key if the first one is an auto_increment and will never repeat?
>

--
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: Two Primary Keys

I think the best, or may be the right way is to use picture_id as primary
key and a unique index to product_sku.

--
João Cândido de Souza Neto

"Victor Subervi" escreveu na mensagem
news:AANLkTikzkSMBx5hUe0X_Q3Hx_68gICndghpkJdRNA9xA@mail.gmai l.com...
> Hi;
> I have the following:
>
> create table pics (
> picture_id int auto_increment primary key,
> product_sku int not null primary key,
> picture_num int not null,
> picture_desc varchar(100),
> picture_data longblob
> );
>
> which doesn't work I need to auto_increment and declare primary key on two
> fields. How do?
> TIA,
> Victor
>



--
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: Two Primary Keys

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

2010/6/29 Jo=E3o C=E2ndido de Souza Neto

> I think the best, or may be the right way is to use picture_id as primary
> key and a unique index to product_sku.
>

Yes, sounds good. So the purpose, then, is to speed lookups on fields
commonly accessed. I'd forgotten that.
Thanks,
V

--00504502cd1159fdaf048a2b912e--

Re: Two Primary Keys

Hi,

try this and you will see exactly how autoincrement behaves in MyISAM
tables when it is part of primary key.

1) declare table like this:
CREATE TABLE `test_tbl` (
`field1` int(10) unsigned NOT NULL default '0',
`field2` int(10) unsigned NOT NULL auto_increment,
`field3` char(10) NOT NULL default '',
PRIMARY KEY (`field1`,`field2`)
) ENGINE=MyISAM;

2) then insert some values
INSERT INTO test_tbl (field1, field3)
VALUES(1,'test1'),(2,'test2'),(1,'test3'),(2,'test4');

3) see what's in the table
SELECT * FROM test_tbl ORDER BY field1;

result is:
1, 1, 'test1'
1, 2, 'test3'
2, 1, 'test2'
2, 2, 'test4'

field2 is unique only in context of field1.

Hth,
Dusan



Victor Subervi napsal(a):
> 2010/6/29 João Cândido de Souza Neto
>
>
>> As far as I know, if you have an auto_increment primary key, you cant have
>> any other field in its primary key.
>>
>>
>
> Makes sense. Actually, I was just copying what someone else gave me and
> adding the auto_increment, then I got to wondering, what is the purpose of
> having two primary keys?
> TIA,
> V
>
>

--
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: Two Primary Keys

You were talking about multiple fields in the primary key, not multiple
primary keys.

On 06/29/2010 03:51 PM, Johan De Meersman wrote:
> Correct, but you still can't have more than one primary key. Kind of
> defeats the idea of it being primary, really.
>
> On Tue, Jun 29, 2010 at 3:36 PM, petya > > wrote:
>
> If you use innodb, primary key lookups are far faster than secondary
> indexes.
>
> Peter
>
>
> On 06/29/2010 03:34 PM, João Cândido de Souza Neto wrote:
>
> I think the real question is: What´s the purpose of any other
> field in my
> primary key if the first one is an auto_increment and will never
> repeat?
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=vegivamp@tuxera.be
>
>
>
>
> --
> Bier met grenadyn
> Is als mosterd by den wyn
> Sy die't drinkt, is eene kwezel
> Hy die't drinkt, is ras een ezel

--
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: Two Primary Keys

@Dušan Pavlica;

I must say thank you. =20
This is really interesting, and in the 7 years I've been using mysql and =
sql, I've never know this (or had, but didn't realize what I had done).
This little bit of information could make for some interesting changes =
to a couple of my projects I am working on, where I've done this, but =
done it in code, rather than in mysql.


Steven Staples


> -----Original Message-----
> From: Dušan Pavlica [mailto:pavlica@unidataz.cz]
> Sent: June 29, 2010 11:26 AM
> To: Victor Subervi
> Cc: mysql@lists.mysql.com
> Subject: Re: Two Primary Keys
>=20
> Hi,
>=20
> try this and you will see exactly how autoincrement behaves in MyISAM
> tables when it is part of primary key.
>=20
> 1) declare table like this:
> CREATE TABLE `test_tbl` (
> `field1` int(10) unsigned NOT NULL default '0',
> `field2` int(10) unsigned NOT NULL auto_increment,
> `field3` char(10) NOT NULL default '',
> PRIMARY KEY (`field1`,`field2`)
> ) ENGINE=3DMyISAM;
>=20
> 2) then insert some values
> INSERT INTO test_tbl (field1, field3)
> VALUES(1,'test1'),(2,'test2'),(1,'test3'),(2,'test4');
>=20
> 3) see what's in the table
> SELECT * FROM test_tbl ORDER BY field1;
>=20
> result is:
> 1, 1, 'test1'
> 1, 2, 'test3'
> 2, 1, 'test2'
> 2, 2, 'test4'
>=20
> field2 is unique only in context of field1.
>=20
> Hth,
> Dusan
>=20
>=20
>=20
> Victor Subervi napsal(a):
> > 2010/6/29 João Cândido de Souza Neto =

> >
> >
> >> As far as I know, if you have an auto_increment primary key, you =
cant
> have
> >> any other field in its primary key.
> >>
> >>
> >
> > Makes sense. Actually, I was just copying what someone else gave me =
and
> > adding the auto_increment, then I got to wondering, what is the =
purpose
> of
> > having two primary keys?
> > TIA,
> > V
> >
> >
>=20
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: =
http://lists.mysql.com/mysql?unsub=3Dsstaples@mnsi.net
>=20
> No virus found in this incoming message.
> Checked by AVG - www.avg.com
> Version: 9.0.830 / Virus Database: 271.1.1/2917 - Release Date: =
06/29/10
> 02:35:00


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

Re: Two Primary Keys

This isn't true for innodb.
I think the only requirement is that you need to have a unique index
on the auto increment column.
We created a composite primary key + auto_increment to take advantage
of clustering by primary key while satisfying unique constraint for
the primary key. It worked out well for us except for the sheer size
of the indexes.
Kyong

2010/6/29 Jo=E3o C=E2ndido de Souza Neto :
> As far as I know, if you have an auto_increment primary key, you cant hav=
e
> any other field in its primary key.
>
>
> Jo=E3o C=E2ndido.
>
> "Victor Subervi" escreveu na mensagem
> news:AANLkTikzkSMBx5hUe0X_Q3Hx_68gICndghpkJdRNA9xA@mail.gmai l.com...
>> Hi;
>> I have the following:
>>
>> create table pics (
>> =A0 picture_id int auto_increment primary key,
>> =A0 product_sku int not null primary key,
>> =A0 picture_num int not null,
>> =A0 picture_desc varchar(100),
>> =A0 picture_data longblob
>> );
>>
>> which doesn't work I need to auto_increment and declare primary key on t=
wo
>> fields. How do?
>> TIA,
>> Victor
>>
>
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: =A0 =A0http://lists.mysql.com/mysql?unsub=3Dkykimdba@gmai=
l.com
>
>

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