Cascade delete processing
View Patent ↗A time-efficient means for identifying and processing cascading deletes due to referential constraint violations includes: logging, to an error file, all primary key (“PK”) errors detected during table load operations; building a foreign key (“FK”) index for each child table; recursively probing each relevant FK index to identify all loaded rows that violate a referential constraint due to a PK error; logging all identified FK errors to the error file; and using the (preferably sorted) error file contents to identify, mark and physically delete table rows that violate a referential constraint. The described cascade delete processing methods make only a single pass through the table data, using ordinary computer files to track and organize rows identified for deletion. Use of error files rather than tablescans (multiple passes through the loaded table data) can provide a significant reduction in table load times, especially for large or intricately “related” tables.
1. A method to process cascading deletes in a relational database, comprising:
loading one or more tables into memory, wherein each table comprises one or more rows and at least one of said tables is a parent table and at least one of said tables is a child table;
obtaining a foreign key index for each loaded child table;
logging, to an error file, all primary key errors detected during the act of loading one or more tables into memory;
probing each relevant foreign key index to identify all loaded rows that violate a referential constraint due to a primary key error, such referential constraint violation being a foreign key error, wherein the act of probing is performed without accessing the child table associated with the foreign key index being probed and wherein the act of probing further comprises—
obtaining a primary key error record,
determining a primary key value associated with the primary key error record,
using the primary key value to probe each foreign key index associated with a table that is dependent to the table associated with the primary key value, and
identifying those rows identified by each foreign key index entry obtained as a result of said probing;
logging all identified foreign key errors to the error file; and
using the error file contents to identify, mark and delete loaded table rows that violate a referential constraint.
2. The method of claim 1 , wherein the at least one parent table and the at least one child table comprise the same table.
3. The method of claim 1 , wherein the act of logging primary key errors comprises writing a primary key error record for each row failing a data validation check operation.
4. The method of claim 3 , further comprising writing a primary key error record for each duplicate primary key value detected.
5. The method of claim 3 , wherein the act of writing a primary key error record comprises writing an error record that includes a table identifier and a row identifier for the row.
6. The method of claim 5 , further comprising writing an identifier identifying the error record as a primary key error record.
7. The method of claim 1 , wherein the acts of obtaining, determining, using and identifying are repeated for each row identified as invalid due to a referential constraint violation.
8. The method of claim 1 , wherein the act of logging foreign key errors comprises writing a foreign key error record for each row identified during the act of identifying.
9. The method of claim 8 , wherein the act of writing a foreign key error record comprises writing an error record that includes a table identifier and a row identifier for the row identified as having violated a referential constraint.
10. The method of claim 9 , further comprising writing an identifier identifying the error record as a foreign key error record.
11. The method of claim 1 , wherein the act of using the error file contents comprises:
sorting the error file based on a table identifier and row identifier associated with each error in the error file;
marking each row identified in a foreign key error record for deletion; and
deleting the marked rows.
12. The method of claim 11 , further comprising sorting the error file based on an identifier associated with each row identified in a foreign key error record, said act of sorting being performed prior to the act of deleting the marked rows.
13. The method of claim 11 , further comprising making the one or more tables available for general access to a database management system.
14. The method of claim 1 , wherein the act of obtaining a foreign key index for each loaded child table comprises obtaining use of a prior built foreign key index for at least one of the loaded child tables.
15. The method of claim 1 , wherein the act of obtaining a foreign key index for each loaded child table comprises building a foreign key index for at least one of the loaded child tables.
16. A program storage device, readable by a programmable control device, comprising instructions stored thereon for causing the programmable control device to:
load one or more database tables into memory, wherein each table comprises one or more rows and at least one of said tables is a parent table and at least one of said tables is a child table;
obtain a foreign key index for each loaded child table;
log, to an error file, all primary key errors detected during the act of loading one or more tables into memory;
probe each relevant foreign key index to identify all loaded rows that violate a referential constraint due to a primary key error, such referential constraint violation being a foreign key error, wherein the instructions to probe are performed without accessing the child table associated with the foreign key index being probed and wherein the instructions to probe each relevant foreign key index further comprise instructions to—
obtain a primary key error record,
determine a primary key value associated with the primary key error record,
use the primary key value to probe each foreign key index associated with a table that is dependent to the table associated with the primary key value, and
identify those rows identified by each foreign key index entry obtained as a result of said probing;
log all identified foreign key errors to the error file; and
use the error file contents to identify, mark and delete loaded table rows that violate a referential constraint.
17. The program storage device of claim 16 , wherein the instructions to log primary key errors comprise instructions to write a primary key error record for each row failing a data validation check operation.
18. The program storage device of claim 17 , further comprising instructions to write a primary key error record for each duplicate primary key value detected.
19. The program storage device of claim 17 , wherein the instructions to write a primary key error record comprise instructions to write an error record that includes a table identifier and a row identifier.
20. The program storage device of claim 19 , further comprising instructions to write an identifier identifying the error record as a primary key error record.
21. The program storage device of claim 16 , wherein the instructions to obtain, determine, use and identify are repeated for each row identified as invalid due to a referential constraint violation.
22. The program storage device of claim 16 , wherein the instructions to log foreign key errors comprise instructions to write a foreign key error record for each row identified during execution of the instructions to identify.
23. The program storage device of claim 22 , wherein the instructions to write a foreign key error record comprise instructions to write an error record that includes a table identifier and a row identifier.
24. The program storage device of claim 23 , further comprising instructions to write an identifier identifying the error record as a foreign key error record.
25. The program storage device of claim 16 , wherein the instructions to use the error file contents comprise instructions to:
sort the error file based on the error records' table identifier and row identifier;
mark each row identified in a foreign key error record for deletion; and
delete the marked rows.
26. The program storage device of claim 25 , further comprising instructions to sort the error file based on an identifier associated with each row identified in a foreign key error record, said instructions to sort being performed prior to the instructions to delete the marked rows.
27. The program storage device of claim 25 , further comprising instructions to make the one or more tables available for general access to a database management system.
28. The program storage device of claim 16 , wherein the instructions to obtain a foreign key index for each loaded child table comprise instructions to use of a prior built foreign key index for at least one of the loaded child tables.
29. The program storage device of claim 16 , wherein the instructions to obtain a foreign key index for each loaded child table comprise instructions to use of a prior built foreign key index for at least one of the loaded child tables.