Techspace

Query to find the Primary Key of a table

This is the simple query which I use to find the primary key of a table. Not a rocket science. But simple and handy.
Inner query version

SELECT TABLE_NAME, COLUMN_NAME PRIMARY_KEY,CONSTRAINT_NAME,OWNER 
FROM USER_CONS_COLUMNS UCC WHERE 
   UCC.CONSTRAINT_NAME=(
   			SELECT CONSTRAINT_NAME FROM USER_CONSTRAINTS UC WHERE UPPER(UC.TABLE_NAME)=UPPER('ADDRESS') AND UC.CONSTRAINT_TYPE='P'
   	)

Or Join Version

SELECT UCC.TABLE_NAME, UCC.COLUMN_NAME PRIMARY_KEY, UCC.CONSTRAINT_NAME,UCC.OWNER
FROM USER_CONS_COLUMNS UCC,USER_CONSTRAINTS UC
 WHERE UCC.CONSTRAINT_NAME= UC.CONSTRAINT_NAME
AND UPPER(UC.TABLE_NAME)=UPPER('ADDRESS')
AND UC.CONSTRAINT_TYPE='P'

where ‘ADDRESS’ is the name of the table

October 28, 2008 - Posted by Paras | Oracle | Oracle, primary key, query, query to find primary key of a table