Oracle7 Server Application Developer's Guide

Listing Integrity Constraint Definitions

The data dictionary contains the following views that relate to integrity constraints:

ALL_CONSTRAINTS

ALL_CONS_COLUMNS

CONSTRAINT_COLUMNS

CONSTRAINT_DEFS

USER_CONSTRAINTS

USER_CONS_COLUMNS

USER_CROSS_REFS

DBA_CONSTRAINTS

DBA_CONS_COLUMNS

DBA_CROSS_REFS

Refer to the Oracle7 Server Reference manual for detailed information about each view.

Examples

Consider the following CREATE TABLE statements that define a number of integrity constraints, and the following examples:

CREATE TABLE dept (  
   deptno   NUMBER(3) PRIMARY KEY,  
   dname    VARCHAR2(15),     loc      VARCHAR2(15),     CONSTRAINT dname_ukey UNIQUE (dname, loc),  
   CONSTRAINT loc_check1  
      CHECK (loc IN ('NEW YORK', 'BOSTON', 'CHICAGO')));  
  
CREATE TABLE emp (  
   empno    NUMBER(5) PRIMARY KEY,  
   ename    VARCHAR2(15) NOT NULL,  
   job      VARCHAR2(10),  
   mgr      NUMBER(5) CONSTRAINT mgr_fkey  
            REFERENCES emp ON DELETE CASCADE,  
   hiredate DATE,
   sal      NUMBER(7,2),  
   comm     NUMBER(5,2),  
   deptno   NUMBER(3) NOT NULL  
   CONSTRAINT dept_fkey REFERENCES dept);

Example 1 Listing All of Your Accessible Constraints

The following query lists all constraints defined on all tables accessible to you, the user:

SELECT constraint_name, constraint_type, table_name, 
      r_constraint_name 
   FROM user_constraints;

Considering the example statements at the beginning of this section, a list similar to the one below is returned:

CONSTRAINT_NAME  C TABLE_NAME  R_CONSTRAINT_NAME  
---------------  - ----------- ------------------  
SYS_C00275       P DEPT  
DNAME_UKEY       U DEPT  
LOC_CHECK1       C DEPT  
SYS_C00278       C EMP  
SYS_C00279       C EMP  
SYS_C00280       P EMP  
MGR_FKEY         R EMP         SYS_C00280  
DEPT_FKEY        R EMP         SYS_C00275

Notice the following:

Some constraint names are user specified (such as DNAME_UKEY), while others are system specified (such as SYS_C00275).

Each constraint type is denoted with a different character in the CONSTRAINT_TYPE column. The table below summarizes the characters used for each constraint type.

*Constraint Type*	*Character*
PRIMARY KEY	P
UNIQUE KEY	U
FOREIGN KEY	R
CHECK, NOT NULL	C

Note: An additional constraint type is indicated by the character "V" in the CONSTRAINT_TYPE column. This constraint type corresponds to constraints created by the WITH CHECK OPTION for views. See Chapter 4 for more information about views and the WITH CHECK OPTION.

Example 2 Distinguishing NOT NULL Constraints from CHECK Constraints

In the previous example, several constraints are listed with a constraint type of "C". To distinguish which constraints are NOT NULL constraints and which are CHECK constraints in the EMP and DEPT tables, issue the following query:

SELECT constraint_name, search_condition  
   FROM user_constraints  
   WHERE (table_name = 'DEPT' OR table_name = 'EMP') AND  
      constraint_type = 'C';

Considering the example CREATE TABLE statements at the beginning of this section, a list similar to the one below is returned:

CONSTRAINT_NAME  SEARCH_CONDITION  
---------------  ----------------------------------------  
LOC_CHECK1       loc IN ('NEW YORK', 'BOSTON', 'CHICAGO')  
SYS_C00278       ENAME IS NOT NULL  
SYS_C00279       DEPTNO IS NOT NULL

Notice the following:

NOT NULL constraints are clearly identified in the SEARCH_CONDITION column.

The conditions for user-defined CHECK constraints are explicitly listed in the SEARCH_CONDITION column.

Example 3 Listing the Column Names that Constitute an Integrity Constraint

The following query lists all columns that constitute the constraints defined on all tables accessible to you, the user:

SELECT constraint_name, table_name, column_name  
   FROM user_cons_columns;

Considering the example statements at the beginning of this section, a list similar to the one below is returned:

CONSTRAINT_NAME  TABLE_NAME  COLUMN_NAME  
---------------  ----------- ---------------  
DEPT_FKEY        EMP         DEPTNO  
DNAME_UKEY       DEPT        DNAME  
DNAME_UKEY       DEPT        LOC  
LOC_CHECK1       DEPT        LOC  
MGR_FKEY         EMP         MGR  
SYS_C00275       DEPT        DEPTNO  
SYS_C00278       EMP         ENAME  
SYS_C00279       EMP         DEPTNO  
SYS_C00280       EMP         EMPNO