System and method for constraint checking bulk data in a database

• US 5,706,494 A
• Filed: 02/10/1995
• Issued: 01/06/1998
• Est. Priority Date: 02/10/1995
• Status: Expired due to Fees

• Current Assignee: International Business Machines Corporation
• Original Assignee: International Business Machines Corporation
• Inventors: Zuzarte, Calisto Paul, Pirahesh, Mir Hamid, Cochrane, Roberta Jo, Sidle, Richard Sefton
• Primary Examiner(s): Black, Thomas G.
• Assistant Examiner(s): Von Buhr, Maria N.
• Application Number:
  US08/387,037
• Time in Patent Office: 1,061 Days
• Field of Search: 395/600, 395/602, 395/604, 395/612, 395/616, 364/DIG. 1, 364/DIG. 2
• US Class Current: 1/1
• CPC Class Codes: G06F 16/284 Relational databases
• CPC Class Codes: Y10S 707/99932 Access augmentation or optimizing
• CPC Class Codes: Y10S 707/99934 Query formulation, input preparation, or translation
• CPC Class Codes: Y10S 707/99942 Manipulating data structure, e.g. compression, compaction, compilation

Abstract

In a relational database management system (RDMS), a method and system for constraint checking of bulk data after storage in a base table. This invention generates a "dummy" INSERT inquiry to cause the compiler constraint rule engine to generate code for checking for constraint violations. After construction of a Query Graph Model by the compiler, a logic module replaces the INSERT inquiry with a SELECT inquiry to create code that when executed will select records from the bulk data table. Constraint violations are handled in several ways by this invention, including merely reporting that a constraint error exists or listing those records that violate constraint conditions. The logic also enforces referential integrity constraints by applying the SQL CASCADE command to the violating records to expand the violation list to include children records that would be orphaned when their parent records are later removed.

Claims

1. In a relational database management system (RDMS) having a query compiler with a constraint engine and memory including mass storage for storing a database that has at least one table T_i containing a plurality of data records, each data record being uniquely identifiable in the RDMS, wherein the RDMS system further has a data processor for processing queries represented by a query graph, a method for checking for constraint violations in a data record in the table T_i, the method comprising the steps of:

generating an insert query Q_MAIN for inserting one or more records into the table T_i ;

compiling the insert query Q_MAIN to produce compiled insert commands including insert code and checking code to check for constraint violations in one or more records specified by the insert query Q_MAIN ;

modifying the query Q_MAIN by replacing the insert code with compiled select commands to produce modified code having select code and the checking code; and

,

constraint checking each record in the table T_i with the modified code.

2. The method of claim 1, wherein the modified code reports at least one constraint violation in an error reporting data message.

3. The method of claim 2, wherein the modified code identifies and reports all the records in the table T_i that exhibit constraint violations.

4. The method of claim 3, wherein the modified code reports a string for each record in table T_i exhibiting a constraint violation, the string containing concatenated constraint names, each constraint name of the concatenated constraint names corresponding to a constraint violation exhibited by the record.

5. The method of claim 4, wherein the RDMS further includes an exception table stored in memory and further comprising the step of:

constructing an insert command to place the record identifier for each record exhibiting a constraint violation in the exception table E_i and also to place each corresponding string containing concatenated constraint names in the exception table E_i.

6. The method of claim 5, wherein the constructed insert command is adapted to place a timestamp to indicate the time of error in the exception table E_i.

7. The method of claim 5, wherein the constructed insert command is executed, thereby causing the exception table E_i to be populated with the column entries of records exhibiting a constraint violation, and an error reporting message for each record exhibiting a constraint violation.

8. The method of claim 6, wherein the constructed insert command is executed, thereby causing the exception table E_i to be populated with the column entries of records exhibiting a constraint violation, an error reporting message for each record exhibiting a constraint violation, and the timestamp for each record exhibiting a constraint violation.

9. The method of claim 5, and further comprising the steps of fixing the table T_i to eliminate violating records by:

(a) selecting each record in the table T_i that is identified as exhibiting a constraint violation; and

(b) deleting each record selected in step 9(a).

10. The method of claim 9, and further comprising the step of creating a record insert command to enable inserting a copy of any records deleted in step 9(b) in the exception table E_i.

