Oracle Find a Constraint

Oracle find a constraint

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.

get constraint name by column name in oracle database

Use the Oracle dictionary views ALL_CONS_COLUMNS or USER_CONS_COLUMNS (if you want to restrict it by column type - i.e. unique or primary key constraints - then you can join in ALL_CONSTRAINTS or USER_CONSTRAINTS):

SELECT acc.constraint_name
FROM   ALL_CONS_COLUMNS acc
       INNER JOIN ALL_CONSTRAINTS ac
       ON ( acc.CONSTRAINT_NAME = ac.CONSTRAINT_NAME )
WHERE  ac.OWNER        = 'YOUR_SCHEMA_NAME'
AND    ac.TABLE_NAME   = 'YOUR_TABLE_NAME'
AND    acc.COLUMN_NAME = 'YOUR_COLUMN_NAME'
AND    ac.CONSTRAINT_TYPE IN ( 'U', 'P' )    -- Unique or primary key constraints

or:

SELECT ucc.constraint_name
FROM   USER_CONS_COLUMNS ucc
       INNER JOIN ALL_CONSTRAINTS uc
       ON ( ucc.CONSTRAINT_NAME = uc.CONSTRAINT_NAME )
WHERE  uc.OWNER        = 'YOUR_SCHEMA_NAME'
AND    uc.TABLE_NAME   = 'YOUR_TABLE_NAME'
AND    ucc.COLUMN_NAME = 'YOUR_COLUMN_NAME'
AND    uc.CONSTRAINT_TYPE IN ( 'U', 'P' )      -- Unique or primary key constraints

Oracle: get all constraints/columns (primary and foreign keys)

Join the constraint column view to the pk constraint view:

SELECT a.table_name
     , a.column_name
     , a.constraint_name
     , c.owner
     , c.r_owner
     , c_pk.table_name      r_table_name
     , c_pk.constraint_name r_pk
     , cc_pk.column_name    r_column_name
  FROM all_cons_columns a
  JOIN all_constraints  c       ON (a.owner                 = c.owner                   AND a.constraint_name   = c.constraint_name     )
  JOIN all_constraints  c_pk    ON (c.r_owner               = c_pk.owner                AND c.r_constraint_name = c_pk.constraint_name  )
  JOIN all_cons_columns cc_pk   on (cc_pk.constraint_name   = c_pk.constraint_name      AND cc_pk.owner         = c_pk.owner            )
 WHERE a.owner = 'BRANCH_DEV'
   AND a.table_name IN ( 'table1','table2' )
     ;

Display names of all constraints for a table in Oracle SQL

You need to query the data dictionary, specifically the USER_CONS_COLUMNS view to see the table columns and corresponding constraints:

SELECT *
  FROM user_cons_columns
 WHERE table_name = '<your table name>';

FYI, unless you specifically created your table with a lower case name (using double quotes) then the table name will be defaulted to upper case so ensure it is so in your query.

If you then wish to see more information about the constraint itself query the USER_CONSTRAINTS view:

SELECT *
  FROM user_constraints
 WHERE table_name = '<your table name>'
   AND constraint_name = '<your constraint name>';

If the table is held in a 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>'

Get table name by constraint name

SELECT owner, table_name
  FROM dba_constraints
 WHERE constraint_name = <<your constraint name>>

will give you the name of the table. If you don't have access to the DBA_CONSTRAINTS view, ALL_CONSTRAINTS or USER_CONSTRAINTS should work as well.

How to get list of all constraints present in a table in Oracle?

You can use the JOIN on two tables USER_CONSTRAINTS and USER_CONS_COLUMNS to fetch the Column Name, Constraint Type and Table Name.

SELECT ucc.COLUMN_NAME, uc.CONSTRAINT_TYPE ,uc.TABLE_NAME 
FROM USER_CONSTRAINTS uc  JOIN  USER_CONS_COLUMNS ucc 
ON uc.CONSTRAINT_NAME = ucc.CONSTRAINT_NAME 
AND uc.TABLE_NAME=ucc.TABLE_NAME
WHERE uc.TABLE_NAME='mytablename';

