RDBMS-independent way to get table primary key? Let's assume we have Drupal instance with second database connection. Tables in that second database are shared between many systems and thus was not created using Drupal's schema functionality. How do I get the name of the primary key, in a universal way that doesn't dependent on the RDBMS? Is there a way that will be equally good for all databases?

For MySQL I can use db_query and:

SHOW INDEX FROM {table} WHERE Key_name = "PRIMARY"


and for SQL-92 compatible ones:

SELECT pk.TABLE_NAME, c.COLUMN_NAME primary_key
  FROM information_schema.table_constraints pk 
  JOIN information_schema.key_column_usage c
    ON c.table_name = pk.table_name 
   AND c.constraint_name = pk.constraint_name
 WHERE constraint_type = 'primary key'


Thanks to T I's answer on stackoverflow.

What happens with Oracle or Apache Derby? I know I can find specific solutions, but I wonder if there is a Drupal way. I couldn't find one outside schema.
For non-Drupal tables

I don't think there is a cross-db drupal way to do this currently.

It would have to be added to the database schema class for each database type.
For example, see the mysql database schema class and look at something like addIndex.

Something similar could be done for showIndex().
If it is added for all the different database handlers, it would then be able to be safely used.

Then a database API function would have to be added for easy calling.

For example you would need a db_show_index() function in database.inc like db_add_index().

For Drupal defined tables

You can use drupal_get_schema() and get the schema for a table; then, get what you need from that. It will return a schema as defined in hook_schema().

You might also be interested in some of the other schema API functions.

The schema will only be what was defined in hook_schema though. It won't pick up anything that has been added or changed outside of that.