A check constraint is simply an expression that must be true for each row in a table. This is the simplest of the constraint types when it comes to querying the data dictionary tables. The check expression is stored in the SEARCH_CONDITION column of the ALL_CONSTRAINTS table. The following query will get you the definition of all CHECK constraints on a particular table:

SELECT constraint_name, search_condition
  FROM all_constraints
 WHERE owner = 'username'
   AND table_name = 'table_name'
   AND constraint_type ='C'

As you can see, the query is very simple. You don’t even have to join any tables. With the other constraint types, the query gets more complex.

Primary key and unique constraints are similar in that they both force each row in a table to have a unique value in one column or combination of columns. The only difference between the two is semantics. When looking at constraints of these two types, you need to include the ALL_CONS_COLUMNS view in your query in order to get a list of the columns involved. The following query shows how to do this:

SELECT ac.constraint_name, ac.constraint_type, 
       ac.table_name, acc.column_name
  FROM all_constraints ac,
       all_cons_columns acc
 WHERE ac.owner = 'username'
   AND ac.table_name = 'table_name'
   AND ac.constraint_type in ('P','U')
   AND ac.constraint_name = acc.constraint_name
   AND ac.owner = acc.owner
ORDER BY ac.constraint_name, acc.position

Ordering the columns in the constraint definition by the POSITION column is done so the output matches the column order used when originally defining a constraint. Oracle enforces unique and primary key constraints by creating unique indexes. The column order used when creating the indexes will match that used in defining the constraints, and can affect the performance of queries issued against the table.

Foreign key constraints are the most complex. A foreign key defines a list of columns in one table, called the child table, that correlates to either a primary key or a unique constraint on a parent table. When a row is inserted into the child table, Oracle checks to be sure a corresponding parent record exists. Foreign key constraints involve two lists of columns, one in the child table on which the constraint is defined, and another in the parent table.

The trick with foreign key constraints is to first find the name of the parent table, then find the names of the columns in the parent table that correspond to the columns in the child table. The key to doing this is to use the R_OWNER and R_CONSTRAINT_NAME columns in the ALL_CONSTRAINTS table. The constraint type code for foreign key constraints is “R”. A foreign key always relates to either a primary key constraint or a unique constraint on the parent table. The name of this related constraint will be in the R_CONSTRAINT_NAME column. Usually, the R_OWNER column will match the OWNER column, but that doesn’t have to be the case.

