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;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;