Skip to content

How to know the primary key column in Oracle?

In this quick tutorial we will see how to get the primary key column in the table. Here we can use the below table to list the columns

  • ALL_CONSTRAINTS – table to describes constraint definitions in the database.
  • ALL_CONS_COLUMNS – describes columns that are accessible to the current user and that are specified in constraints.

Syntax

SELECT cols.table_name, cols.column_name, cols.position, 
cons.status, cons.owner
FROM all_constraints || user_constraints cons, all_cons_columns cols
WHERE cols.table_name = TABLE_NAME
AND cons.constraint_type = 'P'
AND cons.constraint_name = cols.constraint_name
AND cons.owner = cols.owner
ORDER BY cols.table_name, cols.position;

The constraint type ‘P’ refer for primary key, as like this the oracle has the key for every constraint type some are belows,

TypeDefinition
CCheck constraint on a table
PPrimary key
UUnique key
RReferential integrity
VWith check option, on a view
OWith read only, on a view
HHash expression
FConstraint that involves a REF column
SSupplemental logging

The below code snippet with sample query to find the primary key column in table.

SELECT cols.table_name, cols.column_name, cols.position, cons.status, 
cons.owner, cons.constraint_type
FROM ALL_CONSTRAINTS cons, all_cons_columns cols
WHERE cols.table_name = UPPER('templates')
AND cons.constraint_type = 'P'
AND cons.constraint_name = cols.constraint_name
AND cons.owner = cols.owner
ORDER BY cols.table_name, cols.position;

Result

To list the constraint type of all columns in a table you can use the below query

SELECT cols.table_name, cols.column_name, cols.position, cons.status, 
cons.owner, cons.constraint_type
FROM ALL_CONSTRAINTS cons, all_cons_columns cols
WHERE cols.table_name = UPPER('templates')
AND cons.constraint_name = cols.constraint_name
AND cons.owner = cols.owner
ORDER BY cols.table_name, cols.position;

Result

Happy Learning 🙂

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.