get constraint name by column name in oracle database

Question

How to get unique constraint_name of The corresponding column in oracle database?

in other words I want to get constraint name by column name.

pseudo query for this question:

select constraint_name from table_name tname where tname.column_name = 'columnName';

is this possible?

Almost a duplicate of [How to fetch the system generated check constraint name of table column in oracle](http://stackoverflow.com/q/34932419/1509264) but with the added restriction of only getting `UNIQUE` constraints. — MT0, Apr 09 '17 at 09:23

score 7 · Answer 1 · answered Apr 09 '17 at 07:29

7

Try this.

select CONSTRAINT_NAME from  USER_CONS_COLUMNS
where table_name='YOUR_TABLE' 
and column_name='YOUR_COLUMN'

answered Apr 09 '17 at 07:29

Utsav

7,914
2
17
38

This will get all constraints for the column - not just the `UNIQUE` constraints. – MT0 Apr 09 '17 at 09:23
thanks a lot , but as I say,I want get the only 'unique' constraint name, – Behrouz Takhti Apr 15 '17 at 05:07

MT0 · Accepted Answer · 2017-04-09T09:12:27.867

Use the Oracle dictionary views ALL_CONS_COLUMNS or USER_CONS_COLUMNS (if you want to restrict it by column type - i.e. unique or primary key constraints - then you can join in ALL_CONSTRAINTS or USER_CONSTRAINTS):

SELECT acc.constraint_name
FROM   ALL_CONS_COLUMNS acc
       INNER JOIN ALL_CONSTRAINTS ac
       ON ( acc.CONSTRAINT_NAME = ac.CONSTRAINT_NAME )
WHERE  ac.OWNER        = 'YOUR_SCHEMA_NAME'
AND    ac.TABLE_NAME   = 'YOUR_TABLE_NAME'
AND    acc.COLUMN_NAME = 'YOUR_COLUMN_NAME'
AND    ac.CONSTRAINT_TYPE IN ( 'U', 'P' )    -- Unique or primary key constraints

or:

SELECT ucc.constraint_name
FROM   USER_CONS_COLUMNS ucc
       INNER JOIN ALL_CONSTRAINTS uc
       ON ( ucc.CONSTRAINT_NAME = uc.CONSTRAINT_NAME )
WHERE  uc.OWNER        = 'YOUR_SCHEMA_NAME'
AND    uc.TABLE_NAME   = 'YOUR_TABLE_NAME'
AND    ucc.COLUMN_NAME = 'YOUR_COLUMN_NAME'
AND    uc.CONSTRAINT_TYPE IN ( 'U', 'P' )      -- Unique or primary key constraints

get constraint name by column name in oracle database

2 Answers2