Find if a column has unique constraint

Question

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?

Accepted Answer

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.

Find if a column has unique constraint

Solution

Problem

Related problems