how to get the name of primary key ?

--_6a3410ba-bca0-4b9a-8ec2-686c4483f4dc_
Content-Type: text/plain; charset="gb2312"
Content-Transfer-Encoding: 8bit


Hi all,

if we just know the table name but don't know the name of primary key, is there any variables or constants could instead of the PK?

for example:

there is a table "game_log", and now I have the last inserted_id but don't know what its primary_id is,
how can I "SELECT * FROM game_log WHERE this_table's_PK = last_inserted_id"?



____________________________________________________________ _____
Ô¼»á˵²»ÇåµØ·½£¿À´ÊÔÊÔ΢ÈíµØͼ×îÐÂmsn»¥¶¯¹¦ÄÜ£¡
http://ditu.live.com/?form=TL&swm=1
--_6a3410ba-bca0-4b9a-8ec2-686c4483f4dc_--

Re: how to get the name of primary key ?

--0016e68dec47db0e7a047e862340
Content-Type: text/plain; charset=GB2312
Content-Transfer-Encoding: quoted-printable

Hi,

Use

*SELECT * FROM game_log WHERE this_table's_PK =3D last_insert_id()*

It will help you.

Regards,
Thiyaghu CK,
MySQL DBA
www.mafiree.com

2010/2/1 ²Ü¿­

>
> Hi all,
>
> if we just know the table name but don't know the name of primary key, is
> there any variables or constants could instead of the PK?
>
> for example:
>
> there is a table "game_log", and now I have the last inserted_id but don'=
t
> know what its primary_id is,
> how can I "SELECT * FROM game_log WHERE this_table's_PK =3D
> last_inserted_id"?
>
>
>
> ____________________________________________________________ _____
> Ô¼»á˵²»ÇåµØ·½£¿À´ÊÔ ÊÔ΢=
ÈíµØͼ×îÐÂmsn»¥¶¯¹¦ÄÜ=A3 =A1
> http://ditu.live.com/?form=3DTL&swm=3D1

--0016e68dec47db0e7a047e862340--

RE: how to get the name of primary key ?

--_0d51355e-d558-473e-b32b-978cafb607dc_
Content-Type: text/plain; charset="gb2312"
Content-Transfer-Encoding: 8bit



Hi Thiyaghu,

I have already got the last_insert_id. now I wanna know if there are any variables or constants to instead of game_log's primary key cos I don't know its name.






> Date: Mon, 1 Feb 2010 14:24:59 +0530
> Subject: Re: how to get the name of primary key ?
> From: theyahoock@gmail.com
> To: txdyc@hotmail.com
> CC: mysql@lists.mysql.com
>
> Hi,
>
> Use
>
> *SELECT * FROM game_log WHERE this_table's_PK = last_insert_id()*
>
> It will help you.
>
> Regards,
> Thiyaghu CK,
> MySQL DBA
> www.mafiree.com
>
> 2010/2/1 ²Ü¿­
>
> >
> > Hi all,
> >
> > if we just know the table name but don't know the name of primary key, is
> > there any variables or constants could instead of the PK?
> >
> > for example:
> >
> > there is a table "game_log", and now I have the last inserted_id but don't
> > know what its primary_id is,
> > how can I "SELECT * FROM game_log WHERE this_table's_PK =
> > last_inserted_id"?
> >
> >
> >
> > ____________________________________________________________ _____
> > Ô¼»á˵²»ÇåµØ·½£¿À´ÊÔÊÔ΢ÈíµØͼ×îÐÂmsn»¥¶¯¹¦ÄÜ£¡
> > http://ditu.live.com/?form=TL&swm=1

____________________________________________________________ _____
Ô¼»á˵²»ÇåµØ·½£¿À´ÊÔÊÔ΢ÈíµØͼ×îÐÂmsn»¥¶¯¹¦ÄÜ£¡
http://ditu.live.com/?form=TL&swm=1
--_0d51355e-d558-473e-b32b-978cafb607dc_--

Re: how to get the name of primary key ?

--Apple-Mail-1--1053612399
Content-Transfer-Encoding: quoted-printable
Content-Type: text/plain;
charset=GB2312;
delsp=yes;
format=flowed

On 01/02/2010, at 7:33 PM, ²Ü¿­ wrote:

>
> Hi all,
>
> if we just know the table name but don't know the name of primary =20
> key, is there any variables or constants could instead of the PK?
>
> for example:
>
> there is a table "game_log", and now I have the last inserted_id =20
> but don't know what its primary_id is,
> how can I "SELECT * FROM game_log WHERE this_table's_PK =3D =20
> last_inserted_id"?

