Oracle find a constraint

Question

I have a constraint called users.SYS_C00381400. How do I find what that constraint is? Is there a way to query all constraints?

Answer 1

Use the below code:

select * from all_constraints

where owner = '<NAME>'

and constraint_name = 'SYS_C00381400'

/

Like all the data dictionary views, the above code is the USER_CONSTRAINTS view but if you want to check your current schema and a DBA_CONSTRAINTS view for administration users. Then, the construction of the constraint name indicates the system generated constraint name. 

For example, if you want to specify NOT NULL in a table declaration or indeed a primary or unique key like this:

SQL> create table t23 (id number not null primary key)

  2  /

The table is created.

SQL> select constraint_name, constraint_type

  2  from user_constraints

  3  where table_name = 'T23'

  4  /

CONSTRAINT_NAME                C

------------------------------ -

SYS_C00935190                  C

SYS_C00935191                  P

SQL>

Here, 'C' is used for the check and  'P' for the primary.

Usually, it is a good idea to give relational constraints an explicit name. For example, if the database is creating an index for the primary key (which it will do if that column is not already indexed) then it will use the constraint name o name the index. 

But, you don't want a database full of indexes named SYS_C00935191. Many people don't bother naming NOT NULL constraints as well.