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 = '<NAME>'
and constraint_name = 'SYS_C00381400'
/

Like all data dictionary views, this a USER_CONSTRAINTS view if you just want to check your current schema and a DBA_CONSTRAINTS view for administration users.

The construction of the constraint name 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>

'C' for check, 'P' for primary.

Generally it's a good idea to give relational constraints an explicit name. For instance, if the database creates an index for the primary key (which it will do if that column is not already indexed) it will use the constraint name oo name the index. You don't want a database full of indexes named like SYS_C00935191.

To be honest most people don't bother naming NOT NULL constraints.