USER_CONSTRAINTS: Definition

Examples

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

CREATE TABLE Dept_tab ( 
 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_tab ( 
 Empno NUMBER(5) PRIMARY KEY, 
 Ename VARCHAR2(15) NOT NULL, 
 Job VARCHAR2(10), 
  Mgr NUMBER(5) CONSTRAINT Mgr_fkey 
 REFERENCES Emp_tab ON DELETE CASCADE, 
 Hiredate DATE,
 Sal NUMBER(7,2), 
 Comm NUMBER(5,2), 
 Deptno NUMBER(3) NOT NULL 
 CONSTRAINT Dept_fkey REFERENCES Dept_tab);
 

Example 1: Listing All of Your Accessible Constraints

The following query lists all constraints defined on all tables accessible to 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_TAB 
DNAME_UKEY U DEPT_TAB 
LOC_CHECK1 C DEPT_TAB 
SYS_C00278 C EMP_TAB 
SYS_C00279 C EMP_TAB 
SYS_C00280 P EMP_TAB 
MGR_FKEY R EMP_TAB SYS_C00280 
DEPT_FKEY R EMP_TAB 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

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_TAB and DEPT_TAB tables, issue the following query:

SELECT Constraint_name, Search_condition 
 FROM User_constraints 
 WHERE (Table_name = 'DEPT_TAB' OR Table_name = 'EMP_TAB') 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 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_TAB DEPTNO 
DNAME_UKEY DEPT_TAB DNAME 
DNAME_UKEY DEPT_TAB LOC 
LOC_CHECK1 DEPT_TAB LOC 
MGR_FKEY EMP_TAB MGR 
SYS_C00275 DEPT_TAB DEPTNO 
SYS_C00278 EMP_TAB ENAME 
SYS_C00279 EMP_TAB DEPTNO 
SYS_C00280 EMP_TAB EMPNO