Display names of all constraints for a table in Oracle SQL

Question

I have defined a name for each of the constraint for the multiple tables that I have created in Oracle SQL.

The problem is that to drop a constraint for the column of a particular table I need to know the name that I have supplied for each constraints, which I have forgotten.

How do I list out all the names of constraints that I have specified for each column of a table?

Is there any SQL statement for doing so?

Solution 1

You need to query the data dictionary, specifically the USER_CONS_COLUMNS view to see the table columns and corresponding constraints:

SELECT *
  FROM user_cons_columns
 WHERE table_name = '<your table name>';

FYI, unless you specifically created your table with a lower case name (using double quotes) then the table name will be defaulted to upper case so ensure it is so in your query.

If you then wish to see more information about the constraint itself query the USER_CONSTRAINTS view:

SELECT *
  FROM user_constraints
 WHERE table_name = '<your table name>'
   AND constraint_name = '<your constraint name>';

If the table is held in a schema that is not your default schema then you might need to replace the views with:

all_cons_columns

and

all_constraints

adding to the where clause:

   AND owner = '<schema owner of the table>'

Solution 2

SELECT * FROM USER_CONSTRAINTS

Solution 3

maybe this can help:

SELECT constraint_name, constraint_type, column_name
from user_constraints natural join user_cons_columns
where table_name = "my_table_name";

cheers

Solution 4

select constraint_name,constraint_type 
from user_constraints
where table_name = 'YOUR TABLE NAME';

note: table name should be in caps.

In case you don't know the name of the table then,

select constraint_name,constraint_type,table_name 
from user_constraints;

Solution 5

Often enterprise databases have several users and I'm not aways on the right one :

SELECT * FROM ALL_CONSTRAINTS WHERE table_name = 'YOUR TABLE NAME' ;

Picked from Oracle documentation

View more solutions

Comments

[Paul Draper]

Typo: USER_CONS_COLUMNS

[Jasper de Vries]

This won't get you the column name

[Kanagavelu Sugumar]

<your table name> is case sensitive, I think; It should be in upper case.

[Sandip Nirmal]

Use all_constraints

[David Fletcher]

The owner field in both (user|all|dba)_constraints and (user|all|dba)_cons_columns is the owner of the constraint, not the owner of the table (per Oracle documentation). The table owner is not an available field in either of these views. Does this mean that the constraint owner and the table owner must be the same?