Over a million developers have joined DZone.

Quickly Viewing Oracle Database Constraints

· Java Zone

Discover how AppDynamics steps in to upgrade your performance game and prevent your enterprise from these top 10 Java performance problems, brought to you in partnership with AppDynamics.

When I am working with an Oracle database, I still find myself using SQL*Plus for many quick and dirty database queries. In particular, I often look up constraints in SQL*Plus. In this post, I look at the Oracle database views and queries that I use most to get an idea what constraints I am dealing with.

I have found the two most important views for determining basic database constraints are ALL_CONSTRAINTS (USER_CONSTRAINTS) and ALL_CONS_COLUMNS (or USER_CONS_COLUMNS). In this post, I look at some queries I like to use that take advantage of these views from the Oracle Data Dictionary.

The ALL_CONSTRAINTS view is great for finding basic constraint details. The next SQL*Plus snippet demonstrates this in use.

displayConstraintInfo.sql

set linesize 180  
set verify off  
accept constraintName prompt "Constraint Name: "  
SELECT constraint_name, constraint_type, r_constraint_name, table_name,  
       search_condition  
  FROM all_constraints  
 WHERE constraint_name = '&constraintName';

The above snippet will prompt for a constraint name and then provide some fundamental characteristics of that constraint provided by the ALL_CONSTRAINTS view. One of these characteristics is CONSTRAINT_TYPE, which is one of the following values: 'C' (Check Constraint), 'P' (Primary Key), 'R' (Referential/Foreign Key), 'U' (Unique), 'V' (with check option on a view), 'O' (with read only on a view). The above query requires one to know the constraint name. The next query will show similar information for constraints on a given table.

displayConstraintsOnTable.sql

set linesize 180  
    set verify off  
    accept tableName prompt "Table Name: "  
    SELECT constraint_name, constraint_type, r_constraint_name, table_name,  
           search_condition  
      FROM all_constraints  
     WHERE table_name = '&tableName';  

Another useful query using these two constraints-related views is one that provides information on referential integrity constraints (CONSTRAINT_TYPE of R). In particular, this simple query shows the constraints for a given table that are foreign key constraints and which primary key constraints they depend on.

displayForeignKeyConstraints.sql

set linesize 180  
    set verify off  
    accept tableName prompt "Table Name: "  
    SELECT cf.constraint_name "FOREIGN KEY",  
           cp.constraint_name "DEPENDS ON",  
           cp.table_name, ccp.column_name, ccp.position  
      FROM all_constraints cp, all_cons_columns ccp, all_constraints cf  
     WHERE cp.table_name = '&tableName'  
       AND cp.constraint_name = ccp.constraint_name  
       AND cf.r_constraint_name = cp.constraint_name  
       AND cf.r_constraint_name = ccp.constraint_name;  

In this post I've summarized some of the useful queries one can construct from the Oracle Data Dictionary views ALL_CONSTRAINTS and ALL_USER_CONS_COLUMNS.

 

 

 

 

 

The Java Zone is brought to you in partnership with AppDynamics. AppDynamics helps you gain the fundamentals behind application performance, and implement best practices so you can proactively analyze and act on performance problems as they arise, and more specifically with your Java applications. Start a Free Trial.

Topics:

Published at DZone with permission of Dustin Marx , DZone MVB .

Opinions expressed by DZone contributors are their own.

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}