PLSQL: Retrieve primary key information (2024)

Question: How do I determine if a table has a primary key and if it has one, how do I determine what columns are in the primary key?

Answer: You can retrieve primary key information with the following SQL statement:

SELECT cols.table_name, cols.column_name, cols.position, cons.status, cons.ownerFROM all_constraints cons, all_cons_columns colsWHERE cons.constraint_type = 'P'AND cons.constraint_name = cols.constraint_nameAND cons.owner = cols.ownerORDER BY cols.table_name, cols.position;

If you knew the table name that you were looking for, you could modify the SQL as follows:

SELECT cols.table_name, cols.column_name, cols.position, cons.status, cons.ownerFROM all_constraints cons, all_cons_columns colsWHERE cols.table_name = 'TABLE_NAME'AND cons.constraint_type = 'P'AND cons.constraint_name = cols.constraint_nameAND cons.owner = cols.ownerORDER BY cols.table_name, cols.position;

Make sure to type the table_name in uppercase, as Oracle stores all table names in uppercase.

Let's quickly explain the output from this query.

table_name

The name of the table (stored in uppercase).

column_name

The name of the column that is a part of the primary key. (also stored in uppercase)

position

The position in the primary key. A primary key can contain more than one column, so understanding the order of the columns in the primary key is very important.

status

Indicates whether the primary key is currently enabled or disabled.

owner

Indicates the schema that owns the table.