Quickly Viewing Oracle Database Constraints

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.