Constraint Definition can be referred as:

C (check constraint on a table)
P (primary key)
U (unique key)
R (referential integrity)
V (with check option, on a view)
O (with read only, on a view)

For more information you can view Oracle Documentation here

Cant find constraint in oracle

Be sure that the table has been created in the same schema you're using to execute the query. You can also check ALL_CONSTRAINTS table.
Also, by defaults tables names are saved with only uppercase characters and string comparisons in Oracle are case sensitive. If you did everything right this one should do the trick.

SELECT * FROM user_constraints where TABLE_NAME = UPPER('MyTable');

How do I find the definition of a named constraint in Oracle?

Another option would be to reverse engineer the DDL...

DBMS_METADATA.GET_DDL('CONSTRAINT', 'SYS_C003415')

Some examples here....

http://www.psoug.org/reference/dbms_metadata.html

Oracle: How to get all possible constraint violations?

I have found a simple solution for me:

spool 'disabled_constraint_violations.txt';
set serverout on size unlimited
DECLARE
    sql_enable varchar2(400);
    sql_disable varchar2(400);
BEGIN
   FOR c IN (select OWNER, TABLE_NAME, CONSTRAINT_NAME from all_constraints  where STATUS='DISABLED') LOOP
        BEGIN
        sql_enable := ' alter table ' ||  c.owner||'.'||c.table_name || ' enable constraint ' || c.constraint_name;
        execute immediate  sql_enable ;
        sql_disable := ' alter table ' ||  c.owner||'.'||c.table_name || ' disable constraint ' || c.constraint_name;
        execute immediate  sql_disable ;
        EXCEPTION   
            WHEN OTHERS THEN
                dbms_output.put_line('table: '||c.table_name||'    ||    constraint:  '||c.constraint_name||'    ||    error message:  '||sqlerrm);
        END;
   END LOOP;
END;
set serveroutput off
spool off

How to find all foreign key constraints in oracle?

I would do it like that ;

If you want it aggregated with all column on the same row

select distinct c1.owner, c1.table_name, c1.constraint_name, c2.owner, c2.table_name, 
listagg(c2.column_name,',') WITHIN GROUP ( ORDER BY C2.POSITION) OVER ( PARTITION BY c1.owner, c1.table_name, c1.constraint_name, c2.owner, c2.table_name ) column_list
from dba_constraints c1
JOIN dba_cons_columns c2
ON c1.R_CONSTRAINT_NAME=C2.CONSTRAINT_NAME and c1.r_owner=c2.owner
where C1.constraint_type = 'R'

or like that if you want one row per column

select  c1.owner, c1.table_name, c1.constraint_name, c2.owner, c2.table_name, c2.column_name
from dba_constraints c1
JOIN dba_cons_columns c2
ON c1.R_CONSTRAINT_NAME=C2.CONSTRAINT_NAME and c1.r_owner=c2.owner
where C1.constraint_type = 'R'
order by c1.owner, c1.table_name, c1.constraint_name, c2.position

or if you want referencing column details AND referenced columns details

with constraint_colum_list as ( select owner, table_name, constraint_name, listagg(column_name,',') WITHIN GROUP ( order by position ) as column_list
                                FROM DBA_CONS_COLUMNS GROUP BY owner, table_name, constraint_name )
select distinct c1.owner, c1.table_name, c1.constraint_name, c2.column_list, c3.owner, c3.table_name, c3.constraint_name, c3.column_list
from DBA_constraints c1
JOIN constraint_colum_list c2 ON c1.CONSTRAINT_NAME=C2.CONSTRAINT_NAME and c1.owner=c2.owner
JOIN constraint_colum_list c3 ON C1.R_CONSTRAINT_NAME=C3.CONSTRAINT_NAME AND C1.R_OWNER=C3.owner
where C1.constraint_type = 'R' 
-- AND c1.owner = 'YOUR_SCHEMA';

---

Related Topics