| Lesson 9 | Constraints in the data dictionary |
| Objective | Find 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.