Determine MySQL Table’s Primary Key Dynamically

You may have been in a situation which you may need to figure out what is the PRIMARY key field of a given table. Or even if it is a compound key what are the columns building the PRIMARY key.
This usually helps a lot when you’re making some generic tools to do some jobs on a given table.

The Script is fairly easy, the following SQL command returns back all the INDEXes of a given Table :

SHOW INDEX FROM `tbl_name`

As you will see in the result, all the indexes are returned including the PRIMARY ones. The only concern is if the PRIMARY KEY is a compound key it has one entry in the list for each column.

Table           Non_unique  Key_name      Seq_in_index  Column_name 
---------------------------------------------------------------------
test_table      0           PRIMARY       1             Config_ID   
test_table      0           PRIMARY       2             User_ID     
test_table      1           idxSettingID  1             User_ID

So you can easily loop through the results and find the compound keys.

Here is a sample Python script which does the same thing, using the  List Comprehension :

if cursor.execute('SHOW INDEX FROM `test_table`') :
    return  [row[4] for row in cursor.fetchall() if row[2].upper() == 'PRIMARY']
return []

Update – 18.Oct.2011 :

Also you can use the following SQL to get information about the table and loop through it in Python :


DESCRIBE `tbl_name`

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s