You can get the column name from the information schema, however that =20=

can't be used directly in another query in the way you've done in =20
your example. E.g.

game> SELECT COLUMN_NAME FROM information_schema.KEY_COLUMN_USAGE =20
WHERE TABLE_SCHEMA =3D 'game' AND TABLE_NAME =3D 'game_log' AND =20
CONSTRAINT_NAME =3D 'PRIMARY';
+-------------+
| COLUMN_NAME |
+-------------+
| GameLogID |
+-------------+
1 row in set (0.00 sec)

where it is assumed the database name is "game".

Hope that helps.

Jesper=

--Apple-Mail-1--1053612399--

RE: how to get the name of primary key ?

--_9b9a4681-8fa6-4166-a7cf-1ad204ab7a5e_
Content-Type: text/plain; charset="gb2312"
Content-Transfer-Encoding: 8bit


hi Jesper,


thanks a lot! That's what I want. thank u againCC: mysql@lists.mysql.com
From: jesper@noggin.com.au
Subject: Re: how to get the name of primary key ?
Date: Mon, 1 Feb 2010 20:26:36 +1100
To: txdyc@hotmail.com

On 01/02/2010, at 7:33 PM, ²Ü¿­ wrote:
Hi all,
if we just know the table name but don't know the name of primary key, is there any variables or constants could instead of the PK?
for example:
there is a table "game_log", and now I have the last inserted_id but don't know what its primary_id is, how can I "SELECT * FROM game_log WHERE this_table's_PK = last_inserted_id"?
You can get the column name from the information schema, however that can't be used directly in another query in the way you've done in your example. E.g.
game> SELECT COLUMN_NAME FROM information_schema.KEY_COLUMN_USAGE WHERE TABLE_SCHEMA = 'game' AND TABLE_NAME = 'game_log' AND CONSTRAINT_NAME = 'PRIMARY';+-------------+| COLUMN_NAME |+-------------+| GameLogID | +-------------+1 row in set (0.00 sec)
where it is assumed the database name is "game".
Hope that helps.
Jesper
____________________________________________________________ _____
Windows LiveÉçÇøÁ½ÖÜÄ꣬Äý±¹ýÐÂÄ꣡
http://events.livetome.cn/2010/2birthday
--_9b9a4681-8fa6-4166-a7cf-1ad204ab7a5e_--

Re: how to get the name of primary key ?

--00504502bc703ae4e1047e89df41
Content-Type: text/plain; charset=GB2312
Content-Transfer-Encoding: quoted-printable

u can also get information of table using

use> use urdbname
mysql>show create table game_log \G

2010/2/1 ²Ü¿­

>
> hi Jesper,
>
>
> thanks a lot! That's what I want. thank u againCC: mysql@lists.mysql.co=
m
> From: jesper@noggin.com.au
> Subject: Re: how to get the name of primary key ?
> Date: Mon, 1 Feb 2010 20:26:36 +1100
> To: txdyc@hotmail.com
>
> On 01/02/2010, at 7:33 PM, ²Ü¿­ wrote:
> Hi all,
> if we just know the table name but don't know the name of primary key, is
> there any variables or constants could instead of the PK?
> for example:
> there is a table "game_log", and now I have the last inserted_id but don'=
t
> know what its primary_id is, how can I "SELECT * FROM game_log WHERE
> this_table's_PK =3D last_inserted_id"?
> You can get the column name from the information schema, however that can=
't
> be used directly in another query in the way you've done in your example.
> E.g.
> game> SELECT COLUMN_NAME FROM information_schema.KEY_COLUMN_USAGE WHERE
> TABLE_SCHEMA =3D 'game' AND TABLE_NAME =3D 'game_log' AND CONSTRAINT_NAME=
=3D
> 'PRIMARY';+-------------+| COLUMN_NAME |+-------------+| GameLogID |
> +-------------+1 row in set (0.00 sec)
> where it is assumed the database name is "game".
> Hope that helps.
> Jesper
> ____________________________________________________________ _____
> Windows LiveÉçÇøÁ½ÖÜÄ꣬Äý±¹ý=D 0=C2=
Ä꣡
> http://events.livetome.cn/2010/2birthday




--=20
Best Regards,

Prabhat Kumar
MySQL DBA
Datavail-India Mumbai
Mobile : 91-9987681929
www.datavail.com

My Blog: http://adminlinux.blogspot.com
My LinkedIn: http://www.linkedin.com/in/profileprabhat

--00504502bc703ae4e1047e89df41--