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

The above query provides the constraints on a given table, but it is often useful to know which columns in particular on the table have constraints. This is easily done by joining the ALL_CONS_COLUMNS view to the ALL_CONSTRAINTS view.

displayConstraintsOnTableColumns.sql

set linesize 180
set verify off
accept tableName prompt "Table Name: "
SELECT c.constraint_name, c.constraint_type, c.r_constraint_name,
       c.table_name, cc.column_name, cc.position, c.search_condition
  FROM all_constraints c, all_cons_columns cc
 WHERE c.table_name = '&tableName'
   AND c.constraint_name = cc.constraint_name;

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.