To see the definition of all the foreign key constraints for a given table, you can start with the query used for primary key constraints, and modify it to look only at constraint type `R’:

SELECT ac.constraint_name, ac.constraint_type, 
       ac.owner, ac.table_name, acc.column_name
  FROM all_constraints ac,
       all_cons_columns acc
 WHERE ac.owner = 'username'
   AND ac.table_name = 'table_name'
   AND ac.constraint_type = 'R'
   AND ac.constraint_name = acc.constraint_name
   AND ac.owner = acc.owner
ORDER BY ac.constraint_name, acc.position

This will give you the constraint name, the table name, and a list of column names. It won’t tell you the name of the parent table or the names of the corresponding columns in that table. For that, you need to join ALL_CONSTRAINTS to itself via the R_CONSTRAINT_NAME and R_OWNER columns. This will give you access to the parent table’s name.

SELECT ac.constraint_name, ac.constraint_type, 
       ac.owner, ac.table_name, acc.column_name,
       r_ac.owner, r_ac.table_name
  FROM all_constraints ac,
       all_cons_columns acc,
       all_constraints r_ac
 WHERE ac.owner = 'username'
   AND ac.table_name = 'table_name'
   AND ac.constraint_type = 'R'
   AND ac.constraint_name = acc.constraint_name
   AND ac.owner = acc.owner
   AND ac.r_owner = r_ac.owner
   AND ac.r_constraint_name = r_ac.constraint_name
ORDER BY ac.constraint_name, acc.position

Since most foreign key constraints relate to the parent table’s primary key, you may want to just stop here. However, since it is possible to relate a foreign key to a unique key on the parent table, you may want see the corresponding list of parent table columns in order to fully understand the constraint. To do this, you must join with ALL_CONS_COLUMNS once again and pick up the columns that go with the related parent table constraint. For example:

SELECT ac.constraint_name, ac.constraint_type, 
       ac.owner, ac.table_name, acc.column_name,
       r_ac.owner, r_ac.table_name,r_acc.column_name
  FROM all_constraints ac,
       all_cons_columns acc,
       all_constraints r_ac,
       all_cons_columns r_acc
 WHERE  ac.owner = 'username'
   AND ac.table_name = 'table_name'
   AND ac.constraint_type = 'R'
   AND ac.owner = acc.owner
   AND ac.constraint_name = acc.constraint_name
   AND ac.r_owner = r_ac.owner
   AND ac.r_constraint_name = r_ac.constraint_name
   AND r_ac.owner = r_acc.owner
   AND r_ac.constraint_name = r_acc.constraint_name
   AND acc.position = r_acc.position
ORDER BY ac.constraint_name, acc.position

Notice that the ALL_CONS_COLUMN table’s POSITION column forms part of the join criteria. This ensures that matching columns are output together, on the same line. You will see how this works in the next section, which presents a script that will list all constraints defined for a table.

--DESCRIPTION
--List all the constraints on a table.
--
--INPUTS
--  Param 1   A table name, optionally qualified
--            by an owner name.
--            For example: "SYS.ALL_TAB_COLUMNS".
--
SET ECHO OFF
SET NEWPAGE 1
SET VERIFY OFF
SET FEEDBACK OFF
SET HEADING OFF
SET PAGESIZE 9999
SET RECSEP OFF
SET CONCAT ON
CLEAR BREAKS
CLEAR COMPUTES
CLEAR COLUMNS
TTITLE OFF
BTITLE OFF

--Dissect the input argument, and get the owner name and
--table name into two seperate substitution variables.
--The owner name defaults to the current user.
DEFINE s_owner_name = ' '
DEFINE s_table_name = ' '
COLUMN owner_name NOPRINT NEW_VALUE s_owner_name
COLUMN table_name NOPRINT NEW_VALUE s_table_name
SELECT
  DECODE(INSTR('&&1','.'),
         0,USER,  /*Default to current user.*/
         UPPER(SUBSTR('&&1',1,INSTR('&&1','.')-1))) owner_name,
  DECODE(INSTR('&&1','.'),
         0,UPPER('&&1'),  /*Only the table name was passed in.*/
         UPPER(SUBSTR('&&1',INSTR('&&1','.')+1))) table_name
  FROM dual;

--Clear column definitions so that no future queries
--affect the substitution variables that were just set.
CLEAR COLUMNS

--Format the columns
COLUMN constraint_name NOPRINT NEW_VALUE constraint_name_var
COLUMN constraint_type_desc NOPRINT NEW_VALUE constraint_type_var
COLUMN r_owner NOPRINT NEW_VALUE r_owner_var
COLUMN r_table_name NOPRINT NEW_VALUE r_table_name_var
COLUMN status NOPRINT NEW_VALUE status_var
COLUMN delete_rule_desc NOPRINT NEW_VALUE delete_rule_var
COLUMN search_condition FORMAT A50 WORD_WRAPPED 
COLUMN column_name FORMAT A30
COLUMN r_column_name FORMAT A30

--This script breaks on each constraint, and each constraint
--has its own page title. The page title shows information 
--relating to the constraint as a whole.
BREAK ON constraint_name SKIP PAGE

--Tell the user he is going to see constraints. 
PROMPT CONSTRAINTS ON TABLE &&s_owner_name..&&s_table_name

--First show the primary key and unique constraints.
--
--Set up the page title. 
TTITLE LEFT '   ' constraint_name_var ' ' constraint_type_var -
       ' ' status_var SKIP 1

--Run the query to display primary key and unique constraints.
SELECT ac.constraint_name,
       DECODE(ac.constraint_type, 
              'P','PRIMARY KEY','U','UNIQUE') constraint_type_desc,
       ' ' table_name,
       '      ' indent,
       acc.column_name,
       ac.status
  FROM all_constraints ac,
       all_cons_columns acc
 WHERE ac.owner='&&s_owner_name'
   AND ac.table_name = '&&s_table_name'
   AND ac.owner = acc.owner
   AND ac.constraint_name = acc.constraint_name
   AND ac.constraint_type IN ('P','U')
ORDER BY DECODE(ac.constraint_type,'P',1,'U',2),
         ac.constraint_name,
         acc.position;

--Then show the foreign key constraints
--
--Set up the page title for the foreign key constraints
TTITLE LEFT '   '  constraint_name_var ' ' status_var SKIP 1 -
       '   FOREIGN KEY TO ' r_owner_var '.' r_table_name_var SKIP 1

--Run the query to show foreign key constraints
SELECT ac.constraint_name,
       '      ' indent, 
       acc.column_name,
       r_acc.owner r_owner, 
       r_acc.table_name r_table_name,
       r_acc.column_name r_column_name
  FROM all_constraints ac,
       all_cons_columns acc,
       all_cons_columns r_acc
 WHERE ac.owner='&&s_owner_name'
   and ac.table_name = '&&s_table_name'
   AND ac.constraint_type = 'R'
   AND ac.owner = acc.owner
   AND ac.constraint_name = acc.constraint_name
   AND ac.r_owner = r_acc.owner
   AND ac.r_constraint_name = r_acc.constraint_name
   AND acc.position = r_acc.position
ORDER BY ac.constraint_name, acc.position;

--Then show the check constraints
--
--Set up the page title for the check constraints
TTITLE LEFT '   '  constraint_name_var ' CHECK ' status_var SKIP 1 

SELECT ac.constraint_name,
       '      ' indent,
       search_condition
  FROM all_constraints ac
 WHERE ac.owner = '&&s_owner_name'
   AND ac.table_name = '&&s_table_name'
   AND ac.constraint_type = 'C';

--Undefine variables and restore settings to their defaults.
UNDEFINE s_owner_name
UNDEFINE s_table_name

SET NEWPAGE 1
SET VERIFY ON
SET FEEDBACK ON
SET HEADING ON
SET PAGESIZE 14
SET RECSEP WRAPPED
CLEAR BREAKS
CLEAR COMPUTES
CLEAR COLUMNS
TTITLE OFF

You can show all the constraints on a table by invoking the LIST_CONSTRAINTS script and providing the table name as the first argument. The following example shows the constraints defined on the PROJECT_HOURS table:

SQL> @list_constraints project_hours


CONSTRAINTS ON TABLE JEFF.PROJECT_HOURS

   PROJECT_HOURS_PK PRIMARY KEY ENABLED
         PROJECT_ID
         EMPLOYEE_ID
         TIME_LOG_DATE

   PROJECT_HOURS_UNIQUE UNIQUE ENABLED
         PROJECT_ID
         EMPLOYEE_ID
         TIME_LOG_DATE
         HOURS_LOGGED
         DOLLARS_CHARGED

   PROJ_HOURS_FKTO_EMPLOYEE ENABLED
   FOREIGN KEY TO JEFF.EMPLOYEE
       EMPLOYEE_ID                    EMPLOYEE_ID

   PROJ_HOURS_FKTO_PROJECT ENABLED
   FOREIGN KEY TO JEFF.PROJECT
       PROJECT_ID                     PROJECT_ID

   HOURS_LOGGED_MUST_BE_INT CHECK ENABLED
       hours_logged = trunc(hours_logged)