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
Add comment October 28th, 2008
