Find invalid rows (when enabling foreign keys)

Y

You may get into situation when you have added or enabled foreign key and it can't be activated because you have integrity issues. Here is a solution how to find these naugty rows.

First, create table for gathering results.

CREATE TABLE invalid_keys_tmp (
    table_name          VARCHAR2(128)   NOT NULL,
    column_name         VARCHAR2(128)   NOT NULL,
    parent_table        VARCHAR2(128)   NOT NULL,
    parent_column       VARCHAR2(128    NOT NULL,
    row_id              ROWID           NOT NULL,
    --
    CONSTRAINT pk_invalid_keys
        PRIMARY KEY (table_name, column_name, row_id)
);

Check all tables for invalid keys, ignore NULL values. You may want to extend this for compound keys and maybe filter tables to not checking whole schema in single transaction.

BEGIN
    DELETE FROM invalid_keys_tmp;
    --
    FOR c IN (
        SELECT
            fn.table_name               AS foreign_table,
            fc.column_name              AS foreign_column,
            pc.table_name               AS parent_table,
            pc.column_name              AS parent_column
        FROM user_constraints fn
        JOIN user_constraints pn
            ON pn.constraint_name       = fn.r_constraint_name
        JOIN user_cons_columns fc
            ON fc.constraint_name       = fn.constraint_name
        JOIN user_cons_columns pc
            ON pc.constraint_name       = pn.constraint_name
        LEFT JOIN user_cons_columns rc
            ON rc.table_name            = pc.table_name
            AND rc.column_name          = pc.column_name
            AND rc.constraint_name      != pc.constraint_name
            AND rc.position             = 1
        WHERE fn.constraint_type        = 'R'
            AND fc.position             = 1
            AND pc.position             = 1
        ORDER BY 1, 2
    ) LOOP
        EXECUTE IMMEDIATE
            'INSERT INTO invalid_keys_tmp' ||
            ' SELECT' ||
            '    ''' || c.foreign_table  || ''',' ||
            '    ''' || c.foreign_column || ''',' ||
            '    ''' || c.parent_table   || ''',' ||
            '    ''' || c.parent_column  || ''',' ||
            '    ROWID' ||
            ' FROM '  || c.foreign_table  || ' f' ||
            ' WHERE ' || c.foreign_column || ' IS NOT NULL' ||
            '    AND NOT EXISTS (' ||
            '        SELECT '  || c.parent_column ||
            '        FROM '    || c.parent_table  || ' p' ||
            '        WHERE p.' || c.parent_column || ' = f.' || c.foreign_column ||
            '    )';
    END LOOP;
END;
/

Quick overview of gathered violations:

SELECT table_name, column_name, parent_table, parent_column, COUNT(0) AS invalid_rows
FROM invalid_keys_tmp
GROUP BY table_name, column_name, parent_table, parent_column
ORDER BY table_name, column_name, parent_table, parent_column;

And here is a query which will show you the wrong rows in desired table.

SELECT *
FROM &TABLE_NAME
WHERE ROWID IN (
    SELECT row_id
    FROM invalid_keys_tmp
    WHERE table_name = UPPER('&TABLE_NAME')
);

SELECT
    'SELECT * FROM ' || LOWER(table_name) ||
    ' WHERE ROWID IN (' ||
    '     SELECT row_id FROM invalid_keys_tmp' ||
    '     WHERE table_name = ''' || table_name || '''' ||
    ');' AS q
FROM invalid_keys_tmp
GROUP BY table_name, column_name, parent_table, parent_column
ORDER BY table_name, column_name, parent_table, parent_column;

You may want to remove the table at the end.

DROP TABLE invalid_keys_tmp PURGE;