Chapter Five. Viewing Constraints in the Data Dictionary

5.1 WHAT YOU CAN SEE

5.2 DICTIONARY VIEWS: AN OVERVIEW

5.3 CONSTRAINT VIEWS

5.4 USER_CONS_COLUMNS

5.5 USER_CONSTRAINTS

5.6 DATA DICTIONARY CONSTRAINT SCRIPTS

5.6.1 Constraints on a Table

5.6.2 Chasing a Constraint Name

5.6.3 CHECK Constraint Rule

5.6.4 Querying Parent Tables

5.6.5 Querying Child Tables

5.6.6 Constraint Status

5.6.7 Validated

This chapter serves two purposes. First, we look at constraint information in the data dictionary, specifically the data dictionary views USER_CONSTRAINTS and USER_CONS_COLUMNS. An understanding of these views can be a basis for a broad understanding of how constraints are enforced by Oracle.

Second, this is a starting point for exploring the data dictionary. You will be able to extract a wide variety of information from the data dictionary: constraint information, triggers, stored procedures, sequence definitions. Anything you define in the database you can reverse-engineer with SQL.

The sample scripts in this chapter contain sample output assuming they were run against the DDL for the model in Chapter 4.