Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Oracle find a constraint

People also ask

Where can I find constraints in Oracle?

Constraint names are stored in ALL_CONSTRAINTS table. The column names on which constraints are defined can be found in ALL_CONS_COLUMNS.

How do you find constraints in database?

Discussion: Use the view table_constraints in the information_schema schema. The column table_name gives you the name of the table in which the constraint is defined, and the column constraint_name contains the name of the constraint.

How do I view constraints on a SQL Developer table?

To open designer select table, right click and choose Edit... from context menu. In the designer dialog select Constraints option. Again, list includes all table constraints and you will distinguish FKs by Type column.


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.


To get a more detailed description (which table/column references which table/column) you can run the following query:

SELECT   uc.constraint_name||CHR(10)
   ||      '('||ucc1.TABLE_NAME||'.'||ucc1.column_name||')' constraint_source
   ,       'REFERENCES'||CHR(10)
   ||      '('||ucc2.TABLE_NAME||'.'||ucc2.column_name||')' references_column
FROM user_constraints uc ,
  user_cons_columns ucc1 ,
  user_cons_columns ucc2
WHERE uc.constraint_name = ucc1.constraint_name
AND uc.r_constraint_name = ucc2.constraint_name
AND ucc1.POSITION        = ucc2.POSITION -- Correction for multiple column primary keys.
AND uc.constraint_type   = 'R'
AND uc.constraint_name   = 'SYS_C00381400'
ORDER BY ucc1.TABLE_NAME ,
  uc.constraint_name;

From here.


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";