Reputation: 15730
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?
Upvotes: 10
Views: 70246
Reputation: 103
This is useful if you created a unique key with two values. It is difficult to read without this extra info.
SELECT cols.constraint_name, cons.index_name, cols.table_name, cols.column_name, cols.position, cons.status, cons.owner
FROM all_constraints cons, all_cons_columns cols
WHERE cols.table_name = UPPER('table_name')
AND cons.constraint_type = 'U'
AND cons.constraint_name = cols.constraint_name
AND cons.owner = cols.owner
ORDER BY cols.table_name, cols.position;
If you want to read everything this is useful.
SELECT *
FROM all_constraints cons, all_cons_columns cols
WHERE cols.table_name = UPPER('table_name')
AND cons.constraint_type = 'U'
AND cons.constraint_name = cols.constraint_name
AND cons.owner = cols.owner
ORDER BY cols.table_name, cols.position;
Upvotes: 0
Reputation: 41
Here is a query that I just tried. It lists each uniqueness constraint, identified by the index that enforces it, and the columns that are unique:
select x.index_name, c.column_name, c.column_position
from USER_INDEXES x join USER_IND_COLUMNS c
on x.index_name = c.index_name and x.table_name = c.table_name
left join USER_CONSTRAINTS uc
on x.index_name = uc.index_name and x.table_name = uc.table_name
where x.status = 'VALID' and
(x.uniqueness = 'UNIQUE' or
uc.constraint_type = 'U' and uc.status = 'ENABLED' and uc.validated = 'VALIDATED')
and x.table_name='<your table name_in_caps>'
order by x.index_name, c.column_position;
It seems to work for primary keys, unique indexes, and added uniqueness constraints.
Upvotes: 0
Reputation: 2182
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.
Upvotes: 10
Reputation: 2278
select count(*) cnt
from user_constraints
where table_name=your_table_name
and constraint_type='U';
If count = 0 then there is not UNIQUE
constraint else there is UNIQUE
constraint on your table.
Upvotes: 3
Reputation: 22003
for unique constraints you can do something like:
select cons.constraint_type,
all_cols.owner, all_cols.constraint_name,
all_cols.table_name,
all_cols.column_name,
all_cols.position
from all_cons_columns col
inner join all_cons_columns all_cols
on col.owner = all_cols.owner
and col.constraint_name = all_cols.constraint_name
inner join all_constraints cons
on col.owner = cons.owner
and col.constraint_name = cons.constraint_name
where col.owner = 'SCHEMA'
and col.table_name = 'FOO'
and col.column_name = 'ID'
and cons.constraint_type in ('U', 'P')
order by owner, constraint_name, position;
set the owner, table and column of interest and it will show you all constraints that cover that column
Note that this won't show all cases where a unique index exists on a column (as its possible to have a unique index in place without a constraint being present).
example:
SQL> create table foo(id number, id2 number, constraint foo_con unique(id, id2), constraint foo_con2 unique(id));
Table created.
now list all constraints that cover id
:
SQL> col column_name format a20
SQL> col constraint_name format a20
SQL> col table_name format a15
SQL> select cons.constraint_type,
2 all_cols.owner, all_cols.constraint_name,
3 all_cols.table_name,
4 all_cols.column_name,
5 all_cols.position
6 from all_cons_columns col
7 inner join all_cons_columns all_cols
8 on col.owner = all_cols.owner
9 and col.constraint_name = all_cols.constraint_name
10 inner join all_constraints cons
11 on col.owner = cons.owner
12 and col.constraint_name = cons.constraint_name
13 where col.owner = user
14 and col.table_name = 'FOO'
15 and col.column_name = 'ID'
16 and cons.constraint_type in ('U', 'P')
17 order by owner, constraint_name, position;
C OWNER CONSTRAINT_NAME TABLE_NAME COLUMN_NAME POSITION
- ------------------------------ -------------------- --------------- -------------------- ----------
U DTD_TRADE FOO_CON FOO ID 1
U DTD_TRADE FOO_CON FOO ID2 2
U DTD_TRADE FOO_CON2 FOO ID 1
Upvotes: 5