What is a generic SQL method for retrieving column names in a Primary Key?

Question

I do not know if my code will work on MySQL, PostgreSQL, MS SQL, IBM db2, Oracle or what. So, is there a universal way to identify primary keys on a table? Or at least the way that works for 3 or 4 RDBMS or is described in some standard file, so I can say that my code works for standard cases?

Answer 1

I agree that for all databases there is no universal way, you can try using INFORMATION_SCHEMA , which should get you somehow.

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' 

Answer 2

No, there is no universal way.

Each RDBMS has its own metadata tables that contain things like schema details (table names, column names, etc.).

For example, DB2 has many tables in the SYSIBM , for example SYSIBM.SYSCOLUMNS . In fact, I believe that this may vary even between some platforms (such as DB2 / LUW and DB2 / z).

You just need to do what we all do, I'm afraid :-)

This means that your code is configured to use different methods based on the target DBMS.

Answer 3

No, this does not happen. Each DBMS has its own way of requesting metadata.

Answer 4

Do not use SQL, but the ODBC and JDBC APIs have this feature. Other database driver APIs should offer similar features.

For ODBC use SQLPrimaryKeys and for JDBC use databaseMetaData.getPrimaryKeys

Try marking your question again with the driver API, which you will use to get the best answers.