Foreign Key Constraint Analysis (dconfk.sql)

Use at Your Own Risk

This script is for educational purposes only. Always test in development environments before production use. See our disclaimer for full terms.

What This Script Does

This script analyzes foreign key constraints by showing the detailed column mappings between child and parent tables, including target table information and delete rules. It provides essential information for understanding database relationships, validating referential integrity design, and troubleshooting constraint-related issues.

Script

rem  dconfkc.sql
rem
set linesize 132
rem
ttitle 'Foreign Key Table Constraint Columns'
rem
col owner format a12 heading 'OWNER'
col table_name format a18 heading 'TABLE NAME'
col constraint_name format a30 heading 'CONSTRAINT NAME'
col constraint_type format a1 heading 'T'
col status format a8 heading 'STATUS'
col target_table format a18 heading 'TARGET TABLE NAME'
col position format 99 heading 'POS'
col column_name format a30 heading 'COLUMN NAME'
col delete_rule format a9 heading 'DELETE|RULE'
rem
break on owner on table_name on constraint_name on target_table on delete_rule
rem
select c.owner, c.table_name, c.constraint_name,
       cc.table_name target_table, c.delete_rule,
       col.position,
       col.column_name
  from sys.dba_cons_columns col,
       sys.dba_constraints cc,
       sys.dba_constraints c
 where c.owner like upper('&owner')
   and c.table_name like upper('&table')
   and c.r_owner = cc.owner
   and c.r_constraint_name like nvl(upper('&r_constraint_name'), '%')
   and c.r_constraint_name = cc.constraint_name
   and c.constraint_type = 'R'
   and c.owner = col.owner
   and c.table_name = col.table_name
   and c.constraint_name = col.constraint_name
 order by c.owner, c.table_name, c.constraint_name, col.position;
rem
set linesize 80

Usage

-- Run the script in SQL*Plus or SQLcl
@dconfk.sql

-- When prompted, enter parameters:
-- owner: Schema owner pattern (% for all)
-- table: Table name pattern (% for all)
-- r_constraint_name: Referenced constraint pattern (% for all)

-- Examples
Enter value for owner: HR
Enter value for table: %
Enter value for r_constraint_name: %

Parameters

• &owner: Schema owner pattern (use % for all schemas)
• &table: Table name pattern (use % for all tables)
• &r_constraint_name: Referenced constraint name pattern (use % for all)

Required Privileges

• SELECT on SYS.DBA_CONS_COLUMNS
• SELECT on SYS.DBA_CONSTRAINTS
• Typically requires DBA role

Sample Output

Foreign Key Table Constraint Columns

OWNER        TABLE NAME         CONSTRAINT NAME                TARGET TABLE NAME  DELETE POS COLUMN NAME
                                                                             RULE
------------ ------------------ ------------------------------ ------------------ ------ --- -------------
HR           EMPLOYEES          EMP_DEPT_FK                    DEPARTMENTS        NO ACTION 1 DEPARTMENT_ID
             EMPLOYEES          EMP_JOB_FK                     JOBS               NO ACTION 1 JOB_ID
             EMPLOYEES          EMP_MANAGER_FK                 EMPLOYEES          NO ACTION 1 MANAGER_ID

             JOB_HISTORY        JHIST_DEPT_FK                  DEPARTMENTS        NO ACTION 1 DEPARTMENT_ID
             JOB_HISTORY        JHIST_EMP_FK                   EMPLOYEES          CASCADE    1 EMPLOYEE_ID
             JOB_HISTORY        JHIST_JOB_FK                   JOBS               NO ACTION 1 JOB_ID

SALES        ORDER_ITEMS        ITEM_ORDER_FK                  ORDERS             CASCADE    1 ORDER_ID
             ORDER_ITEMS        ITEM_PRODUCT_FK                PRODUCTS           NO ACTION 1 PRODUCT_ID
                                ITEM_PRODUCT_FK                PRODUCTS           NO ACTION 2 PRODUCT_CODE

Key Output Columns

• OWNER: Schema owner of the child table
• TABLE NAME: Child table containing the foreign key
• CONSTRAINT NAME: Name of the foreign key constraint
• TARGET TABLE NAME: Parent table being referenced
• DELETE RULE: Action when parent record is deleted
• POS: Position of column in multi-column constraints
• COLUMN NAME: Column participating in the foreign key

Understanding Foreign Key Relationships

Delete Rules

• NO ACTION: Prevents deletion of parent if children exist
• CASCADE: Deletes child records when parent is deleted
• SET NULL: Sets foreign key columns to NULL when parent deleted
• RESTRICT: Same as NO ACTION but checked immediately

Multi-Column Foreign Keys

• Position 1, 2, 3…: Order of columns in composite foreign keys
• All columns required: Must reference all columns of parent primary/unique key
• Matching data types: Child and parent columns must have compatible types

Performance Analysis

Index Implications

