CODEWITHSUNDEEP
sql
Mołot
Mołot

Reputation: 709

What is universal SQL way to obtain names of columns in Primary Key?

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

Upvotes: 4

Views: 352

Answers (4)

Colin Raybould
Colin Raybould

Reputation: 1

Not using SQL, but the ODBC and JDBC APIs have this function. Other database driver APIs should offer similar functionality.

For ODBC use SQLPrimaryKeys and for JDBC use databaseMetaData.getPrimaryKeys

Try re-tagging your question with the driver API that you will be using to get better answers.

Upvotes: 0

T I
T I

Reputation: 9933

I agree there is no truly universal way for all databases, you could try using INFORMATION_SCHEMA which should get you someway.

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'

Upvotes: 2

vc 74
vc 74

Reputation: 38179

No, there is no such thing. Each DBMS has its own way to query the metadata.

Upvotes: 1

paxdiablo
paxdiablo

Reputation: 881323

No, there's no universal way.

Each RDBMS has its own metadata tables which holds thing like the schema details (table names, column names and so on).

For example, DB2 has a host of tables in the SYSIBM schema such as SYSIBM.SYSCOLUMNS. In fact, I believe this may differ even between some platforms (like DB2/LUW and DB2/z).

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

That means making your code configurable to use different methods based on the target DBMS.

Upvotes: 1

Related Questions