ORA-02292 Constraint violation - child records found.
Every developer must have encountered this error once. There is one approach that we invariably take; turn on “On Delete Cascade” option while creating FK as mentioned briliiantly by Don here. I recently faced the same issue while working on something and to my amazement I found out that there was no “On Delete Cascade” option in FK and I was not allowed to create one. The problem was compounded by the fact that the parent table has more than 30 child tables and some of the child tables, in turn, have 5 or so grand child tables and even these grand child tables have children of their own.
That’s when I thought of implementing on delete cascade using just DML. The script that I have created is not the best approach, as it is Version 1.0, but it still does the job smoothly and, as one my colleague happily pointed out, lazily.
The script basically works like this:
- Insert the Table name and where clause into stack.
- Get all the child tables and corresponding column(s) for the parent table.
- Form query.
- Delete records from child table using the above query.
- If operation fails, insert that table name and corresponding where clause in stack and repeat from STEP 2.
- If operation doesn't fail and all child records deleted successfully for that parent, then POP that parent and where clause from the stack.
- Repeat the above steps till stack is empty.
The input parameters to this program is just the table name and the where condition of delete. Complexity of where condition doesn’t really count.
For example,
- To delete an employee with emp id as 1, pass
- Emp
- Emp_id = 1
- To delete all employees of dept 10, pass
- Emp
- Dept_id = 10
- To purge employee table and all it’s child tables, pass
- Emp
- 1 = 1
Third example really shows how lethal this script can be and in fact purge the whole database if the correct where condition is not passed and hence script should be used carefully.
CREATE OR REPLACE PACKAGE PKG_ON_DELETE_CASCADE AS
/***********************************************************
NAME: PKG_ON_DELETE_CASCADE
PURPOSE: This package will be used to implement ON DELETE CASACADE
functionality of Oracle.
REVISIONS:
Ver Date Author Description
--------- ---------- --------------- ------------------------------------
1.0 5/25/2011 Prateek Created this package
***********************************************************/
/*This Procedure will call ON DELETE CASCADE program in loop
*/
PROCEDURE SP_MAIN (
p_table_name IN VARCHAR2,
p_where_condition IN VARCHAR2
);
/*This Procedure will implement ON DELETE CASCADE functionality of
Oracle using DML.
*/
PROCEDURE SP_ON_DELETE_CASCADE (
p_table_name IN OUT VARCHAR2,
p_where_condition IN OUT VARCHAR2,
p_flag OUT BOOLEAN
);
END PKG_ON_DELETE_CASCADE;
/
CREATE OR REPLACE PACKAGE BODY PKG_ON_DELETE_CASCADE AS
/***********************************************************
NAME: PKG_ON_DELETE_CASCADE
PURPOSE: This package will be used to implement ON DELETE CASACADE
functionality of Oracle.
REVISIONS:
Ver Date Author Description
--------- ---------- --------------- ------------------------------------
1.0 5/25/2011 Prateek Created this package
***********************************************************/
/*This function will execute the delete query formed by SP_ON_DELETE_CASCADE.
In case of 2292 error (Child record found), it'll return the flag as 0. For
successful completion, it'll return the flag as 1.
*/
FUNCTION FN_DELETE_RECORD (
p_delete_query IN VARCHAR2
)
RETURN NUMBER;
PROCEDURE SP_MAIN (
p_table_name IN VARCHAR2,
p_where_condition IN VARCHAR2
)
IS
/**************************************************************************
NAME: SP_MAIN
PURPOSE: This Procedure will call ON DELETE CASCADE program in loop
PARAMTERES: p_table_name: Parent table name from where records needs to
to be deleted.
p_where_condition: Condition on which record needs to be deleted
For example, to delete all records from EMP where
FIRST_NAME is John, the parameters will be
p_table_name: EMP
p_where_condition: FIRST_NAME = 'JOHN'
REVISIONS:
Ver Date Author Description
----- ---------- --------- ----------------------------------
1.0 5/25/2011 Prateek Initial Coding
**************************************************************************/
--Local Variable Declaration
TYPE tab_table_name IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER;
TYPE tab_where_clause IS TABLE OF VARCHAR2(10000) INDEX BY BINARY_INTEGER;
rec_table_name tab_table_name;
rec_where_clause tab_where_clause;
ln_row_index NUMBER := 0;
lv_table_name VARCHAR2(30);
lv_where_condition VARCHAR2(10000);
lb_flag BOOLEAN;
lv_delete_query VARCHAR2(10000);
BEGIN
--Form the final delete query
lv_delete_query := 'DELETE FROM ' || p_table_name || ' WHERE ' || p_where_condition;
--Insert the parameters into stack
ln_row_index := ln_row_index + 1;
rec_table_name(ln_row_index) := p_table_name;
rec_where_clause(ln_row_index) := p_where_condition;
--Loop until the ln_row_index is 0.
WHILE ln_row_index > 0
LOOP
DBMS_OUTPUT.PUT_LINE('Number of records in stack: ' || ln_row_index);
--Get the table name and where clause from array
lv_table_name := rec_table_name(ln_row_index);
lv_where_condition := rec_where_clause(ln_row_index);
DBMS_OUTPUT.PUT_LINE('Deleting child records for ' || lv_table_name);
DBMS_OUTPUT.PUT_LINE('Using where clause: ' || lv_where_condition);
--Call SP_ON_DELETE_CASCADE
SP_ON_DELETE_CASCADE (
lv_table_name,
lv_where_condition,
lb_flag
);
--If flag is TRUE, then decrement ln_row_index
IF (lb_flag) THEN
--All child records deleted successfully. Delete the entry from stack.
rec_table_name.DELETE(ln_row_index);
rec_where_clause.DELETE(ln_row_index);
--Decrement row index
ln_row_index := ln_row_index - 1;
ELSE
--There are grandchild records for the child records. Insert an entry into stack.
ln_row_index := ln_row_index + 1;
rec_table_name(ln_row_index) := lv_table_name;
rec_where_clause(ln_row_index) := lv_where_condition;
END IF;
END LOOP;
DBMS_OUTPUT.PUT_LINE('Deleting final parent record using query: '|| lv_delete_query);
--Delete parent record.
EXECUTE IMMEDIATE lv_delete_query;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
DBMS_OUTPUT.PUT_LINE('Exception encountered in SP_MAIN: ');
DBMS_OUTPUT.PUT_LINE(SQLCODE || ' ' || SQLERRM);
RAISE;
END SP_MAIN;
PROCEDURE SP_ON_DELETE_CASCADE (
p_table_name IN OUT VARCHAR2,
p_where_condition IN OUT VARCHAR2,
p_flag OUT BOOLEAN
)
IS
/**************************************************************************
NAME: SP_MAIN
PURPOSE: This Procedure will call ON DELETE CASCADE program in loop
PARAMTERES: p_table_name: Parent table name from where records needs to
to be deleted.
p_where_condition: Condition on which record needs to be deleted
For example, to delete all records from EMP where
FIRST_NAME is John, the parameters will be
p_table_name: EMP
p_where_condition: FIRST_NAME = 'JOHN'
REVISIONS:
Ver Date Author Description
----- ---------- --------------- ----------------------------------
1.0 5/25/2011 Prateek Initial Coding
**************************************************************************/
--Local Variable Declaration
lv_delete_query VARCHAR2(10000);
ln_return_code NUMBER;
function_error EXCEPTION;
BEGIN
--Initialize out parameter
p_flag := TRUE;
--Get all child tables for parent table
FOR ln_child IN (
SELECT PARENT_TABLE,
REPLACE(RTRIM (XMLAGG (XMLELEMENT (E, PARENT_COL || ',')).EXTRACT ('//text()'),','), ',', '||''-''||') PARENT_COL,
CHILD_TABLE,
REPLACE(RTRIM (XMLAGG (XMLELEMENT (E, CHILD_COL || ',')).EXTRACT ('//text()'),','), ',', '||''-''||') CHILD_COL,
CONSTRAINT_NAME
FROM (SELECT AC.TABLE_NAME CHILD_TABLE,
ACC2.COLUMN_NAME CHILD_COL,
ACC1.TABLE_NAME PARENT_TABLE,
ACC1.COLUMN_NAME PARENT_COL,
AC.CONSTRAINT_NAME
FROM ALL_CONSTRAINTS AC,
ALL_CONS_COLUMNS ACC1,
ALL_CONS_COLUMNS ACC2
WHERE AC.OWNER = ACC1.OWNER
AND AC.OWNER = ACC2.OWNER
AND AC.R_CONSTRAINT_NAME = ACC1.CONSTRAINT_NAME
AND AC.CONSTRAINT_NAME = ACC2.CONSTRAINT_NAME
AND ACC1.POSITION = ACC2.POSITION
AND AC.CONSTRAINT_TYPE = 'R'
AND AC.OWNER = ‘SCOTT’
AND ACC1.TABLE_NAME = p_table_name)
GROUP BY PARENT_TABLE, CHILD_TABLE, CONSTRAINT_NAME
)
LOOP
--Form delete query
lv_delete_query := 'DELETE FROM '|| ln_child.CHILD_TABLE ||
' WHERE ' || ln_child.CHILD_COL || ' IN ' ||
' ( SELECT ' || ln_child.PARENT_COL || ' FROM ' || ln_child.PARENT_TABLE
|| ' WHERE ' || p_where_condition || ' ) ';
dbms_output.put_line('Query : ' || lv_delete_query);
--Call the function to delete the query
ln_return_code := FN_DELETE_RECORD(lv_delete_query);
--Check the return code for errors
IF ln_return_code = -1 THEN
--Exception in deleting rows
RAISE function_error;
ELSIF ln_return_code = 0 THEN
--Child record found. Exit the loop.
--Get the table name for which child records found.
p_table_name := ln_child.CHILD_TABLE;
p_where_condition := ln_child.CHILD_COL || ' IN ' || ' ( SELECT ' ||
ln_child.PARENT_COL || ' FROM ' ||
ln_child.PARENT_TABLE || ' WHERE ' ||
p_where_condition || ' ) ';
--Set flag to FALSE
p_flag := FALSE;
DBMS_OUTPUT.PUT_LINE('Child records found for table: ' || p_table_name);
DBMS_OUTPUT.PUT_LINE('New WHERE Condition: ' || p_where_condition);
EXIT;
END IF;
END LOOP;
EXCEPTION
WHEN FUNCTION_ERROR THEN
ROLLBACK;
DBMS_OUTPUT.PUT_LINE('Exception encountered in SP_ON_DELETE_CASCADE: ');
DBMS_OUTPUT.PUT_LINE(SQLCODE || ' ' || SQLERRM);
RAISE;
WHEN OTHERS THEN
ROLLBACK;
DBMS_OUTPUT.PUT_LINE('Exception encountered in SP_ON_DELETE_CASCADE: ');
DBMS_OUTPUT.PUT_LINE(SQLCODE || ' ' || SQLERRM);
RAISE;
END SP_ON_DELETE_CASCADE;
FUNCTION FN_DELETE_RECORD (
p_delete_query IN VARCHAR2
)
RETURN NUMBER
IS
/**************************************************************************
NAME: FN_DELETE_RECORD
PURPOSE: This function will execute the delete query formed by
SP_ON_DELETE_CASCADE. In case of 2292 error (Child record found),
it'll return the flag as 0. For successful completion, it'll return
the flag as 1. In other cases, it'll return the flag as -1.
PARAMTERES: p_delete_query: Query to be executed, which will delete the record.
REVISIONS:
Ver Date Author Description
----- ---------- --------------- ----------------------------------
1.0 5/25/2011 Prateek Initial Coding
**************************************************************************/
--Local Variable Declaration
BEGIN
--Execute the statement
EXECUTE IMMEDIATE p_delete_query;
DBMS_OUTPUT.PUT_LINE('Number of rows deleted: ' || SQL%ROWCOUNT );
--For successful completion, return 1
RETURN 1;
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE = -2292 THEN
DBMS_OUTPUT.PUT_LINE('Child record found.');
RETURN 0;
ELSE
DBMS_OUTPUT.PUT_LINE('Exception encountered in FN_DELETE_RECORD: ');
DBMS_OUTPUT.PUT_LINE(SQLCODE || ' ' || SQLERRM);
RETURN -1;
RAISE;
END IF;
END FN_DELETE_RECORD;
END PKG_ON_DELETE_CASCADE;
So enjoy deleting!!!
No comments:
Post a Comment