ORA-02298: Cannot Validate - Parent Keys Not Found (Orphan Rows Fix)

ORA-02298: Cannot Validate - Parent Keys Not Found

Error Overview

Error Text: ORA-02298: cannot validate ([schema].[constraint_name]) - parent keys not found

This error occurs when Oracle attempts to enable or validate a foreign key constraint but discovers orphan rows in the child table — rows that reference parent key values which do not exist in the parent table. Unlike ORA-02291 which blocks individual INSERT/UPDATE operations, ORA-02298 is raised during constraint validation against existing data.

Understanding Constraint Validation

When This Error Occurs

Constraint Validation Triggers
├── ALTER TABLE ... ENABLE CONSTRAINT
├── ALTER TABLE ... MODIFY CONSTRAINT ... VALIDATE
├── ALTER TABLE ... ADD CONSTRAINT (with existing data)
├── Data Pump import with constraints enabled
└── Database upgrade with constraint re-validation

Common Scenarios

• Enabling a disabled constraint - Constraint was disabled for data loading and orphan rows were introduced
• Data loaded without constraints - Bulk loads via SQL*Loader or external tables bypassed referential integrity
• Parent rows deleted while constraint was disabled - Parent records removed during a maintenance window
• Data migration without referential integrity - Migrated child data without corresponding parent records
• Post-upgrade constraint validation - Upgrading a database that had NOVALIDATE constraints

Diagnostic Steps

1. Identify the Constraint and Tables Involved

-- Get constraint details from the error message
SELECT
  c.owner,
  c.constraint_name,
  c.table_name AS child_table,
  c.status,
  c.validated,
  c.delete_rule,
  rc.table_name AS parent_table,
  rc.constraint_name AS parent_constraint
FROM dba_constraints c, dba_constraints rc
WHERE c.constraint_name = 'CONSTRAINT_NAME_FROM_ERROR'  -- Replace with actual constraint name
  AND c.r_constraint_name = rc.constraint_name(+)
  AND c.r_owner = rc.owner(+);

-- Get column mapping between child and parent tables
SELECT
  cc.table_name AS child_table,
  cc.column_name AS child_column,
  cc.position,
  rcc.table_name AS parent_table,
  rcc.column_name AS parent_column
FROM dba_cons_columns cc, dba_constraints c, dba_cons_columns rcc
WHERE cc.constraint_name = 'CONSTRAINT_NAME_FROM_ERROR'
  AND cc.owner = c.owner
  AND cc.constraint_name = c.constraint_name
  AND c.r_constraint_name = rcc.constraint_name
  AND c.r_owner = rcc.owner
  AND cc.position = rcc.position
ORDER BY cc.position;

2. Find Orphan Rows Using NOT EXISTS

-- Count orphan rows (single column foreign key)
SELECT COUNT(*) AS orphan_count
FROM child_table c
WHERE c.foreign_key_column IS NOT NULL
  AND NOT EXISTS (
    SELECT 1 FROM parent_table p
    WHERE p.primary_key_column = c.foreign_key_column
  );

-- List distinct orphan key values with counts
SELECT
  c.foreign_key_column AS orphan_value,
  COUNT(*) AS row_count
FROM child_table c
WHERE c.foreign_key_column IS NOT NULL
  AND NOT EXISTS (
    SELECT 1 FROM parent_table p
    WHERE p.primary_key_column = c.foreign_key_column
  )
GROUP BY c.foreign_key_column
ORDER BY row_count DESC;

-- For composite foreign keys
SELECT
  c.fk_col1,
  c.fk_col2,
  COUNT(*) AS row_count
FROM child_table c
WHERE NOT EXISTS (
    SELECT 1 FROM parent_table p
    WHERE p.pk_col1 = c.fk_col1
      AND p.pk_col2 = c.fk_col2
  )
AND (c.fk_col1 IS NOT NULL OR c.fk_col2 IS NOT NULL)
GROUP BY c.fk_col1, c.fk_col2
ORDER BY row_count DESC;

3. Use EXCEPTIONS INTO to Identify Violating Rows

-- Create the EXCEPTIONS table (Oracle-provided script)
@?/rdbms/admin/utlexcpt.sql

-- Or create it manually
CREATE TABLE exceptions (
  row_id    ROWID,
  owner     VARCHAR2(128),
  table_name VARCHAR2(128),
  constraint VARCHAR2(128)
);

