Function for Cascade Deletion of any Single Oracle Database Row
Simplify Row Deletion with Automatic Handling of Dependent Foreign Keys without Oracle Table Delete Cascade
Specializing in innovative, visionary and strategic application of technology to create value and solve real world problems through a virtual CIO client-service model. Services include vision and strategic planning; creative problem solving and process optimization; application architecting, Oracle database & PL/SQL, Oracle APEX, Forms migration, and web design, build, and support. Experienced certified Oracle Database Administrator, Oracle Cloud Infrastructure, and Linux system administration team as well.
Context
Oracle foreign key constraints in database tables ensure referential integrity between two tables. This means that a column value in one table must match at least one value in the other table, or the column value can be null. For example, if the
Customer_IDin the orders table is 2435, there must be aCustomer_ID2435 in the Customer table. If you try to delete 2435 from the Customer table, Oracle will show an error because the value exists in a related table, enforcing the match.During the table creation process or by using ALTER TABLE, Oracle offers the "Delete Cascade" option. This option automatically removes all dependent rows if the primary row is deleted.
For example:
ALTER TABLE Customer_Orders ADD CONSTRAINT Orders_Customer_ID_FK FOREIGN KEY (Customer_IDe) REFERENCES Customer (Customer_ID) ON DELETE CASCADE;
Delete Cascade can be very useful, BUT the risk of causing unwanted cascading deletions accidentally is potentially high and can be undesirable depending on the use case.
Objective
Delete any table/row combination, including dependent children foreign keys, with a single function, without the need for “Delete Cascade” in the table definition. Returns what will be deleted, or what was deleted depending on whether the function was called as Log Only.
Benefits
Single Function: Single database function that can be called for any table/row combination that recursively identifies child dependencies and deletes them, automatically adapting to schema and data changes. If change to delete process required, only one place needs to be changed.
Limited Use: Function execution can be limited to instances requiring delete cascade reducing the need to include “Delete Cascade” in the table definition, keeping the table more protected.
Logging: Ability to review specific child deletions that will be made before they occur provides the ability for an extra layer of review/reporting. Log information after deletion supports system event tracking.
APEX Modal Page:
Single Page: Reusable because it can be redirected to from anywhere in APEX. If a change to the delete process is required, only one place needs to be updated.
Review: Present deletions that will occur for review prior to actual deletion and require confirmation.
Security: Easier to limit access because functionality is only in one place. Could simply be kept in a system utility area.
- 💡Caution: If there are many dependencies an APEX Gateway timeout error could occur if it takes too long to return a result. The delete could be run as a background process in that case once the desire to delete is confirmed. That leads to a larger question of whether a delete with so many dependencies is prudent…
Components How To
Create a single row delete cascade function:
Parameters: Accept Schema, Table_Name, Primary_ID, and Log Only Yes/No parameters.
Return an HTML-formatted list of tables, constraint column names, and child table primary key values that depend on the row to be deleted and will be removed due to a delete cascade action.
Cycle through dependent tables and rows, deleting each one, starting with the lowest dependency, just as the Oracle Delete Cascade action would do.
Delete cascade at the table / row combination level for any table, and automatically adjust for schema and table updates, additions, and deletions.
Following is the body of the function including comments and then highlights.
Create Or Replace Function Row_Delete_Cascade (
p_Schema In Varchar2
,p_Table_Name IN VARCHAR2
,p_Primary_ID IN NUMBER
,p_Log_Only_YN IN Varchar2
)
Return Varchar2
AS
Type t_ID_List Is Table of Number;
Type t_Col_List Is Table of Varchar2(128);
v_SQL VARCHAR2(32767 CHAR);
v_Table_Name Varchar2(128 ); -- Size should be in bytes
v_Schema Varchar2(128 ); -- Size should be in bytes
v_Primary_Column Varchar2(128);
v_Child_IDs t_ID_List;
v_SY_Count Integer;
PK_Data_Type VARCHAR2(30 CHAR);
v_Return_Log Varchar2(32767 CHAR);
v_Return_log_Sub Varchar2(32767 CHAR);
v_Logged Boolean;
v_Log_Only_YN Varchar2(1 CHAR);
-- All Table Names with corresponding Foreign Key Column Names
-- that rely on p_Schema.p_Table_Name
-- Exclude tables that are the basis for a materialized view.
--
Cursor FK_cur IS
Select Upper(a.Table_Name) AS FK_Table
,Upper(a.Column_Name) AS FK_Column
From All_Cons_Columns a
Join All_Constraints c ON a.Constraint_Name = c.Constraint_Name
Where c.Constraint_Type = 'R'
And c.r_constraint_name IN (
Select Constraint_Name
FROM All_Constraints
WHERE Owner = v_Schema
And Table_Name = v_Table_Name
AND Constraint_Type = 'P'
And Table_Name Not In (Select Object_Name
From All_Objects
Where Object_Name = a.Table_Name
And Object_Type = 'MATERIALIZED VIEW'
)
);
Begin
v_Table_Name := Upper(Trim(p_Table_Name));
v_Schema := Upper(Trim(p_Schema));
v_Log_Only_YN := Upper(Trim(p_Log_Only_YN));
-- Fetch the column_Name of p_Table_Name's Primary Key
Select Column_Name
Into v_Primary_Column
FROM All_Cons_Columns
WHERE
Owner = v_Schema
And Table_Name = v_Table_Name
And Constraint_Name In (
Select Constraint_Name
From All_Constraints
WHERE Owner = v_Schema
And Table_Name = v_Table_Name
AND Constraint_Type = 'P'
)
;
If v_Primary_Column Is Not Null Then
-- p_Table_Name has a primary key assigned.
-- Determine data type of p_Table_Name's
-- primary key column.
Select Data_Type
Into PK_Data_Type
FROM All_Tab_Columns
WHERE Owner = v_Schema
And Table_Name = v_Table_Name
AND Column_Name = v_Primary_Column;
v_Return_Log := '<b>Primary Delete: ' || v_Schema || '.' || v_Table_Name
|| '.' || v_Primary_Column || ' : ' || p_Primary_ID
|| ' Type: ' || PK_Data_Type || '</b><br>';
FOR FK_Rec In FK_Cur LOOP
-- For each child, grandchild, etc. dependent table
Declare
v_Count Integer;
Child_PK_Cols t_Col_List;
Child_PK_Type VARCHAR2(30);
Begin
-- Get child table's PK column Name.
Select Column_Name Bulk Collect
Into Child_PK_Cols
From All_Cons_Columns
Where Owner = v_Schema
And Table_Name = Upper(FK_Rec.FK_Table)
And Constraint_Name In (
Select Constraint_Name
From All_Constraints
Where Owner = v_Schema
And Table_Name = Upper(FK_Rec.FK_Table)
And Constraint_Type = 'P'
)
;
-- Determine child primary key column data type.
Select Data_Type
Into Child_PK_Type
From All_Tab_Columns
Where Table_Name = UPPER(fk_rec.fk_table)
And Owner = v_Schema
AND Column_Name = Child_PK_Cols(1);
-- Fetch child primary key values that are dependents
-- of p_Table_Name primary key value.
-- Handle cases where the child primary key column is type Raw
-- for example if SYS_GUID(). If alphanumeric this will error.
--
If Child_PK_Type Like 'RAW%' Then
v_SQL := 'SELECT TO_NUMBER(RAWTOHEX(' || Child_PK_Cols(1) || ')) FROM ' || (v_Schema || '.' || FK_Rec.FK_Table) ||
' WHERE ' || FK_Rec.FK_Column || ' = :id';
Else
v_SQL := 'SELECT ' || Child_PK_Cols(1) || ' FROM ' || (v_Schema || '.' || FK_Rec.FK_Table) ||
' WHERE ' || FK_Rec.FK_Column || ' = :id';
End If;
v_Logged := FALSE;
EXECUTE IMMEDIATE v_SQL Bulk Collect Into v_Child_IDs Using p_Primary_ID;
v_SY_Count := v_Child_IDs.Count;
-- Recursively delete each child row
FOR i IN 1 .. v_Child_IDs.COUNT
Loop
v_Return_Log_Sub := SY_Del_Primary_Key_Row_Children( p_Schema => p_Schema
,p_Table_Name => fk_rec.fk_table
,p_Primary_ID => v_Child_IDs(i)
,p_Log_Only_YN=> p_Log_Only_YN
);
If Not v_Logged Then
v_Return_Log := v_Return_Log
|| ' Child: ' || v_Schema || '.' || FK_Rec.FK_Table || '.' || FK_Rec.FK_Column || '<br>';
v_Logged := TRUE;
End If;
v_Return_Log := v_Return_Log
|| ' Delete ID: ' || v_Child_IDs(i) || '<br>';
End Loop;
-- Delete child rows referencing current row
If Child_PK_Type Like 'RAW%' Then
v_SQL := 'DELETE FROM ' || (v_Schema || '.' || FK_Rec.FK_Table) ||
' WHERE ' || FK_Rec.FK_Column || ' = :id';
Else
v_SQL := 'DELETE FROM ' || (v_Schema || '.' || FK_Rec.FK_Table) ||
' WHERE ' || FK_Rec.FK_Column || ' = :id';
End If;
If v_Log_Only_YN = 'N' Then
EXECUTE IMMEDIATE v_sql USING p_Primary_ID;
End If;
END;
END LOOP;
-- Delete p_Table_Name row with primary key value of p_Primary_ID in schema p_Schema.
If PK_Data_Type Like 'RAW%' Then
v_sql := 'DELETE FROM ' || (v_Schema || '.' || v_Table_Name) || ' WHERE ' || v_Primary_Column || ' = UTL_RAW.CAST_TO_RAW(:id)';
Else
v_sql := 'DELETE FROM ' || (v_Schema || '.' || v_Table_Name) || ' WHERE ' || v_Primary_Column || ' = :id';
End If;
If v_Log_Only_YN = 'N' Then
EXECUTE IMMEDIATE v_sql USING p_Primary_ID;
End If;
End If;
Return v_Return_Log;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
RAISE;
END;
/
Notes:
The function returns a list of the primary and child table information that is useful for reviewing what will be deleted before actually deletion (p_Log_Only_YN = ‘Y’), and for reviewing and tracking what actually was deleted (p_Log_Only_YN = ‘N’). Of course, review after deletion requires good historical logging to translate the primary ID values of now deleted rows that no longer exist…
The return list only consists of table information for child dependencies that have rows to be deleted. If a complete list of dependencies is desired, even if there are no matching rows move the following section:
If Not v_Logged Then v_Return_Log := v_Return_Log || ' Child: ' || v_Schema || '.' || FK_Rec.FK_Table || '.' || FK_Rec.FK_Column || '<br>'; v_Logged := TRUE; End If;Above
FOR i IN 1 .. v_Child_IDs.COUNTAnd remove references to v_Logged so it will always add the current dependent child table to the return log value.
The function can be made more universal by adding a parameter to only delete the primary table/row combination, resulting in an error if foreign key dependencies exist.
If your organization has standards for delete procedures, adjust the v_SQL delete statements to use those standard procedures instead of direct SQL "Delete for" statements. This approach maintains existing delete procedures that might include other actions, prevents foreign key errors since this function orders actions based on dependencies, and does not interfere with this function.
Identification of column Number vs. RAW data type is important so they are handle properly as can be seen in the Select and Delete statement construction.
The cursor specifically excludes materialized views from being considered. This might seem unnecessary because they are a “view”, but materialized views create underlying tables that could be confused for tables that need to have rows deleted.
Consider creating a table white and/or blacklist that is checked prior to deletion to only allow certain tables to have rows deleted in this manner, and/or prevent any rows from being deleted from a table in this manner, respectively.
Be sure to add your own style of error management, system event tracking, and parameter value validation.
Single Row Delete Cascade APEX Modal Dialog
Create a single APEX Modal Dialog page that users can access from anywhere to review the potential impact of deleting a row. They can then choose to cancel or proceed with the deletion.
The page takes function parameters from a redirect like schema, table name, primary ID, and a descriptive heading (blurred out in image), such as "Sam Smith ID 2435," to describe the Customer row to be deleted. Passing the heading makes it easy to appropriately adapt the heading for the type of data being deleted, i.e. orders, employees, inventory, etc.
Before Page header, the page runs a process that calls the row delete function in Log Only mode. It saves the return value of rows to be deleted in a Rich Text Page Item for viewing. This log data allows users to review the information and decide whether to cancel or confirm the deletion using buttons at the bottom of the page.
The Delete Button calls the delete row function again, but this time with p_Log_Only_YN = 'N'.
An example APEX page is:
A downside is that the list displays table names and primary ID values, which require users to have more knowledge and the ability to search using primary IDs if they want to verify one or more rows. To make this more user-friendly, use a lookup table to translate table names into user-friendly names. Translating the primary ID values would need adding a "v_SQL" select statement/execute immediate that queries row data and adds it to the log.
Of course, it doesn’t hurt to confirm the confirmation of the delete…
Conclusion
The row delete cascade function offers an alternative to the Oracle table “Delete Cascade” when deciding which table or row to delete and when Delete Cascade is suitable. This function requires a deliberate decision to use delete cascade, reducing risk from accidental deletions, like removing a product from the product table and unintentionally erasing inventory, orders, commissions, and more (and a career?). The function is highly flexible because it works for any table or row combination without needing specific code for each case, and it automatically adapts to changes, additions, and deletions in the schema and table rows.
Hopefully you found this helpful. If you did, please click the like button.
