Constraint processing
View Patent ↗Constraint processing for a relational database generates primary (e.g., based on primary key values) and constraint index records (e.g., based on foreign key values) during table load operations that are then sorted in a manner that rapidly and unambiguously identifies rows that fail the specified constraint test. Rows so identified may be deleted to maintain the constraint (e.g., referential) integrity of a child table. In one case, child table row data may be processed in constraint key order, eliminating the need first load the child table with row data and then delete those rows that subsequently fail the integrity test.
1. A method to process a constraint in a database having a table that is both a parent and a child with respect to a referential constraint relationship, comprising:
generating a primary index record for each loaded row of data associated with a parent table, each of said primary index records including a table identifier, a primary key associated with the row of data, and a first flag value;
generating a referential index record for each loaded row of data associated with a child table, each of said referential index records including a parent table identifier, a foreign key associated with the row of data, and a second flag value, the second flag value having a value different than the first flag value;
sorting the primary index records and the referential index records based on the table identifier, the primary key, and the first flag value of the primary index records, and the parent table identifier, the foreign key, and the second flag value of the referential index records, wherein, by selecting the first flag value to be different than the second flag value, referential index records associated with the child table having the same foreign key are grouped together and one primary index record whose primary key corresponds to the foreign key of the referential index records immediately precedes the group of referential index records; and
deleting those rows associated with each referential index record whose foreign key does not match the primary key of an immediately prior primary index record.
2. The method of claim 1 , wherein the acts of generating a primary index record, generating a referential index record and sorting are performed during a table load operation.
3. The method of claim 2 , wherein the table load operation comprises a load resume operation.
4. The method of claim 2 , wherein the table load operation comprises a load replace operation.
5. The method of claim 1 , wherein the first flag value is zero.
6. The method of claim 5 , wherein the second flag value is greater than zero.
7. The method of claim 1 , wherein the act of sorting further comprises:
sorting the primary index records based on a combination of the primary keys and the first flag values; and
sorting the referential index records based on a combination of the foreign keys and the second flag values, wherein
the acts of sorting jointly sorts the primary index records and the referential index records into a sorted list.
8. The method of claim 7 , wherein the act of deleting comprises:
identifying a primary index record in the sorted list;
identifying a next index record in the sorted list;
marking the table row associated with the next index record for deletion if the next index record is a referential index record and the foreign key of the next index record does not match the primary key of the identified primary index record; and
repeating the acts of identifying a next index record and marking until the next index record comprises another primary index record.
9. The method of claim 8 , further comprising repeating the acts of:
identifying a next index record;
marking the table row associated with the next index record; and
repeating until all entries in the sorted list have been processed.
10. The method of claim 1 , wherein the first flag value of each primary index record is less than the second flag value of each referential index record.
11. A non-transitory computer-readable medium having executable instructions to cause at least one processor to:
generate a primary index record for each loaded row of data associated with a parent table, each of said primary index records including a table identifier, a primary key associated with the row of data, and a first flag value;
generate a referential index record for each loaded row of data associated with a child table, each of said referential index records including a parent table identifier, a foreign key associated with the row of data, and a second flag value, the second flag value having a value different than the first flag value;
sort the primary index records and the referential index records based on the table identifier, the primary key, and the first flag value of the primary index records, and the parent table identifier, the foreign key, and the second flag value of the referential index records, wherein, by selecting the first flag value to be different than the second flag value, referential index records associated with the child table having the same foreign key are grouped together and one primary index record whose primary key corresponds to the foreign key of the referential index record precedes the group of referential index records; and
delete those rows associated with each referential index record whose foreign key does not match the primary key of an immediately prior primary index record.
12. The non-transitory computer-readable medium of claim 11 , wherein the primary index records and the referential index records are generated and sorted during a table load operation.
13. The non-transitory computer-readable medium of claim 12 , wherein the table load operation comprises a load resume operation.
14. The non-transitory computer-readable medium of claim 12 , wherein the table load operation comprises a load replace operation.
15. The non-transitory computer-readable medium of claim 11 , wherein the executable instructions to sort include executable instructions to cause the at least one processor to:
sort the primary index records based on a combination of the primary keys and the first flag values; and
sort the referential index records based on a combination of the foreign keys and the second flag values,
wherein the primary index records and the referential index records are jointed sorted into a sorted list.
16. The non-transitory computer-readable medium of claim 15 , wherein the executable instructions to delete include executable instructions to cause the at least one processor to:
identify a primary index record in the sorted list;
identify a next index record in the sorted list;
mark the table row associated with the next index record for deletion if the next index record is a referential index record and the foreign key of the next index record does not match the primary key of the identified primary index record; and
repeat identifying a next index record and marking the table row until the next index record comprises another primary index record.
17. The non-transitory computer-readable medium of claim 16 , further comprising:
identify a next index record;
mark the table row associated with the next index record; and
repeat until all entries in the sorted list have been processed.
18. The non-transitory computer-readable medium of claim 11 , wherein the first flag value of each primary index record is less than the second flag value of each referential index record.