-- Attempt to enable the constraint and capture violating rows
ALTER TABLE child_table
ENABLE CONSTRAINT constraint_name
EXCEPTIONS INTO exceptions;

-- This will fail with ORA-02298 but populates the EXCEPTIONS table

-- Query the specific violating rows
SELECT e.row_id, e.constraint, t.*
FROM exceptions e, child_table t
WHERE e.row_id = t.rowid
  AND e.constraint = 'CONSTRAINT_NAME_FROM_ERROR'
ORDER BY e.row_id;

-- Get a summary of violations
SELECT
  e.constraint,
  e.table_name,
  COUNT(*) AS violation_count
FROM exceptions e
GROUP BY e.constraint, e.table_name
ORDER BY violation_count DESC;

4. Analyze the Scope of the Problem

-- Compare child and parent table record counts
SELECT
  'PARENT' AS table_type,
  COUNT(*) AS total_rows,
  COUNT(DISTINCT primary_key_column) AS unique_keys
FROM parent_table
UNION ALL
SELECT
  'CHILD' AS table_type,
  COUNT(*) AS total_rows,
  COUNT(DISTINCT foreign_key_column) AS unique_fk_values
FROM child_table
WHERE foreign_key_column IS NOT NULL;

-- Check for data type or format mismatches
SELECT
  c.foreign_key_column AS child_value,
  DUMP(c.foreign_key_column) AS child_dump,
  LENGTH(c.foreign_key_column) AS child_length,
  (SELECT p.primary_key_column
   FROM parent_table p
   WHERE UPPER(TRIM(p.primary_key_column)) = UPPER(TRIM(c.foreign_key_column))
   AND ROWNUM = 1) AS possible_match
FROM child_table c
WHERE NOT EXISTS (
    SELECT 1 FROM parent_table p
    WHERE p.primary_key_column = c.foreign_key_column
  )
AND c.foreign_key_column IS NOT NULL
AND ROWNUM <= 20;

-- Check all foreign key constraints on the child table
SELECT
  c.constraint_name,
  c.status,
  c.validated,
  rc.table_name AS parent_table,
  LISTAGG(cc.column_name, ', ') WITHIN GROUP (ORDER BY cc.position) AS fk_columns
FROM dba_constraints c, dba_constraints rc, dba_cons_columns cc
WHERE c.table_name = UPPER('child_table')
  AND c.constraint_type = 'R'
  AND c.r_constraint_name = rc.constraint_name
  AND c.r_owner = rc.owner
  AND c.owner = cc.owner
  AND c.constraint_name = cc.constraint_name
GROUP BY c.constraint_name, c.status, c.validated, rc.table_name
ORDER BY c.constraint_name;

Immediate Solutions

Solution 1: Delete Orphan Rows

Remove Child Rows with No Parent Reference

-- Back up orphan rows before deletion
CREATE TABLE child_table_orphans_bak AS
SELECT c.*, SYSDATE AS backup_date
FROM child_table c
WHERE c.foreign_key_column IS NOT NULL
  AND NOT EXISTS (
    SELECT 1 FROM parent_table p
    WHERE p.primary_key_column = c.foreign_key_column
  );

-- Verify backup
SELECT COUNT(*) AS backed_up_rows FROM child_table_orphans_bak;

-- Delete orphan rows
DELETE FROM child_table
WHERE foreign_key_column IS NOT NULL
  AND NOT EXISTS (
    SELECT 1 FROM parent_table p
    WHERE p.primary_key_column = child_table.foreign_key_column
  );

COMMIT;

-- Now enable the constraint
ALTER TABLE child_table ENABLE CONSTRAINT constraint_name;

Solution 2: Insert Missing Parent Rows

Create Parent Records to Satisfy the Foreign Key

-- Identify what parent records are needed
SELECT DISTINCT c.foreign_key_column AS missing_parent_key
FROM child_table c
WHERE c.foreign_key_column IS NOT NULL
  AND NOT EXISTS (
    SELECT 1 FROM parent_table p
    WHERE p.primary_key_column = c.foreign_key_column
  )
ORDER BY c.foreign_key_column;

-- Insert placeholder parent records
INSERT INTO parent_table (primary_key_column, name, status, created_date)
SELECT DISTINCT
  c.foreign_key_column,
  'PLACEHOLDER - Auto-created for data integrity' AS name,
  'INACTIVE' AS status,
  SYSDATE AS created_date
