Determining Primary Key

#11

On Jan 2, 5:20*pm, Jerry Stuckle <jstuck... (AT) attglobal (DOT) net> wrote:

Quote:

On 1/2/2011 4:31 PM, bruce wrote:

On Jan 2, 3:36 pm, Jerry Stuckle<jstuck... (AT) attglobal (DOT) net> *wrote:
On 1/2/2011 3:17 PM, Luuk wrote:

On 02-01-11 19:50, Jerry Stuckle wrote:
On 1/2/2011 10:34 AM, bruce wrote:
Is there anyway, using a SELECT statement, I can determine what the
PRIMARY KEY is?

I want the select a field from the last row in a database. I'm using a
select statement
SELECT myfield FROM table WHERE primarykey - (SELECT MAX(primarykey)
FROM table)

Or is there a better way to get a field from the last row?

Thanks..

Bruce

Define "last row"? Tables are by definition unordered. The only way you
can get a "last" (or "first") row is to ORDER BY a field.

But, if you read the original message:
"WHERE primarykey - (SELECT MAX(primarykey) FROM table)"

This will ALWAYS produce just 1 record, so what order are you talking
about?

That will get the row with the highest primary key, true. *But that is
not necessarily the last row.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstuck... (AT) attglobal (DOT) net
==================

Sorry. I should have said I wanted the "Highest" primary key...

So, is there some way I can find out the row with the "Highest"
primary key without knowing the name of the primary key.

This will give me the primary key name (SHOW INDEX FROM table) but I
don't know how to incorporate this information to my statement

SELECT myfield FROM table WHERE primarykey - (SELECT MAX(primarykey)
FROM table)

I also found the following which returns the primary key name, but
again I don't know how to incorporate the result into my SELECT.

SELECT k.COLUMN_NAME
FROM information_schema.table_constraints t
LEFT JOIN information_schema.key_column_usage k
USING(constraint_name,table_schema,table_name)
WHERE t.constraint_type='PRIMARY KEY'
* * *AND t.table_schema=DATABASE()
* * *AND t.table_name='mytable';

You would have to do a SHOW CREATE TABLE tblname and parse the result.
Bear in mind you may have multiple columns in a primary key, and the
primary key may or may not be numeric. *And in some tables the concept
of "highest primary key" may be nonexistent - as in an n:n link table.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstuck... (AT) attglobal (DOT) net
==================

In my case, the primary key is a single, INT UNSIGNED, element.

Thanks for the help..

Bruce

#12

On Jan 2, 9:31*pm, bruce <bruc... (AT) bellsouth (DOT) net> wrote:

Quote:

On Jan 2, 3:36*pm, Jerry Stuckle <jstuck... (AT) attglobal (DOT) net> wrote:

On 1/2/2011 3:17 PM, Luuk wrote:

On 02-01-11 19:50, Jerry Stuckle wrote:
On 1/2/2011 10:34 AM, bruce wrote:
Is there anyway, using a SELECT statement, I can determine what the
PRIMARY KEY is?

I want the select a field from the last row in a database. I'm using a
select statement
SELECT myfield FROM table WHERE primarykey - (SELECT MAX(primarykey)
FROM table)

Or is there a better way to get a field from the last row?

Thanks..

Bruce

Define "last row"? Tables are by definition unordered. The only way you
can get a "last" (or "first") row is to ORDER BY a field.

But, if you read the original message:
"WHERE primarykey - (SELECT MAX(primarykey) FROM table)"

This will ALWAYS produce just 1 record, so what order are you talking
about?

That will get the row with the highest primary key, true. *But that is
not necessarily the last row.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstuck... (AT) attglobal (DOT) net
==================

Sorry. I should have said I wanted the "Highest" primary key...

So, is there some way I can find out the row with the "Highest"
primary key without knowing the name of the primary key.

This will give me the primary key name (SHOW INDEX FROM table) but I
don't know how to incorporate this information to my statement

SELECT myfield FROM table WHERE primarykey - (SELECT MAX(primarykey)
FROM table)

I also found the following which returns the primary key name, but
again I don't know how to incorporate the result into my SELECT.

SELECT k.COLUMN_NAME
FROM information_schema.table_constraints t
LEFT JOIN information_schema.key_column_usage k
USING(constraint_name,table_schema,table_name)
WHERE t.constraint_type='PRIMARY KEY'
* * AND t.table_schema=DATABASE()
* * AND t.table_name='mytable';

As a matter of interest, WHY do you need this information?

#13

On Jan 2, 5:20*pm, Jerry Stuckle <jstuck... (AT) attglobal (DOT) net> wrote:

Quote:

On 1/2/2011 4:31 PM, bruce wrote:

On Jan 2, 3:36 pm, Jerry Stuckle<jstuck... (AT) attglobal (DOT) net> *wrote:
On 1/2/2011 3:17 PM, Luuk wrote:

On 02-01-11 19:50, Jerry Stuckle wrote:
On 1/2/2011 10:34 AM, bruce wrote:
Is there anyway, using a SELECT statement, I can determine what the
PRIMARY KEY is?

I want the select a field from the last row in a database. I'm using a
select statement
SELECT myfield FROM table WHERE primarykey - (SELECT MAX(primarykey)
FROM table)

Or is there a better way to get a field from the last row?

Thanks..

Bruce

Define "last row"? Tables are by definition unordered. The only way you
can get a "last" (or "first") row is to ORDER BY a field.

But, if you read the original message:
"WHERE primarykey - (SELECT MAX(primarykey) FROM table)"

