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, primary key errors detected during loading the tables into memory;
identifying the loaded rows that violate a referential constraint due to a primary key error using both the error file and the foreign key index for each loaded child table, wherein identifying the loaded rows that violate the referential constraint includes:
obtaining from the error file a primary key record,
determining a primary key value associated with the primary key error record, and
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 to identify the loaded rows that violate the referential constraint;
creating a foreign key error file that includes the identified rows;
marking each identified row in the foreign key error file for deletion; and
deleting the marked rows.
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 logging the 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 writing the 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 creating the foreign key error file comprises:
writing a foreign key error record for each identified row.
8. The method of claim 7 , wherein writing the foreign key error record comprises writing an error record that includes a table identifier and a row identifier for the identified row.
9. The method of claim 8 , further comprising writing an identifier identifying the error record as a foreign key error record.
10. The method of claim 1 , further comprising sorting the error file based on a table identifier and row identifier associated with each error in the error file.
11. The method of claim 10 , further comprising sorting the error file based on an identifier associated with each row identified in a foreign key error record, the sorting being performed prior to the act of deleting the marked rows.
12. The method of claim 10 , further comprising making the one or more tables available for general access to a database management system.
13. The method of claim 1 , wherein obtaining the foreign key index for each loaded child table comprises building a foreign key index for at least one of the loaded child tables.
14. The method of claim 1 , wherein obtaining the 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. 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, primary key errors detected during loading the tables into memory;
identify the loaded rows that violate a referential constraint due to a primary key error using both the error file and the foreign key index for each loaded child table, wherein the instructions to identify the loaded rows that violate the referential constraint include instructions to:
obtain from the error file 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 to identify the loaded rows that violate the referential constraint;
create a foreign key error file that includes the identified rows;
mark each identified row in the foreign key error file for deletion; and
delete the marked rows.
16. The program storage device of claim 15 , 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.
17. The program storage device of claim 16 , further comprising instructions to write a primary key error record for each duplicate primary key value detected.
18. The program storage device of claim 16 , 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.
19. The program storage device of claim 18 , further comprising instructions to write an identifier identifying the error record as a primary key error record.
20. The program storage device of claim 15 , wherein the instructions to create the foreign key error file comprise instructions to:
write a foreign key error record for each identified row.
21. The program storage device of claim 20 , 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.
22. The program storage device of claim 21 , further comprising instructions to write an identifier identifying the error record as a foreign key error record.
23. The program storage device of claim 15 , further comprising instructions to sort the error file based on the error records' table identifier and row identifier.
24. The program storage device of claim 23 , 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.
25. The program storage device of claim 23 , further comprising instructions to make the one or more tables available for general access to a database management system.
26. The program storage device of claim 15 , 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.
27. The program storage device of claim 15 , 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.