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

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;

Other useful queries using these two constraints-related views are those that provide information on referential integrity constraints (CONSTRAINT_TYPE of R). In particular, the new two simple queries show the constraints for a given table that are foreign key constraints and which primary key constraints they depend on. The scripts only differ in substance in which version of the table_name is provided to the script (the primary or the referencing table). The majority of these scripts are exactly the same other than the comment, the prompt for the table name, and the all_constraints.table_name that is joined to the provided table name. I also use the UPPER() function in this example so that the input table_name could be provided in any case. Because I always have my tables in all uppercase, this will always work for me. Anyone who uses case sensitivity with their table names should not do this. I could have used UPPER() in my above examples as well.

displayForeignKeyConstraintsForPrimaryTable.sql

-- displayForeignKeyConstraintsForPrimaryTable.sql
--
-- Display the foreign key dependencies on a provided
-- PRIMARY table. In other words, show tables and their
-- foreign key constraints that reference the table whose
-- name is provided to the script.
--
set linesize 180
set verify off
column "PRIMARY COLUMN" format a25
accept tableName prompt "PRIMARY Table Name: "
SELECT cf.table_name "FK TABLE", 
       cf.constraint_name "FOREIGN KEY",
       cp.constraint_name "DEPENDS ON",
       cp.table_name "PK TABLE",
       ccp.column_name "PRIMARY COLUMN",
       ccp.position
  FROM all_constraints cp, all_cons_columns ccp, all_constraints cf
 WHERE cp.table_name = UPPER('&tableName')
   AND cp.constraint_name = ccp.constraint_name
   AND cf.r_constraint_name = cp.constraint_name
   AND cf.r_constraint_name = ccp.constraint_name;

displayForeignKeyConstraintsForReferencingTable.sql

-- displayForeignKeyConstraintsForReferencingTable.sql
--
-- Display the foreign key dependencies of a provided
-- table on other tables. In other words, show tables
-- upon which the provided table has foreign key
-- constraints.
--
set linesize 180
set verify off
column "REFERENCING COLUMN" format a25
accept tableName prompt "REFERENCING Table Name: "
SELECT cf.table_name "FK TABLE", 
       cf.constraint_name "FOREIGN KEY",
       cp.constraint_name "DEPENDS ON",
       cp.table_name "PK TABLE",
       ccp.column_name "PRIMARY COLUMN",
       ccp.position
  FROM all_constraints cp, all_cons_columns ccp, all_constraints cf
 WHERE cf.table_name = UPPER('&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.