FROM child_table c
WHERE c.foreign_key_column IS NOT NULL
  AND NOT EXISTS (
    SELECT 1 FROM parent_table p
    WHERE p.primary_key_column = c.foreign_key_column
  );

COMMIT;

-- Now enable the constraint
ALTER TABLE child_table ENABLE CONSTRAINT constraint_name;

Solution 3: Enable with NOVALIDATE

Skip Validation of Existing Data

-- Enable constraint without validating existing rows
-- New INSERT/UPDATE operations will be checked, but existing orphans are ignored
ALTER TABLE child_table ENABLE NOVALIDATE CONSTRAINT constraint_name;

-- Verify constraint status
SELECT constraint_name, status, validated
FROM user_constraints
WHERE constraint_name = 'CONSTRAINT_NAME';

-- Result: STATUS = ENABLED, VALIDATED = NOT VALIDATED
-- This means:
--   New operations ARE checked against the constraint
--   Existing data is NOT guaranteed to satisfy the constraint

NOVALIDATE Warning

Using NOVALIDATE means existing orphan rows remain in the table. The optimizer cannot fully rely on the constraint for query transformations. This approach is acceptable for data warehouses or when you plan to clean up data later, but should be avoided in OLTP systems where data integrity is critical.

Solution 4: Use the EXCEPTIONS Table for Targeted Cleanup

-- Create exceptions table
CREATE TABLE exceptions (
  row_id     ROWID,
  owner      VARCHAR2(128),
  table_name VARCHAR2(128),
  constraint VARCHAR2(128)
);

-- Try enabling -- this will fail but populate exceptions
BEGIN
  EXECUTE IMMEDIATE
    'ALTER TABLE child_table ENABLE CONSTRAINT constraint_name EXCEPTIONS INTO exceptions';
EXCEPTION
  WHEN OTHERS THEN
    IF SQLCODE = -2298 THEN
      DBMS_OUTPUT.PUT_LINE('Constraint validation failed. Check EXCEPTIONS table.');
    ELSE
      RAISE;
    END IF;
END;
/

-- Review the violating rows
SELECT t.*
FROM child_table t
WHERE t.rowid IN (
  SELECT e.row_id FROM exceptions e
  WHERE e.constraint = 'CONSTRAINT_NAME'
);

-- Delete only the violating rows
DELETE FROM child_table
WHERE rowid IN (
  SELECT row_id FROM exceptions
  WHERE constraint = 'CONSTRAINT_NAME'
);

COMMIT;

-- Clean up and retry
TRUNCATE TABLE exceptions;
ALTER TABLE child_table ENABLE CONSTRAINT constraint_name;

Long-Term Solutions

1. Implement Safe Data Loading Procedures

ETL Process with Constraint Management

-- Procedure for safe bulk data loading with constraint handling
CREATE OR REPLACE PROCEDURE safe_data_load(
  p_child_table   VARCHAR2,
  p_parent_table  VARCHAR2,
  p_fk_constraint VARCHAR2,
  p_fk_column     VARCHAR2,
  p_pk_column     VARCHAR2
) AS
  v_orphan_count NUMBER;
BEGIN
  -- Step 1: Disable constraint for bulk loading
  EXECUTE IMMEDIATE 'ALTER TABLE ' || p_child_table ||
    ' DISABLE CONSTRAINT ' || p_fk_constraint;
  DBMS_OUTPUT.PUT_LINE('Constraint disabled: ' || p_fk_constraint);

  -- Step 2: Load data (caller handles the actual INSERT/MERGE)
  DBMS_OUTPUT.PUT_LINE('Ready for data loading into ' || p_child_table);

  -- Step 3: Check for orphan rows before re-enabling
  EXECUTE IMMEDIATE
    'SELECT COUNT(*) FROM ' || p_child_table || ' c ' ||
    'WHERE c.' || p_fk_column || ' IS NOT NULL ' ||
    'AND NOT EXISTS (SELECT 1 FROM ' || p_parent_table || ' p ' ||
    'WHERE p.' || p_pk_column || ' = c.' || p_fk_column || ')'
  INTO v_orphan_count;

  IF v_orphan_count > 0 THEN
    DBMS_OUTPUT.PUT_LINE('WARNING: ' || v_orphan_count || ' orphan rows detected.');
    DBMS_OUTPUT.PUT_LINE('Clean up orphans before enabling constraint.');
    RAISE_APPLICATION_ERROR(-20001,
      v_orphan_count || ' orphan rows found in ' || p_child_table ||
      '. Clean up required before enabling constraint ' || p_fk_constraint);
  END IF;

  -- Step 4: Re-enable constraint
  EXECUTE IMMEDIATE 'ALTER TABLE ' || p_child_table ||
    ' ENABLE CONSTRAINT ' || p_fk_constraint;
  DBMS_OUTPUT.PUT_LINE('Constraint enabled: ' || p_fk_constraint);