This will ALWAYS produce just 1 record, so what order are you talking
about?

That will get the row with the highest primary key, true. *But that is
not necessarily the last row.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstuck... (AT) attglobal (DOT) net
==================

Sorry. I should have said I wanted the "Highest" primary key...

So, is there some way I can find out the row with the "Highest"
primary key without knowing the name of the primary key.

This will give me the primary key name (SHOW INDEX FROM table) but I
don't know how to incorporate this information to my statement

SELECT myfield FROM table WHERE primarykey - (SELECT MAX(primarykey)
FROM table)

I also found the following which returns the primary key name, but
again I don't know how to incorporate the result into my SELECT.

SELECT k.COLUMN_NAME
FROM information_schema.table_constraints t
LEFT JOIN information_schema.key_column_usage k
USING(constraint_name,table_schema,table_name)
WHERE t.constraint_type='PRIMARY KEY'
* * *AND t.table_schema=DATABASE()
* * *AND t.table_name='mytable';

You would have to do a SHOW CREATE TABLE tblname and parse the result.
Bear in mind you may have multiple columns in a primary key, and the
primary key may or may not be numeric. *And in some tables the concept
of "highest primary key" may be nonexistent - as in an n:n link table.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstuck... (AT) attglobal (DOT) net
==================

The OP could learn to use google... however, Wouldn't it be easier to
query the INFORMATION_SCHEMA?

From: http://rpbouman.blogspot.com/2008/03...entifying.html

'SELECT k.column_name
FROM information_schema.table_constraints t
JOIN information_schema.key_column_usage k
USING(constraint_name,table_schema,table_name)
WHERE t.constraint_type='PRIMARY KEY'
AND t.table_schema='db_name'
AND t.table_name='tbl_name';'

#14

Quote:

Is there anyway, using a SELECT statement, I can determine what the
PRIMARY KEY is?

You can do a SELECT from INFORMATION_SCHEMA.KEY_COLUMN_USAGE and
look for rows where CONSTRAINT_NAME = 'PRIMARY' and a matching
database and table to what you're looking for.

Note that a primary key may be a compound primary key.

Quote:

I want the select a field from the last row in a database. I'm using a

The definition of "last row" is rather fuzzy without an ordering.
If you're going to consider ordering by the primary key, fine, but
it may be a compound primary key.

Quote:

select statement
SELECT myfield FROM table WHERE primarykey - (SELECT MAX(primarykey)
FROM table)

There's a syntax error here, and it won't work for compound primary keys.

SELECT myfield FROM table ORDER BY primarykeypart1 desc, primarykeypart2 desc,
primarykeypart3 desc, ... LIMIT 1

Quote:

Or is there a better way to get a field from the last row?

Are you really sure that "last row" makes any more sense than "last finger"
or "last key on the keyboard"?

#15

El 03/01/2011 23:07, onedbguru escribió/wrote:

Quote:

The OP could learn to use google... however, Wouldn't it be easier to
query the INFORMATION_SCHEMA?

From: http://rpbouman.blogspot.com/2008/03...entifying.html

'SELECT k.column_name
FROM information_schema.table_constraints t
JOIN information_schema.key_column_usage k
USING(constraint_name,table_schema,table_name)
WHERE t.constraint_type='PRIMARY KEY'
AND t.table_schema='db_name'
AND t.table_name='tbl_name';'

In shared hosting services it's not common to have permissions over the
information_schema database.

--
-- http://alvaro.es - Álvaro G. Vicario - Burgos, Spain
-- Mi sitio sobre programación web: http://borrame.com
-- Mi web de humor satinado: http://www.demogracia.com
--

#16

On Jan 4, 3:09*am, "Álvaro G. Vicario"
<alvaro.NOSPAMTH... (AT) demogracia (DOT) com.invalid> wrote:

Quote:

El 03/01/2011 23:07, onedbguru escribi /wrote:

The OP could learn to use google... however, Wouldn't it *be easier to
query the INFORMATION_SCHEMA?

From: *http://rpbouman.blogspot.com/2008/03...chema-identify...

'SELECT k.column_name
FROM information_schema.table_constraints t
JOIN information_schema.key_column_usage k
USING(constraint_name,table_schema,table_name)
WHERE t.constraint_type='PRIMARY KEY'
AND t.table_schema='db_name'
AND t.table_name='tbl_name';'

In shared hosting services it's not common to have permissions over the
information_schema database.

--
--http://alvaro.es- lvaro G. Vicario - Burgos, Spain
-- Mi sitio sobre programaci n web:http://borrame.com
-- Mi web de humor satinado:http://www.demogracia.com
--

Short of hard-coding your "create table" statement or doing something
equally annoying like parsing a show tables command, you are pretty
much out of luck. You should always have sufficient control over
your database to accomplish the purpose for which it was created.
More to the point, if you do not have control over your database, you
do not have control over anything. I have heard way too many stories
about people using shared services and all of a sudden their provider
goes out of business and shuts everything down - leaving you with
nothing.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Determining Primary Key

comp.databases.mysql comp.databases.mysql

Re: Determining Primary Key - 01-02-2011 , 04:36 PM

Re: Determining Primary Key - 01-03-2011 , 06:31 AM

Re: Determining Primary Key - 01-03-2011 , 04:07 PM

Re: Determining Primary Key - 01-03-2011 , 07:08 PM

Re: Determining Primary Key - 01-04-2011 , 02:09 AM

Re: Determining Primary Key - 01-04-2011 , 11:57 AM