Oracle Data Dictionary Foreign Key Constraints

To analyze foreign key constraints within an Oracle database, you can query the data dictionary views. These views contain metadata about the database objects, including constraints. For foreign key constraints, the following data dictionary views are particularly useful:

1. USER_CONSTRAINTS: This view contains information about constraints owned by the current user. If you're looking for foreign key constraints on tables owned by the user you're logged in as, this is the appropriate view to query.
2. ALL_CONSTRAINTS: For a broader view that encompasses constraints accessible to the current user, including those owned by other users, you would query this view.
3. DBA_CONSTRAINTS: This view provides information on all constraints in the database and is available to users with DBA privileges. It is comprehensive and includes every detail about database constraints, across all schemas.
4. USER_CONS_COLUMNS: When you need to find out which columns are involved in constraints, this view can be queried. It provides information about the columns associated with constraints owned by the current user.
5. ALL_CONS_COLUMNS: Similar to USER_CONS_COLUMNS, but includes information about the columns that are accessible to the current user, not just those owned by the user.

To find out about foreign key constraints specifically, you would look for constraints where the `CONSTRAINT_TYPE` column is 'R', which denotes referential integrity (foreign keys). Here is a sample SQL query to retrieve information about all foreign key constraints on tables accessible by the current user:

SELECT 
    a.table_name, 
    a.constraint_name, 
    a.r_constraint_name as referenced_constraint_name,
    b.table_name as referenced_table_name
FROM 
    all_constraints a
JOIN 
    all_constraints b ON a.r_constraint_name = b.constraint_name
WHERE 
    a.constraint_type = 'R'
AND 
    b.constraint_type IN ('P', 'U');

This query will return the names of tables with foreign key constraints (`table_name`), the names of the constraints (`constraint_name`), the names of the primary or unique constraints they reference (`referenced_constraint_name`), and the names of the tables that the foreign keys reference (`referenced_table_name`).
The `R` in `constraint_type` specifies that you are interested in referential constraints, i.e., foreign keys. The join with `all_constraints b` is necessary to find the corresponding primary or unique constraint (denoted by 'P' or 'U' in the `constraint_type`) that the foreign key references.
For in-depth analysis, you might need to join with the `ALL_CONS_COLUMNS` view to get the specific columns involved in these constraints. Understanding the structure and relationships between these data dictionary views is crucial for effectively managing and analyzing the integrity constraints within an Oracle database environment.
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.

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.