EXCEPTION
  WHEN OTHERS THEN
    -- Log the error but don't leave constraint disabled silently
    DBMS_OUTPUT.PUT_LINE('ERROR: ' || SQLERRM);
    DBMS_OUTPUT.PUT_LINE('WARNING: Constraint ' || p_fk_constraint || ' may still be disabled!');
    RAISE;
END;
/

2. Use Deferred Constraints for Complex Transactions

Deferred Constraint Checking

-- Create a deferrable foreign key constraint
ALTER TABLE child_table
ADD CONSTRAINT fk_child_parent
  FOREIGN KEY (foreign_key_column)
  REFERENCES parent_table (primary_key_column)
  DEFERRABLE INITIALLY DEFERRED;

-- With deferred constraints, validation happens at COMMIT time
-- This allows inserting child rows before parent rows in the same transaction
BEGIN
  -- Insert child first (would fail with immediate constraint)
  INSERT INTO child_table (id, foreign_key_column, data)
  VALUES (1, 100, 'Child record');

  -- Then insert parent
  INSERT INTO parent_table (primary_key_column, name)
  VALUES (100, 'Parent record');

  -- Constraint is validated here at COMMIT
  COMMIT;
END;
/

-- You can also switch between deferred and immediate within a session
SET CONSTRAINTS fk_child_parent IMMEDIATE;  -- Validate after each statement
SET CONSTRAINTS fk_child_parent DEFERRED;   -- Validate at COMMIT only
SET CONSTRAINTS ALL DEFERRED;               -- Defer all deferrable constraints

3. Build an Orphan Row Monitoring System

Automated Referential Integrity Checks

-- Create monitoring table
CREATE TABLE orphan_row_monitor (
  check_id         NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  constraint_name  VARCHAR2(128),
  child_table      VARCHAR2(128),
  parent_table     VARCHAR2(128),
  orphan_count     NUMBER,
  sample_values    VARCHAR2(1000),
  check_date       TIMESTAMP DEFAULT SYSTIMESTAMP,
  resolved         CHAR(1) DEFAULT 'N'
);

-- Procedure to check all FK constraints for orphan rows
CREATE OR REPLACE PROCEDURE check_orphan_rows(p_schema VARCHAR2 DEFAULT USER) AS
  CURSOR fk_cursor IS
    SELECT
      c.constraint_name,
      c.table_name AS child_table,
      rc.table_name AS parent_table,
      cc.column_name AS child_column,
      rcc.column_name AS parent_column
    FROM dba_constraints c
    JOIN dba_constraints rc
      ON c.r_constraint_name = rc.constraint_name AND c.r_owner = rc.owner
    JOIN dba_cons_columns cc
      ON c.owner = cc.owner AND c.constraint_name = cc.constraint_name
    JOIN dba_cons_columns rcc
      ON rc.owner = rcc.owner AND rc.constraint_name = rcc.constraint_name
      AND cc.position = rcc.position
    WHERE c.constraint_type = 'R'
      AND c.owner = UPPER(p_schema)
      AND cc.position = 1;  -- Simplified for single-column FKs

  v_count NUMBER;
  v_samples VARCHAR2(1000);
