The USER_CONS_COLUMNS table in Oracle stores detailed information about the columns that are part of constraints in the user's schema. This table allows you to view which columns are involved in specific constraints (e.g., primary keys, foreign keys, unique constraints, check constraints) and their positions in the constraint (for composite keys).

Columns in USER_CONS_COLUMNS Table

Example Usage of USER_CONS_COLUMNS

Let’s assume the following constraints exist on the EMPLOYEES table:

A Primary Key constraint on the ID column.

A Foreign Key constraint on the DEPARTMENT_ID column.

A Unique constraint on the EMAIL column.

You can query the USER_CONS_COLUMNS table to get details about the columns involved in these constraints:

SELECT
CONSTRAINT_NAME,
TABLE_NAME,
COLUMN_NAME,
POSITION
FROM
USER_CONS_COLUMNS
WHERE
TABLE_NAME = 'EMPLOYEES';

Sample Output

Explanation of Output

EMP_PK: This is the Primary Key constraint, and the column ID is part of this constraint.

EMP_DEPT_FK: This is the Foreign Key constraint, with DEPARTMENT_ID as the referenced column.

EMP_EMAIL_UK: This is a Unique constraint on the EMAIL column.

POSITION: Since these constraints involve a single column, the POSITION is 1. If there were composite constraints (involving multiple columns), the POSITION would show the column's order in that constraint.

How to Interpret the Results

The POSITION column helps identify the order of columns in a composite constraint (such as a composite primary or foreign key).

CONSTRAINT_NAME provides the name of the constraint that the column is part of.

The TABLE_NAME and COLUMN_NAME show which table and column the constraint is associated with.

This information is very useful when you need to inspect or manage constraints in a table, especially for foreign keys and composite constraints.