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

select * from all_constraints
where owner = 'USERS'
and constraint_name = 'SYS_C00381400'
/

This construction indicates a system generated constraint name. For instance, if we specify NOT NULL in a table declaration. Or indeed a primary or unique key. For example:

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

Table 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>