11. The method of claim 8, and further comprising the steps of fixing the table T_i to eliminate violating records by:

(a) selecting each record in the table T_i that is identified as exhibiting a constraint violation; and

(b) deleting each record selected in step 11(a).

12. The method of claim 11, and further comprising the step of executing the record insert command, thereby populating the exception table E_i with a copy of records deleted in step 9(b).

13. The method of claim 12, wherein any deleted parent records from T₁ are used to identify any child record that has been orphaned by the deletion of their parent records in the table T_i and those identified orphaned child records are deleted by issuing a cascade delete command.

14. The method of claim 13, wherein a copy of each deleted orphaned child record is inserted in the exception table E_i.

15. In a relational database management system (RDMS) having a compiler with a constraint engine and memory including mass storage, for storing a database that has at least one table T_i containing a plurality of data records, each data record being uniquely identifiable in the RDMS, wherein the RDMS system further has a data processor for processing queries represented by a query graph, and the RDMS system further includes an exception table E_i stored in memory, a method for checking for constraint violations in a data record in the table T_i, the method comprising the steps of:

generating an insert query Q_MAIN for inserting one or more records into the table T_i ;

compiling the insert query Q_MAIN to produce compiled insert commands including insert code and checking code to check for constraint violations in one or more records specified by the insert query Q_MAIN ;

modifying the query Q_MAIN by replacing the insert code with compiled select commands producing modified code having select code and the checking code thereby enabling constraint checking for each record in the table T_i,

executing the modified code over the table T_i to report at least one constraint violation and to identify all the records in the table T_i that exhibit constraint violations in an error reporting data message; and

constructing an insert command to place the error reporting message and any column entries of records exhibiting a constraint violation in the exception table E_i.

16. The method of claim 15, wherein the constructed insert command is executed, thereby causing the exception table E_i to be populated with the column entries of records exhibiting a constraint violation, and an error reporting message for each record exhibiting a constraint violation.

17. The method of claim 16, and further comprising the steps of fixing the table T_i to eliminate violating records by:

(a) selecting a record in the table T_i that is identified as exhibiting a constraint violation; and

(b) deleting each record selected in step 16(a).

18. A bulk-data constraint checking system in a relational database management system (RDMS) having memory including mass storage for storing a database that has at least one table T_i containing a plurality of data records, each data record being uniquely identifiable in the RDMS, wherein the RDMS system further has a data processor for processing queries represented by a query graph, the system comprising:

insert query generating means coupled to said data processor for generating an insert query Q_MAIN with one or more records to be inserted into the table T_i ;

a constraint compiler with a constraint engine for compiling the insert query Q_MAIN to produce compiled insert commands including insert code and checking code to check for constraint violations in one more records specified by the insert query Q_MAIN ;

query modifying means coupled with the data processor for modifying the query Q_MAIN by replacing the insert code with compiled select commands to produce modified code having select code and the checking code; and

,

select and constraint checking command execution means coupled with the data processor for executing the modified code over the table T_i to report at least one constraint violation in an error reporting data message.

19. The bulk-data constraint checking system of claim 18, wherein the modified code identifies and reports all the records in the table T_i that exhibit constraint violations.

20. The bulk-data constraint checking system of claim 19, wherein the modified code reports a string for each record in the table T_i exhibiting a constraint violation, the string containing concatenated constraint names, each constraint name of the concatenated constraint names corresponding to a constraint violation exhibited by the record.

21. The bulk-data constraint checking system of claim 20, wherein the RDMS further includes an exception table E_i stored in memory, and the checking system further comprises:

command generating means coupled to the data processor for constructing an insert command to place the record identifier for each record exhibiting a constraint violation in the exception table E_i, and also to place each corresponding string containing concatenated constraint names in the exception table E_i.

22. The bulk-data constraint checking system of claim 21, and further comprising means for fixing the table T_i to eliminate violating records including:

(a) selection means coupled to the data processor for selecting each record in the table T_i as exhibiting a constraint violation; and

(b) record deletion means coupled to the data processor for deleting any record selected by the selection means.

23. The bulk-data constraint checking system of claim 22, and further comprising means for creating a record insert command to enable inserting in the exception table E_i a copy of any records deleted by the record deletion means.

