Search code examples
sqloracle

Find if a column has unique constraint

In a hypothetical scenario, I am an user with no table creation privileges. I want to know if a column in a table has UNIQUE CONSTRAINT. Is it possible to look it up in the DICTIONARY? How would I go about it?

Solution

  • Both answers given here miss one way to enforce uniqueness on a column: by creating a unique index (without defining a unique constraint on the column). See these two links (one, two) if you are not familiar with this option.

    This check should be performed additionally to the unique constraint check:

    select count(*) from
USER_IND_COLUMNS cols
where cols.table_name='YOUR_TABLE_NAME'
and cols.COLUMN_NAME='YOUR_COLUMN';

    To check for a unique constraint use the already provided method:

    select count(*) cnt 
from user_constraints uc
where uc.table_name='YOUR_TABLE_NAME'
and uc.constraint_type='U';

    Alternatively you can also look in the ALL_CONSTRAINTS and ALL_IND_COLUMNS views.