• Unindexed foreign keys: Can cause table locks during parent DML
• Performance impact: Slow parent table updates/deletes
• Lock escalation: TM locks on child tables

Referential Integrity Validation

-- Check for missing indexes on foreign key columns
SELECT c.owner, c.table_name, c.constraint_name,
       LISTAGG(cc.column_name, ',') WITHIN GROUP (ORDER BY cc.position) fk_columns
FROM dba_constraints c, dba_cons_columns cc
WHERE c.constraint_type = 'R'
AND c.owner = cc.owner
AND c.table_name = cc.table_name
AND c.constraint_name = cc.constraint_name
AND NOT EXISTS (
    SELECT 1 FROM dba_ind_columns ic
    WHERE ic.table_owner = cc.owner
    AND ic.table_name = cc.table_name
    AND ic.column_name = cc.column_name
    AND ic.column_position = 1
)
GROUP BY c.owner, c.table_name, c.constraint_name;

Common Use Cases

Database Design Validation

-- Analyze foreign key design for specific schema
@dconfk.sql
-- Enter schema name and % for tables
-- Review relationship structure and delete rules

Performance Troubleshooting

-- Identify foreign keys during lock issues
@dconfk.sql
-- Focus on tables experiencing TM locks
-- Check for missing indexes on foreign key columns

Data Migration Planning

-- Map dependencies before data migration
@dconfk.sql
-- Understand child-parent relationships
-- Plan deletion/insertion order

Troubleshooting Constraint Issues

Missing Index Detection

-- Find foreign keys without supporting indexes
SELECT c.owner, c.table_name, c.constraint_name,
       cc.column_name, cc.position
FROM dba_constraints c, dba_cons_columns cc
WHERE c.constraint_type = 'R'
AND c.owner = cc.owner
AND c.table_name = cc.table_name
AND c.constraint_name = cc.constraint_name
AND cc.position = 1  -- First column of FK
AND NOT EXISTS (
    SELECT 1 FROM dba_ind_columns ic
    WHERE ic.table_owner = cc.owner
    AND ic.table_name = cc.table_name
    AND ic.column_name = cc.column_name
);

Constraint Validation Failures

-- Check for constraint violations
SELECT 'ALTER TABLE '||owner||'.'||table_name||
       ' MODIFY CONSTRAINT '||constraint_name||' VALIDATE;' validation_sql
FROM dba_constraints
WHERE constraint_type = 'R'
AND status = 'ENABLED'
AND validated = 'NOT VALIDATED'
AND owner = 'HR';

Delete Rule Analysis

-- Analyze cascade deletion impact
SELECT c.owner, c.table_name, c.constraint_name,
       cc.table_name parent_table,
       c.delete_rule,
       (SELECT num_rows FROM dba_tables t
        WHERE t.owner = c.owner AND t.table_name = c.table_name) child_rows,
       (SELECT num_rows FROM dba_tables t
        WHERE t.owner = cc.owner AND t.table_name = cc.table_name) parent_rows
FROM dba_constraints c, dba_constraints cc
WHERE c.constraint_type = 'R'
AND c.r_owner = cc.owner
AND c.r_constraint_name = cc.constraint_name
AND c.delete_rule = 'CASCADE'
ORDER BY child_rows DESC;

Advanced Analysis

Relationship Hierarchy

-- Build table dependency hierarchy
WITH fk_relations AS (
  SELECT c.owner, c.table_name child_table,
         cc.owner parent_owner, cc.table_name parent_table,
         c.constraint_name, c.delete_rule
  FROM dba_constraints c, dba_constraints cc
  WHERE c.constraint_type = 'R'
  AND c.r_owner = cc.owner
  AND c.r_constraint_name = cc.constraint_name
)
SELECT LEVEL hierarchy_level,
       LPAD(' ', (LEVEL-1)*2) || owner||'.'||child_table table_path,
       constraint_name, delete_rule
FROM fk_relations
START WITH parent_table NOT IN (SELECT child_table FROM fk_relations)
CONNECT BY PRIOR child_table = parent_table
ORDER SIBLINGS BY child_table;

Circular Reference Detection

-- Detect circular foreign key references
WITH fk_graph AS (
  SELECT c.owner||'.'||c.table_name child,
         cc.owner||'.'||cc.table_name parent
  FROM dba_constraints c, dba_constraints cc
  WHERE c.constraint_type = 'R'
  AND c.r_owner = cc.owner
  AND c.r_constraint_name = cc.constraint_name
)
SELECT child, parent, LEVEL
FROM fk_graph
WHERE CONNECT_BY_ISCYCLE = 1
START WITH parent IN (SELECT child FROM fk_graph)
CONNECT BY NOCYCLE parent = PRIOR child;

Related Scripts

• Primary Key Constraints (dconpk.sql) - Primary key analysis
• All Constraints (dcontig.sql) - Complete constraint overview
• Index Analysis (dindex.sql) - Index structure and performance
• Table Relationships (../database-info/vdict.sql) - Data dictionary relationships