24. A database processing system comprising:

memory including a data store for storing at least one table T_i containing a plurality of data records, each data record being uniquely identifiable in the system;

a data processor coupled to said data store for processing queries represented by query graphs;

insert query generating means coupled to said data processor for generating an insert query Q_MAIN with one or more records to be inserted into the table T_i ;

a constraint compiler with a constraint engine for compiling the insert query Q_MAIN to produce compiled insert commands including insert code and checking code generated by the compiler'"'"'s constraint engine to check for constraint violations in the records of the insert query Q_MAIN ;

query modifying means coupled with the data processor for modifying the query Q_MAIN by replacing the insert code with compiled select commands to produce modified code having select code and the checking code generated by the compiler'"'"'s constraint engine; and

,

select and constraint checking command execution means coupled with the data processor for executing the modified code over the table T_i to report at least one constraint violation in an error reporting data message.

25. The database processing system of claim 24, wherein the modified code identifies and reports all the records in the table T_i that exhibit constraint violations.

26. The database processing system of claim 25, wherein the modified code reports a string for each record exhibiting a constraint violation, the string containing concatenated constraint names, each constraint name of the concatenated constraint names corresponding to a constraint violation exhibited by the record.

27. The database processing system of claim 26, further including:

an exception table E_i stored in memory; and

command generating means coupled to the data processor for constructing an insert command to place the record identifier for each record exhibiting a constraint violation in the exception table E_i, and also to place each corresponding string containing concatenated constraint names in the exception table E_i.

28. The database processing system of claim 27, and further comprising fix-up means for fixing the table T_i to eliminate violating records including:

(a) selection means coupled to the data processor for selecting each record in the table T_i identified as exhibiting a constraint violation; and

(b) record deletion means coupled to the data processor for deleting any record selected by the selection means.

29. The database processing system of claim 28, and further comprising means for creating a record insert command to enable inserting a copy of any records deleted from the exception table E_i.

30. A computer program product, for use with a relational database processing system having a compiler with a constraint engine and memory including mass storage, and also having a database stored in the mass storage that has at least one table T_i containing a plurality of data records, each data record being uniquely identifiable in the system, wherein the relational database processing system further has a data processor for processing queries represented by a query graph, a method for checking for constraint violations in a data record of the plurality of data records in the table T_i by using the compiler constraint engine, the computer program product comprising:

a recording medium;

means, recorded on the recording medium, for directing the data processor to generate an insert query Q_MAIN with one or more records to be inserted into the table T_i ;

means, recorded on the recording medium, for directing the compiler to compile the insert query Q_MAIN to produce compiled insert commands including insert code and checking code to check for constraint violations in one or more records specified by the insert query Q_MAIN ;

means, recorded on the recording medium, for directing the data processor to modify the query Q_MAIN by replacing the insert code with compiled select commands to produce modified code having checking code generated by the compiler'"'"'s constraint engine; and

,

means, recorded on the recording medium, for directing the data processor to execute the modified code over the table T_i to report at least one constraint violation in an error reporting data message.

31. The computer program product of claim 30, wherein the modified code identifies and reports all the records in the table T_i that exhibit constraint violations.

32. The computer program product of claim 30, wherein the modified code reports a string for each record exhibiting a constraint violation, the string containing concatenated constraint names, each constraint name of the concatenated constraint names corresponding to a constraint violation exhibited by the record.

33. The computer program product of claim 31, wherein the system further includes an exception table E_i in memory, and the computer program product further comprises:

insert command generating means recorded on the recording medium for directing the data processor to construct an insert command to place the record identifier for each record exhibiting a constraint violation in the exception table E_i, and also to place each corresponding string containing concatenated constraint names in the exception table E_i.

34. The computer program product of claim 33, and further comprising means, recorded on the recording medium for fixing the table T_i to eliminate violating records including:

(a) selection means for directing the data processor to select each record in the table T_i identified as exhibiting a constraint violation; and

(b) record deletion means for directing the data processor to delete any record selected by the selection means.

35. The computer program product of claim 33, and further comprising means, recorded on the recording medium for deleting records from the exception table, and for directing the data processor to create a record insert command to enable inserting a copy of any records deleted from the exception table E_i.