CODEWITHSUNDEEP
mysqlsqlprimary-keywhere-clause
JStark
JStark

Reputation: 2788

Is there a keyword to identify a PRIMARY column in a MySQL WHERE clause?

I have a situation where the column name "field1" and "field3" are not given to me but all the other data is. The request is coming in via a url in like: /table1/1 or /table2/3 and it is assumed that 1 or 3 represent the primary key. However, the column name may be different. Consider the following 2 queries:

SELECT * FROM table1 where field1 = 1 and field2 =2;
SELECT * FROM table2 where field3 = 3 and field4 =4;

Ideally, I'd like to perform a search like the following:

SELECT * FROM table1 where MYSQL_PRIMARY_COLUMN = 1 and field2 =2;
SELECT * FROM table2 where MYSQL_PRIMARY_COLUMN = 3 and field4 =4;

Is there a keyword to identify MYSQL_PRIMARY_COLUMN in a MySQL WHERE clause?

Upvotes: 1

Views: 125

Answers (2)

Bill Karwin
Bill Karwin

Reputation: 562280

No, there's no pseudocolumn you can use to map to the primary key column. One reason this is complicated is that a given table may have a multi-column primary key. This is a totally ordinary way to design a table:

CREATE TABLE BooksAuthors (
  book_id INT NOT NULL,
  author_id INT NOT NULL,
  PRIMARY KEY (book_id, author_id)
);

When I implemented the table data gateway class in Zend Framework 1.0, I had to write code to "discover" the table's primary key column (or columns) as @doublesharp describes. Then the table object instance retained this information so that searches and updates knew which column (or columns) to use when generating queries.

I understand you're looking for a solution that doesn't require this "2 pass process" but no such solution exists for the general case.

Some application framework environments attempt to simplify the problem by encouraging developers to give every table a single column primary key, and name the column "id" by convention. But this convention is an oversimplification that fails to cover many legitimate table designs.

Upvotes: 1

doublesharp
doublesharp

Reputation: 27599

You can use DESCRIBE (which is a synonym for EXPLAIN) to get information about the table, which will include the all column information.

DESCRIBE `table`;

You can also use SHOW INDEX to just get information about the PRIMARY key.

SHOW INDEX FROM `table` WHERE Key_name = 'PRIMARY'

Upvotes: 0

Related Questions