How to Check Primary Key of a Table

  • by

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 quick review the type 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.

Leave a Reply

Your email address will not be published. Required fields are marked *