Managing Constraints   «Prev  Next»

Lesson 9Constraints in the data dictionary
ObjectiveFind information about constraints in the data dictionary.

Oracle Data Dictionary Foreign Key Constraints

Constraints in the Oracle Data Dictionary

As with the other database objects discussed in this course, you can find out more about constraints through views in the data dictionary.

Data dictionary views about constraints

There are two data dictionary views that relate to constraints, USER_CONSTRAINTS and USER_CON_COLS. The USER_CONSTRAINTS view contains columns with the most important information about the constraints in a particular schema. Some of the columns in the USER_CONSTRAINTS view apply to all constraints, including the following:

Constraint name What the constraint determines
OWNER The owner (schema) of the constraint
CONSTRAINT_NAME The name of the constraint
CONSTRAINT_TYPE The type of constraint, which is either C (a CHECK constraint), P (a PRIMARY KEY constraint), U (a UNIQUE constraint), R (a FOREIGN KEY constraint, since the R stands for “REFERENTIAL”, as in integrity), or V (a constraint on a view, which was not covered in this module)
TABLE_NAME The name of the table that contains the constraint
STATUS The current status of the constraint: ENABLED or DISABLED
DEFERRABLE Whether the constraint can be deferred
DEFERRED Whether the constraint is currently deferred

Some of the columns in the USER_CONSTRAINTS view only apply to foreign key constraints, including the following:
Constraint name What the constraint determines
R_OWNER The owner (schema) of the constraint
R_CONSTRAINT The owner of the referenced table
DELETE_RULE The name of the primary key constraint in the referenced table
CASCADE or NO ACTION Whether the rule for deleting referenced values in the primary key is CASCADE or NO ACTION, then NO ACTION is the default

One of the columns in the USER_CONSTRAINTS view only applies to CHECK constraints, which is the SEARCH_CONDITION constraint, which contains the text of the CHECK constraint expression.
The USER_CON_COLS view contains information about the columns that constraints are associated with. This view simply holds identifying information about the constraint, including the OWNER_NAME, the CONSTRAINT_NAME, the table and column name for each column referenced in the constraint, and the original position of the column in the constraint.
The next lesson is a wrap-up of this module.