BEGIN
  FOR fk_rec IN fk_cursor LOOP
    BEGIN
      EXECUTE IMMEDIATE
        'SELECT COUNT(*) FROM ' || p_schema || '.' || fk_rec.child_table || ' c ' ||
        'WHERE c.' || fk_rec.child_column || ' IS NOT NULL ' ||
        'AND NOT EXISTS (SELECT 1 FROM ' || p_schema || '.' || fk_rec.parent_table || ' p ' ||
        'WHERE p.' || fk_rec.parent_column || ' = c.' || fk_rec.child_column || ')'
      INTO v_count;

      IF v_count > 0 THEN
        -- Get sample orphan values
        EXECUTE IMMEDIATE
          'SELECT LISTAGG(val, '', '') WITHIN GROUP (ORDER BY val) FROM (' ||
          'SELECT DISTINCT TO_CHAR(c.' || fk_rec.child_column || ') AS val ' ||
          'FROM ' || p_schema || '.' || fk_rec.child_table || ' c ' ||
          'WHERE c.' || fk_rec.child_column || ' IS NOT NULL ' ||
          'AND NOT EXISTS (SELECT 1 FROM ' || p_schema || '.' || fk_rec.parent_table || ' p ' ||
          'WHERE p.' || fk_rec.parent_column || ' = c.' || fk_rec.child_column || ') ' ||
          'AND ROWNUM <= 5)'
        INTO v_samples;

        INSERT INTO orphan_row_monitor (constraint_name, child_table, parent_table, orphan_count, sample_values)
        VALUES (fk_rec.constraint_name, fk_rec.child_table, fk_rec.parent_table, v_count, v_samples);

        DBMS_OUTPUT.PUT_LINE('ORPHANS FOUND: ' || fk_rec.constraint_name ||
          ' (' || fk_rec.child_table || ' -> ' || fk_rec.parent_table || '): ' || v_count || ' rows');
      END IF;
    EXCEPTION
      WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Error checking ' || fk_rec.constraint_name || ': ' || SQLERRM);
    END;
  END LOOP;

  COMMIT;
END;
/

4. Data Migration Best Practices

Migration Framework with Integrity Checks

-- Pre-migration: Document all constraints
SELECT
  c.constraint_name,
  c.table_name,
  c.constraint_type,
  c.status,
  c.validated,
  rc.table_name AS referenced_table,
  LISTAGG(cc.column_name, ', ') WITHIN GROUP (ORDER BY cc.position) AS columns
FROM dba_constraints c
LEFT JOIN dba_constraints rc ON c.r_constraint_name = rc.constraint_name AND c.r_owner = rc.owner
JOIN dba_cons_columns cc ON c.owner = cc.owner AND c.constraint_name = cc.constraint_name
WHERE c.owner = 'MIGRATION_SCHEMA'
  AND c.constraint_type IN ('P', 'U', 'R')
GROUP BY c.constraint_name, c.table_name, c.constraint_type, c.status, c.validated, rc.table_name
ORDER BY
  CASE c.constraint_type
    WHEN 'P' THEN 1
    WHEN 'U' THEN 2
    WHEN 'R' THEN 3
  END;

-- Generate disable/enable scripts in dependency order
-- Disable: Foreign keys first, then unique/primary keys
SELECT 'ALTER TABLE ' || table_name || ' DISABLE CONSTRAINT ' || constraint_name || ';' AS disable_script
FROM user_constraints
WHERE constraint_type = 'R'
ORDER BY table_name;

-- Enable: Primary/unique keys first, then foreign keys
SELECT 'ALTER TABLE ' || table_name || ' ENABLE CONSTRAINT ' || constraint_name || ';' AS enable_script
FROM user_constraints
WHERE constraint_type IN ('P', 'U')
ORDER BY table_name;

SELECT 'ALTER TABLE ' || table_name || ' ENABLE CONSTRAINT ' || constraint_name || ';' AS enable_script
FROM user_constraints
WHERE constraint_type = 'R'
ORDER BY table_name;

-- Post-migration validation
SELECT
  c.constraint_name,
  c.table_name,
  c.status,
  c.validated,
  CASE
    WHEN c.status = 'DISABLED' THEN 'ACTION REQUIRED: Constraint still disabled'
    WHEN c.validated = 'NOT VALIDATED' THEN 'WARNING: Constraint not validated'
    ELSE 'OK'
  END AS migration_status
FROM user_constraints c
WHERE c.constraint_type = 'R'
ORDER BY
  CASE
    WHEN c.status = 'DISABLED' THEN 1
    WHEN c.validated = 'NOT VALIDATED' THEN 2
    ELSE 3
  END;

Prevention Strategies

1. Data Loading Order

-- Always load parent tables before child tables
-- Example loading sequence:
-- 1. Reference/lookup tables (countries, statuses, categories)
-- 2. Primary entity tables (customers, products)
-- 3. Transaction tables (orders, invoices)
-- 4. Detail/junction tables (order_items, invoice_lines)

