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,
| Type | 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 |
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 🙂
