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 parent table and a child table wherein the parent table and the child table are related through the constraint, comprising:
generating a first index record for each loaded row of data associated with the parent table, each of said first index records including a first table identifier value identifying the parent table, a first key value associated with the row of data and a first flag value;
generating a second index record for each loaded row of data associated with the child table, each of said second index records including a second table identifier value identifying the parent table, a second key value associated with the row of data and a second flag value;
sorting the first index records and the second index records so that all second index records having the same second table identifier value and second key value are grouped together and, collectively, immediately adjacent to zero or one first index record whose first table identifier value corresponds to the second table identifier of the second index records and further wherein the first key value of the first index record corresponds to the second key value of the second index records; and
deleting those child table rows associated with each second index record whose second table identifier value and second key value do not match the first table identifier and first key value of an immediately prior first index record.
2. The method of claim 1 , wherein the constraint comprises a referential constraint.
3. The method of claim 1 , wherein the parent table and the child table comprise the same table.
4. The method of claim 1 , wherein the acts of generating a first index record, generating a second index record and sorting are performed during a child table load operation.
5. The method of claim 4 , wherein the child table load operation comprises a load resume operation.
6. The method of claim 4 , wherein the child table load operation comprises a load replace operation.
7. The method of claim 1 , wherein the first flag value is zero.
8. The method of claim 7 , wherein the second flag value is greater than zero.
9. The method of claim 1 , wherein the act of sorting further comprises:
sorting the first index records based on a combination of the first table identifier values, the first key values and the first flag values; and
sorting the second index records based on a combination of the second table identifier values, the second key values and the second flag values, wherein
the acts of sorting jointly sorts the first index records and the second index records into a sorted list.
10. The method of claim 9 , wherein the act of deleting comprises:
identifying a first 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 second index record and the second table identifier value and the second key value of the next index record do not match the first table identifier value and the first key value of the identified first index record; and
repeating the acts of identifying a next index record and marking until the next index record comprises another first index record.
11. The method of claim 10 , 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.
12. A program storage device readable by a machine comprising instructions for causing a programmable control device to perform the method of claim 1 .
13. A program storage device readable by a machine comprising instructions for causing a programmable control device to perform any one of claims 2 - 11 .