SQL Zone is brought to you in partnership with:

Dustin Marx is a software developer who enjoys identifying and using the correct tool for the job. In addition to writing software and writing a blog on software development, Dustin occasionally presents at conferences and writes articles. Dustin is a DZone MVB and is not an employee of DZone and has posted 235 posts at DZone. You can read more from them at their website. View Full User Profile

Quickly Viewing Oracle Database Constraints

06.20.2012
| 2595 views |
  • submit to reddit

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.

 

 

 

 

 

Published at DZone with permission of Dustin Marx, author and DZone MVB. (source)

(Note: Opinions expressed in this article and its replies are the opinions of their respective authors and not those of DZone, Inc.)