For getting the information of a primary key, we have to join two dictionary views, one is ALL_CONS_COLUMNS, the other is ALL_CONSTRAINTS.
And then we use the following SQL statement to retrieve the primary key information of a table. For example, we'd like to check the primary key information of HR.JOB_HISTORY as followings.
SQL> column CONSTRAINT_NAME format a30;
SQL> column POSITION format 99;
SQL> column COLUMN_NAME format a30;
SQL> select a.constraint_name, a.position, a.column_name from all_cons_columns a inner join all_constraints b on a.constraint_name = b.constraint_name where a.owner = 'HR' and a.table_name = 'JOB_HISTORY' and b.constraint_type = 'P' order by 1,2;
CONSTRAINT_NAME POSITION COLUMN_NAME
------------------------------ -------- ------------------------------
JHIST_EMP_ID_ST_DATE_PK 1 EMPLOYEE_ID
JHIST_EMP_ID_ST_DATE_PK 2 START_DATE
Please note that, "P" in CONSTRAINT TYPE stands for "Primary Key". Let's quickly review types of the constraint definition:
- C - Check constraint on a table
- P - Primary key
- U - Unique key
- R - Referential integrity
- V - With check option, on a view
- O - With read only, on a view
- H - Hash expression
- F - Constraint that involves a REF column
- S - Supplemental logging
In the above case, the primary key is combined with two columns, one is EMPLOYEE_ID, the other is START_DATE.
In some particular situations, you may also use DBA_CONS_COLUMNS and DBA_CONSTRAINTS instead.