Getting Information on Constraints:user_constraints and all_constraints

user_constraints has information on your constraints. Some of the columns in user_constraints:

Column	Type	Description
owner	VARCHAR2(30)	Owner of the constraint.
constraint_name	VARCHAR2(30)	Name of the constraint.
constraint_type	VARCHAR2(1)	Constraint type (P, R, C, U, V, or O).
table_name	VARCHAR2(30)	Name of the table on which the constraint is defined.
status	VARCHAR2(8)	Constraint status (ENABLED or DISABLED).
deferrable	VARCHAR2(14)	Whether the constraint is deferrable (DEFERRABLE or NOT DEFERRABLE).
deferred	VARCHAR2(9)	Whether the constraint is enforced immediately or deferred (IMMEDIATEor DEFERRED).


SELECT constraint_name, constraint_type, status, deferrable, deferred
FROM user_constraints
WHERE table_name = 'EMP';

all_constraints view has information on all the constraints you have access to. user_cons_columns view has information on the constraints for a column.

Some Columns in the user_cons_columns View:

Column	Type	Description
owner	VARCHAR2(30)	Owner of the constraint
constraint_name	VARCHAR2(30)	Name of the constraint
table_name	VARCHAR2(30)	Name of the table on which the constraint is defined
column_name	VARCHAR2(4000)	Name of the column on which the constraint is defined


SQL> ALTER TABLE emp
  2  ADD CONSTRAINT my_uq UNIQUE (ename)
  3  DEFERRABLE INITIALLY DEFERRED;

Table altered.

SQL> SELECT constraint_name, column_name
  2  FROM user_cons_columns
  3  WHERE table_name = 'EMP'
  4  ORDER BY constraint_name;

CONSTRAINT_NAME
------------------------------
COLUMN_NAME
-------------------------------------------------
MY_UQ
ENAME

SYS_C004212
EMPNO


SQL>

all_cons_columns view has information on all the column constraints you have access to. The next query joins user_constraints and user_cons_columns to get the column_name, constraint_name, constraint_type, and status:


SQL> SELECT ucc.column_name,
  2         ucc.constraint_name,
  3         uc.constraint_type,
  4         uc.status
  5  FROM user_constraints uc,
  6       user_cons_columns ucc
  7  WHERE uc.table_name = ucc.table_name
  8    AND uc.constraint_name = ucc.constraint_name
  9    AND ucc.table_name = 'EMP'
 10  ORDER BY ucc.constraint_name;

COLUMN_NAME       CONSTRAINT_NAME                C STATUS
-----------------------------------------------------------
ENAME             MY_UQ                          U ENABLED
EMPNO             SYS_C004212                    C ENABLED


SQL>

Home »
Oracle »
Table »

Constraints:

Adding a Constraint with CHECK
Adding a NOT NULL Constraint
Adding a FOREIGN KEY Constraint
ON DELETE CASCADE
ON DELETE SET NULL
Adding a UNIQUE Constraint
CHECK constraint
Multiple Constraints
Dropping a Constraint
Disabling a Constraint
Enabling a Constraint
Deferred Constraints
Getting Information on Constraints:user_constraints and all_constraints