6.5 How do I…Determine foreign key dependencies?

Problem

I want to list information about the constraints in the database. I need to determine the constraint name, the type of constraint, and the status of the constraint. I also need to find out the table and columns on which the constraint is defined. I know that all this information can be obtained from data dictionary views. How do I list information about constraints to determine foreign key dependencies in the database?

Technique

Constraint information can be queried from the USER_CONSTRAINTS or ALL_CONSTRAINTS data dictionary views. Columns associated with constraints can be viewed from the USER_CONS_COLUMNS or ALL_CONS_COLUMNS views. When the primary key is created, a unique index is placed on the table that can be listed from the USER_INDEXES view. Table 6.1 shows the columns in the USER_CONSTRAINTS view and details on how to interpret the value of the CONSTRAINT_TYPE column to find out the type of constraint definition.

Table 6.1 Columns in the USER_CONSTRAINTS view

Column Description
OWNER Owner of the constraint
CONSTRAINT_NAME Name of the constraint
CONSTRAINT_TYPE Type of constraint definition
TABLE_NAME Name of the table associated with the constraint
SEARCH_CONDITION Search condition used for CHECK constraints
R_OWNER Owner of the table referenced by a FOREIGN KEY constraint
R_CONSTRAINT_NAME Name of the constraint referenced by a FOREIGN KEY constraint
DELETE_RULE Delete rule for a referential constraint: CASCADE or NO ACTION
STATUS Status of constraint: ENFORCED, ENABLED, or DISABLED
DEFERRABLE (Oracle8) Indicates whether the constraint is deferrable
DEFERRED (Oracle8) Indicates whether the constraint was initially deferred
GENERATED (Oracle8) Indicates whether the constraint name is system generated
LAST_CHANGE (Oracle8) Indicates when the constraint was last enabled or disabled
VALIDATED (Oracle8) Indicates whether all data obeys the constraint: VALIDATED or NOT VALIDATED

Steps

1. Run SQL*Plus and connect as the WAITE user account. Run the CHP6_14.SQL file. The USER_CONSTRAINTS view is queried to display information about constraints created in this chapter.

SQL> START CHP6_14

SQL> SET ECHO ON

SQL> COLUMN CONSTRAINT_NAME FORMAT A15

SQL> COLUMN TABLE_NAME FORMAT A10

SQL> COLUMN R_CONSTRAINT_NAME FORMAT A17

SQL> COLUMN SEARCH_CONDITION FORMAT A30

SQL> SELECT

2 CONSTRAINT_NAME,

3 TABLE_NAME,

4 CONSTRAINT_TYPE,

5 R_CONSTRAINT_NAME,

6 SEARCH_CONDITION,

7 STATUS

8 FROM

9 USER_CONSTRAINTS

10 ORDER BY 1, 2;

Figure 6.14 shows the results of the query. The COLUMN commands at the top format column lengths, before running the statement. The columns returned by the query show you the name of the constraint, the type of constraint, and the name of the referenced key—if it is a foreign key constraint. The CONSTRAINT_NAME column contains the unique name of the constraint. Use Table 6.2 to recognize the type of constraint from the character displayed under the CONSTRAINT_TYPE column. The query displays PRIMARY KEY constraints on tables created in previous sections. NOT NULL constraints show up as CHECK constraints with a value of “C” under the constraint type column listing. Most importantly, the FOREIGN KEY constraint in table EMP06 shows a dependency on the PRIMARY KEY constraint of DEPT06, through the R_CONSTRAINT_NAME column value. As all CHECK constraints and NOT NULL constraints have a value of “C” in the CONSTRAINT_TYPE column, the SEARCH_CONDITION column can be used to distinguish between the two. As you might note, for NOT NULL constraints, the SEARCH_CONDITION is clearly identified by COLUMN_NAME IS NOT NULL. For CHECK constraints, this column would display the user-defined condition that was specified while defining the constraint. The STATUS column contains the state of the constraint: ENABLED, DISABLED, or ENFORCED. By including the DELETE_RULE column in the preceding query, any missing cascade delete rules can be easily found. Delete cascades are examined in section 6.7.

Table 6.2 Values for the CONSTRAINT_TYPE column

Character Constraint Type
CCheck constraint (including NOT NULL)
PPRIMARY KEY constraint
RFOREIGN KEY constraint
UUNIQUE constraint
VWITH CHECK OPTION constraint for views

2. Run the CHP6_15.SQL file, which contains a SELECT statement to query the USER_CONS_COLUMNS table and list columns on which constraints are defined for a specified table. Columns of the USER_CONS_COLUMNS view are listed in Table 6.3.

SQL> START CHP6_15

SQL> SET ECHO ON

SQL> SET DEFINE ON

SQL> SET VERIFY ON

SQL> SELECT

2 CONSTRAINT_NAME,

3 COLUMN_NAME

4 FROM

5 USER_CONS_COLUMNS

6 WHERE TABLE_NAME = UPPER(‘&TABLE_NAME’)

7 ORDER BY 1;

Provide EMP06 as the value for the table name substitution variable. Figure 6.15 shows the results of the query for the EMP06 table created earlier in this chapter. The columns returned by the query show you the name of the constraint followed by the name of the participating column. The USER_CONSTRAINTS and USER_CONS_COLUMNS views can be joined in a single SELECT statement for a detailed reporting of constraints and their constituent columns.

Table 6.3 Columns in the USER_CONS_COLUMNS view

CONSTRAINT_TYPE Description
OWNER Owner of the constraint
CONSTRAINT_NAME Name of the constraint
TABLE_NAME Name of the table associated with the constraint
COLUMN_NAME Name of the associated column or attribute
POSITION Position of column or attribute in definition

How It Works

The USER_CONSTRAINTS and USER_CONS_COLUMNS data dictionary views contain information about constraints in the database. The TABLE_NAME column can be used in the WHERE clause of a query to either view, for returning information about constraints on a specific table. Step 1 executes a query on the USER_CONTRAINTS view to list information about all constraints in the database that are accessible to you. Foreign key dependencies are then deciphered from the output resulting from the query. Step 2 executes a query on the USER_CONS_COLUMNS view to list the names of constraints on a specified table along with the names of the columns that they act upon.

Comments

A simple query to the USER_CONSTRAINTS and USER_CONS_COLUMNS data dictionary views will provide you with valuable information about constraints in the database. If data manipulation statements are running into integrity violation exceptions, query the data dictionary to verify that they behave in conformance with the foreign key dependencies and also comply with other constraints defined on tables. With Oracle8, important new columns to the USER_CONSTRAINTS view are DEFERRABLE and DEFERRED, which indicate the mode of deferred constraints. Noteworthy is the new VALIDATED column, if included in the query would display whether the data in any table is valid or not. This can be used to troubleshoot data problems. As with other views, a prefix of ALL_ instead of USER_ will allow you to query constraints created by another user, by specifying the OWNER in the WHERE clause of the query.