CODEWITHSUNDEEP
mysql
Rishi Kesh Dwivedi
Rishi Kesh Dwivedi

Reputation: 713

mysql: getting primary key of a table programmatically

How can I get primary key of some table programmatically ? I know I can run some queries like "show create table" or "show keys" but after that I will have to parse the result for primary key. Was wondering if there was an easy way to do it.

Upvotes: 0

Views: 164

Answers (2)

rsandwick3
rsandwick3

Reputation: 566

As long as you're working with a "physical" table (i.e., not a view), you should be able to get a single string representation for the constituent columns of the primary key of a table, foo.bar, from the information_schema database:

mysql> SELECT GROUP_CONCAT(CONCAT('`', COLUMN_NAME, '`')) AS `PRIMARY KEY`
    -> FROM information_schema.KEY_COLUMN_USAGE
    -> WHERE CONSTRAINT_NAME = 'PRIMARY'
    ->   AND TABLE_SCHEMA = 'foo'
    ->   AND TABLE_NAME = 'bar'
    -> ORDER BY ORDINAL_POSITION;
+--------------+
| PRIMARY KEY  |
+--------------+
| `baz`,`zoid` |
+--------------+
1 row in set (0.00 sec)

Two caveats:

  1. This may not work for other keys than primary

  2. If you have very long column names or keys referencing very many columns, you may run into the GROUP_CONCAT cap:

    mysql> SELECT @@session.group_concat_max_len;
+--------------------------------+
| @@session.group_concat_max_len |
+--------------------------------+
|                           1024 |
+--------------------------------+
1 row in set (0.00 sec)

You can update this for the session, if needed, via the usual set statement.

Upvotes: 1

Bill Karwin
Bill Karwin

Reputation: 562280

mysql> SHOW COLUMNS FROM foo WHERE `Key` = 'PRI';
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id    | int(11) | NO   | PRI | NULL    |       |
+-------+---------+------+-----+---------+-------+

Now just read the names of your primary key column(s) in the Field column.

One weird aspect of this is that Key must be in back-ticks. In spite of the fact that normally column names are case-insensitive in MySQL, it doesn't work unless you delimit Key in this case.

Upvotes: 1

Related Questions