How to solve ORA-02291: integrity constraint violated - parent key not found .
=================================================================================
SQL> insert into emp values(7935,'XXX','XXXX',7782,'23-JAN-83',10000,null,50) ;
insert into emp values(7935,'XXX','XXXX',7782,'23-JAN-83',10000,null,50)
*
ERROR at line 1:
ORA-02291: integrity constraint (ANUJ1.FK_DEPTNO) violated - parent key not found
ALTER TABLE anuj1.dept DISABLE PRIMARY KEY;
ww
alter table anuj1.dept disable primary key cascade ;
inserted wrong row !!! in dept table
insert into emp values(7935,'XXX','XXXX',7782,'23-JAN-83',10000,null,50) ;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7900 JAMES CLERK 7698 03-DEC-81 950 30
7934 MILLER CLERK 7782 23-JAN-82 1300 10
7935 XXX XXXX 7782 23-JAN-83 10000 50 <<<<<
alter table anuj1.dept enable primary key cascade ;
SQL> alter table anuj1.dept enable primary key ; <<<< Primary Key enabled !!!
Table altered.
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7900 JAMES CLERK 7698 03-DEC-81 950 30
7934 MILLER CLERK 7782 23-JAN-82 1300 10
7935 XXX XXXX 7782 23-JAN-83 10000 50 <<<<<
set linesize 300
COLUMN owner FORMAT A25
COLUMN table_name FORMAT A25
COLUMN constraint_name FORMAT A25
COLUMN constraint_type FORMAT A25
COLUMN status FORMAT A25
COLUMN validated FORMAT A25
SELECT
owner,
table_name,
constraint_name,
constraint_type,
status,
validated
FROM
dba_constraints
WHERE 1=1
and status = 'DISABLED'
and owner='ANUJ1'
ORDER BY table_name, constraint_type;
OWNER TABLE_NAME CONSTRAINT_NAME CONSTRAINT_TYPE STATUS VALIDATED
------------------------- ------------------------- ------------------------- ------------------------- ------------------------- -------------------------
ANUJ1 BIN$RFG84ieDVBXgYyL1akC3x BIN$RFG84ieCVBXgYyL1akC3x P DISABLED NOT VALIDATED
A==$0 A==$0
ANUJ1 EMP FK_DEPTNO R DISABLED NOT VALIDATED
2 rows s
set linesize 300
COLUMN owner FORMAT A25
COLUMN table_name FORMAT A25
COLUMN constraint_name FORMAT A25
COLUMN constraint_type FORMAT A25
COLUMN status FORMAT A25
COLUMN validated FORMAT A25
SELECT
owner,
table_name,
constraint_name,
status,
validated
FROM
all_constraints
WHERE
status != 'ENABLED'
--AND validated = 'NOT VALIDATED'
and owner='ANUJ1'
ORDER BY owner, table_name;
set serveroutput on
define O='ANUJ1' -----<<<<<<<<<<<<
DECLARE
v_sql_stmt VARCHAR2(200);
BEGIN
DBMS_OUTPUT.PUT_LINE('-- Starting Constraint Enable Script --');
DBMS_OUTPUT.PUT_LINE('--');
FOR rec IN (
SELECT
owner,
table_name,
constraint_name
FROM
all_constraints -- Use ALL_CONSTRAINTS to cover all schemas you can see
WHERE
status = 'DISABLED'
-- Optional: Filter by specific schema if needed (e.g., AND owner = 'ANUJ1')
AND owner IN ('&O') -- Exclude system constraints
ORDER BY
owner, table_name, constraint_name
) LOOP
-- Generate the ENABLE statement, using VALIDATE (default) to check data integrity
v_sql_stmt := 'ALTER TABLE ' || rec.owner || '.' || rec.table_name || ' ENABLE CONSTRAINT ' || rec.constraint_name || ';';
-- Print the generated SQL statement
DBMS_OUTPUT.PUT_LINE(v_sql_stmt);
END LOOP;
DBMS_OUTPUT.PUT_LINE('--');
DBMS_OUTPUT.PUT_LINE('-- Constraint Enable Script Generated Successfully --');
END;
/
ALTER TABLE ANUJ1.EMP ENABLE CONSTRAINT FK_DEPTNO;
SQL> SQL> ALTER TABLE ANUJ1.EMP ENABLE CONSTRAINT FK_DEPTNO;
ALTER TABLE ANUJ1.EMP ENABLE CONSTRAINT FK_DEPTNO
*
ERROR at line 1:
ORA-02298: cannot validate (ANUJ1.FK_DEPTNO) - parent keys not found
COLUMN foreign_key_owner FORMAT A25
COLUMN foreign_key_name FORMAT A25
COLUMN child_table FORMAT A25
COLUMN foreign_key_columns FORMAT A25
COLUMN parent_owner FORMAT A25
COLUMN parent_table FORMAT A25
COLUMN parent_key_columns FORMAT A25
SELECT
c.owner AS foreign_key_owner,
c.constraint_name AS foreign_key_name,
cc.table_name AS child_table,
LISTAGG(cc.column_name, ', ') WITHIN GROUP (ORDER BY cc.position) AS foreign_key_columns,
r.owner AS parent_owner,
r.table_name AS parent_table,
LISTAGG(rc.column_name, ', ') WITHIN GROUP (ORDER BY rc.position) AS parent_key_columns
FROM all_constraints c
JOIN all_cons_columns cc ON c.owner = cc.owner AND c.constraint_name = cc.constraint_name
JOIN all_constraints r ON c.r_owner = r.owner AND c.r_constraint_name = r.constraint_name
JOIN all_cons_columns rc ON r.owner = rc.owner AND r.constraint_name = rc.constraint_name AND cc.position = rc.position
WHERE
c.owner = 'ANUJ1'
AND c.constraint_name = 'FK_DEPTNO'
AND c.constraint_type = 'R'
GROUP BY c.owner, c.constraint_name, cc.table_name, r.owner, r.table_name;
FOREIGN_KEY_OWNER FOREIGN_KEY_NAME CHILD_TABLE FOREIGN_KEY_COLUMNS PARENT_OWNER PARENT_TABLE PARENT_KEY_COLUMNS
------------------------- ------------------------- ------------------------- ------------------------- ------------------------- ------------------------- -------------------------
ANUJ1 FK_DEPTNO EMP DEPTNO ANUJ1 DEPT DEPTNO
SELECT DISTINCT e.DEPTNO AS offending_deptno
FROM ANUJ1.EMP e
WHERE
e.DEPTNO IS NOT NULL
MINUS ------<<<<<<
SELECT d.DEPTNO
FROM ANUJ1.DEPT d
;
DELETE FROM ANUJ1.EMP e
WHERE e.DEPTNO IS NOT NULL
AND e.DEPTNO IN (
SELECT e_bad.DEPTNO
FROM ANUJ1.EMP e_bad
MINUS
SELECT d.DEPTNO
FROM ANUJ1.DEPT d
);
1 row deleted.
SQL> ALTER TABLE ANUJ1.EMP ENABLE CONSTRAINT FK_DEPTNO;
Table altered.
COLUMN foreign_key_owner FORMAT A25
COLUMN foreign_key_name FORMAT A25
COLUMN child_table FORMAT A25
COLUMN foreign_key_columns FORMAT A25
COLUMN parent_owner FORMAT A25
COLUMN parent_table FORMAT A25
COLUMN parent_key_columns FORMAT A25
COLUMN status FORMAT A10
COLUMN validated FORMAT A15
SELECT
c.owner AS foreign_key_owner,
c.constraint_name AS foreign_key_name,
cc.table_name AS child_table,
LISTAGG(cc.column_name, ', ') WITHIN GROUP (ORDER BY cc.position) AS foreign_key_columns,
r.owner AS parent_owner,
r.table_name AS parent_table,
LISTAGG(rc.column_name, ', ') WITHIN GROUP (ORDER BY rc.position) AS parent_key_columns,
c.status AS status,
c.validated AS validated
FROM all_constraints c
JOIN all_cons_columns cc ON c.owner = cc.owner AND c.constraint_name = cc.constraint_name
JOIN all_constraints r ON c.r_owner = r.owner AND c.r_constraint_name = r.constraint_name
JOIN all_cons_columns rc ON r.owner = rc.owner AND r.constraint_name = rc.constraint_name AND cc.position = rc.position
WHERE 1=1
and c.owner = 'ANUJ1'
-- AND c.constraint_name = 'FK_DEPTNO'
-- AND c.constraint_type = 'R'
GROUP BY c.owner, c.constraint_name, cc.table_name, r.owner, r.table_name, c.status, c.validated;
FOREIGN_KEY_OWNER FOREIGN_KEY_NAME CHILD_TABLE FOREIGN_KEY_COLUMNS PARENT_OWNER PARENT_TABLE PARENT_KEY_COLUMNS STATUS VALIDATED
------------------------- ------------------------- ------------------------- ------------------------- ------------------------- ------------------------- ------------------------- ---------- ---------------
ANUJ1 FK_DEPTNO EMP DEPTNO ANUJ1 DEPT DEPTNO ENABLED VALIDATED
ANUJ1 FK_INVITDEL T_INVOICE_ITEM_DELIVERY INVOICE_ID, PRODUCT_ID ANUJ1 T_INVOICE_ITEM INVOICE_ID, PRODUCT_ID ENABLED VALIDATED
ANUJ1 FK_PROD_PROD T_PRODUCT PRODUCT_EQUIVALENT ANUJ1 T_PRODUCT PRODUCT_ID ENABLED VALIDATED
ANUJ1 FK_PROD_PROV T_PRODUCT PROVIDER_ID ANUJ1 T_PROVIDER PROVIDER_ID ENABLED VALIDATED
ANUJ1 FK_INVITEM_INVOICE T_INVOICE_ITEM INVOICE_ID ANUJ1 T_INVOICE INVOICE_ID ENABLED VALIDATED
ANUJ1 FK_INVITEM_PRODUCT T_INVOICE_ITEM PRODUCT_ID ANUJ1 T_PRODUCT PRODUCT_ID ENABLED VALIDATED
ANUJ1 FK_PRODWARE_PRODUCT T_PRODUCT_WAREHOUSE PRODUCT_ID ANUJ1 T_PRODUCT PRODUCT_ID ENABLED VALIDATED
ANUJ1 FK_PRODWARE_WAREHOUSE T_PRODUCT_WAREHOUSE WAREHOUSE_ID ANUJ1 T_WAREHOUSE WAREHOUSE_ID ENABLED VALIDATED
8 rows selected.
SET SERVEROUTPUT ON SIZE UNLIMITED;
DECLARE
v_sql_stmt VARCHAR2(500);
v_target_owner CONSTANT VARCHAR2(128) := 'ANUJ1' -- or keep NULL to target all
BEGIN
DBMS_OUTPUT.PUT_LINE('-- START: Dynamic Primary Key DISABLE Script (DBA_CONSTRAINTS) --');
DBMS_OUTPUT.PUT_LINE('--');
FOR rec IN (
SELECT
owner,
table_name
FROM
dba_constraints
WHERE
constraint_type = 'P' -- 'P' for Primary Key
AND status = 'ENABLED'
-- Filter out system schemas
AND owner NOT IN ('SYS', 'SYSTEM', 'OUTLN', 'DBSNMP', 'CTXSYS')
-- Optional: Filter by specific owner if V_TARGET_OWNER is set
AND (v_target_owner IS NULL OR owner = v_target_owner)
ORDER BY owner, table_name
) LOOP
-- Generate the DISABLE command with CASCADE to handle foreign key dependencies
v_sql_stmt := 'ALTER TABLE ' || rec.owner || '.' || rec.table_name || ' DISABLE PRIMARY KEY CASCADE;';
-- Print the generated SQL statement
DBMS_OUTPUT.PUT_LINE(v_sql_stmt);
END LOOP;
DBMS_OUTPUT.PUT_LINE('--');
DBMS_OUTPUT.PUT_LINE('-- END: Primary Key DISABLE Script Generated. Copy and execute above statements. --');
END;
/
===
SET SERVEROUTPUT ON SIZE UNLIMITED;
DECLARE
v_sql_stmt VARCHAR2(500);
-- Define the schema owner you want to target, or NULL for all non-system schemas
v_target_owner CONSTANT VARCHAR2(128) := 'ANUJ1' -- or keep NULL to target all
BEGIN
DBMS_OUTPUT.PUT_LINE('-- START: Dynamic Primary Key ENABLE Script (DBA_CONSTRAINTS) --');
DBMS_OUTPUT.PUT_LINE('--');
FOR rec IN (
SELECT
owner,
table_name
FROM
dba_constraints
WHERE
constraint_type = 'P' -- 'P' for Primary Key
AND status = 'DISABLED'
-- Filter out system schemas
AND owner NOT IN ('SYS', 'SYSTEM', 'OUTLN', 'DBSNMP', 'CTXSYS')
-- Optional: Filter by specific owner if V_TARGET_OWNER is set
AND (v_target_owner IS NULL OR owner = v_target_owner)
ORDER BY
owner, table_name
) LOOP
-- Generate the ENABLE command (default is ENABLE VALIDATE)
v_sql_stmt := 'ALTER TABLE ' || rec.owner || '.' || rec.table_name || ' ENABLE PRIMARY KEY;';
-- Print the generated SQL statement
DBMS_OUTPUT.PUT_LINE(v_sql_stmt);
END LOOP;
DBMS_OUTPUT.PUT_LINE('--');
DBMS_OUTPUT.PUT_LINE('-- END: Primary Key ENABLE Script Generated. Copy and execute above statements. --');
END;
/
Thursday, 4 December 2025
How to solve ORA-02291: integrity constraint violated - parent key not found
Subscribe to:
Comments (Atom)