-- Verify loading order with dependency query
SELECT
  LEVEL AS load_order,
  table_name,
  constraint_name,
  r_table_name AS parent_table
FROM (
  SELECT
    c.table_name,
    c.constraint_name,
    rc.table_name AS r_table_name
  FROM user_constraints c, user_constraints rc
  WHERE c.constraint_type = 'R'
    AND c.r_constraint_name = rc.constraint_name
)
START WITH r_table_name NOT IN (
  SELECT table_name FROM user_constraints WHERE constraint_type = 'R'
)
CONNECT BY PRIOR table_name = r_table_name;

2. Enable Constraints During ETL

-- Validate data before enabling constraints
-- Run this after each data load step
DECLARE
  v_errors NUMBER := 0;
BEGIN
  FOR fk_rec IN (
    SELECT constraint_name, table_name
    FROM user_constraints
    WHERE constraint_type = 'R'
      AND status = 'DISABLED'
  ) LOOP
    BEGIN
      EXECUTE IMMEDIATE 'ALTER TABLE ' || fk_rec.table_name ||
        ' ENABLE CONSTRAINT ' || fk_rec.constraint_name;
      DBMS_OUTPUT.PUT_LINE('Enabled: ' || fk_rec.constraint_name);
    EXCEPTION
      WHEN OTHERS THEN
        v_errors := v_errors + 1;
        DBMS_OUTPUT.PUT_LINE('FAILED: ' || fk_rec.constraint_name || ' - ' || SQLERRM);
    END;
  END LOOP;

  IF v_errors > 0 THEN
    DBMS_OUTPUT.PUT_LINE(v_errors || ' constraints could not be enabled. Review orphan data.');
  END IF;
END;
/

3. Use Deferrable Constraints for Flexibility

-- Create foreign keys as DEFERRABLE for complex transaction support
ALTER TABLE order_items
ADD CONSTRAINT fk_order_items_order
  FOREIGN KEY (order_id) REFERENCES orders(order_id)
  DEFERRABLE INITIALLY IMMEDIATE;

-- This allows temporary violations within a transaction when needed
-- while still enforcing integrity at COMMIT time

Related Errors

• ORA-02291 - Integrity constraint violated - parent key not found (on INSERT/UPDATE)
• ORA-00001 - Unique constraint violated
• ORA-02292 - Integrity constraint violated - child record found (on DELETE)
• ORA-02449 - Unique/primary keys referenced by foreign keys

Quick Reference

Emergency Response Steps

1. Identify the constraint name and the child/parent tables involved
2. Find orphan rows using NOT EXISTS or the EXCEPTIONS INTO technique
3. Decide on resolution: delete orphans, insert missing parents, or use NOVALIDATE
4. Back up orphan data before making changes
5. Clean up data and re-enable the constraint
6. Validate the constraint is fully enforced

Quick Commands

-- Find orphan rows
SELECT c.fk_column, COUNT(*) FROM child_table c
WHERE NOT EXISTS (SELECT 1 FROM parent_table p WHERE p.pk_column = c.fk_column)
AND c.fk_column IS NOT NULL
GROUP BY c.fk_column;

-- Use EXCEPTIONS INTO to find violating rowids
ALTER TABLE child_table ENABLE CONSTRAINT fk_name EXCEPTIONS INTO exceptions;

-- Delete orphans
DELETE FROM child_table WHERE fk_column NOT IN (SELECT pk_column FROM parent_table);

-- Insert missing parents
INSERT INTO parent_table (pk_column)
SELECT DISTINCT fk_column FROM child_table
WHERE fk_column NOT IN (SELECT pk_column FROM parent_table);

-- Enable without validating existing data
ALTER TABLE child_table ENABLE NOVALIDATE CONSTRAINT fk_name;

-- Validate later
ALTER TABLE child_table MODIFY CONSTRAINT fk_name VALIDATE;

Prevention Guidelines

• Load parent data first - Always insert parent records before child records
• Use EXCEPTIONS INTO - Identify specific violating rows before cleanup
• Back up before deleting - Create backup tables of orphan rows
• Consider NOVALIDATE - For data warehouses where existing data cannot be changed
• Use deferred constraints - Allow flexible transaction ordering
• Automate integrity checks - Schedule regular orphan row detection