Display names of all constraints for a table in Oracle SQL

Question

I have defined a name for each of the constraints 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 constraint, 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?

Answer 1

You have to query the data dictionary, specially the USER_CONS_COLUMNS view to see the table columns and the corresponding constraints like this:

SELECT *

FROM user_cons_columns

WHERE table_name = '<your table name>';

Unless you are creating the table with the lower case name (by using double quotes) then the table name must be defaulted to upper case hence ensure it is so in the query.

But, if you wish to see more information about the constraint itself query the USER_CONSTRAINTS view like this:

SELECT *

  FROM user_constraints

 WHERE table_name = '<your table name>'

   AND constraint_name = '<your constraint name>';

But, if the table is held in the 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>'

If you wish to learn more about tables in SQL then visit this blog on SQL Table.

You can master these queries and become proficient in SQL queries by enrolling in an industry-recognized SQL certification.

Answer 2

If we want to determine the restrictions or constraints placed on each and every column within an Oracle database, we have to refer to two special tables, that is, USER_CONSTRAINTS and USER_CONS_COLUMNS. The very first one, USER_CONSTRAINTS, explains in detail all the boundaries and regulations. The second table known as USER_CONS_COLUMNS explains to us which of these affected rules applies to which columns. Therefore we can bring all these into one description of the constraints for every single column.

If you want to implement follow this:

SELECT 

    uc.CONSTRAINT_NAME, 

    uc.CONSTRAINT_TYPE, 

    ucc.COLUMN_NAME, 

    uc.TABLE_NAME

FROM 

    USER_CONSTRAINTS uc

JOIN 

    USER_CONS_COLUMNS ucc 

ON 

    uc.CONSTRAINT_NAME = ucc.CONSTRAINT_NAME

WHERE 

    uc.TABLE_NAME = 'YOUR_TABLE_NAME';