Method and apparatus for reducing overhead of validating constraints in a database

• US 20070208787A1
• Filed: 03/01/2006
• Published: 09/06/2007
• Est. Priority Date: 03/01/2006
• Status: Active Grant

• Current Assignee: International Business Machines Corporation
• Original Assignee: International Business Machines Corporation
• Inventors: Cheng, Qi, Zuzarte, Calisto, Rizvi, Haider
• Granted Patent: US 7,788,241 B2
• Time in Patent Office: Days
• US Class Current: 1/1
• CPC Class Codes: G06F 16/21 Design, administration or maintenance of databases

Abstract

Methods and systems for reducing the overhead of validating constraints in a database are provided. The method includes providing a target table, and providing a source table. The source table includes data to be loaded into the target table. The method further includes loading data into the target table from the source table including eliminating any logic to validate a constraint defined on the target table based on one or more pre-determined conditions.

Claims

1. A method for loading data from a source table to a target table comprising:

providing a target table;

providing a source table, the source table including data to be loaded into the target table; and

loading data into the target table from the source table including eliminating any logic to validate a constraint defined on the target table based on one or more pre-determined conditions.

2. The method of claim 1, wherein the constraint is one of a check constraint, a referential integrity constraint, a generated column, or an enforced functional dependency.

3. The method of claim 1, wherein the one or more pre-determined conditions includes verification that the constraint defined on the target table includes a similar definition to a constraint defined on the source table.

4. The method of claim 1, wherein eliminating any logic to validate a constraint defined on the target table based on one or more pre-determined conditions comprises:

verifying that there is a constraint defined on the source table that has a similar definition to the constraint defined on the target table;

verifying that data from a column of the source table involved in the constraint is to be inserted into a corresponding column of the target table that is involved in the similar constraint definition on the target table; and

verifying that the data in the column of the source table is not modified during loading of the data.

5. The method of claim 1, wherein eliminating any logic to validate a constraint defined on the target table based on one or more pre-determined conditions comprises:

verifying that a referential integrity constraint defined between two target tables is similar to a referential integrity constraint defined between two source tables;

verifying that data from one or more columns of the source tables is to be inserted into the corresponding columns of the target tables and a parent-child relationship of the referential integrity constraint is similar between the source tables and the target tables; and

verifying that the data in the columns from one or more columns of the source tables involved in the constraint are not modified during loading of the data.

6. The method of claim 5, wherein eliminating any logic to validate a constraint defined on the target table based on one or more pre-determined conditions further comprises verifying that corresponding parent and child table rows which satisfy the referential constraint between the two source tables are loaded together in an appropriate order with no relevant data modifying operations defined on the two target tables during loading of the data.

7. The method of claim 1, wherein eliminating any logic to validate a constraint defined on the target table includes removing pre-existing logic to validate the constraint.

8. A computer program product tangibly stored on a computer-readable medium for loading data from a source table to a target table, the product comprising program instructions operable to cause a programmable processor to:

provide a target table;

provide a source table, the source table including data to be loaded into the target table; and

load data into the target table from the source table including instructions to eliminate any logic to validate a constraint defined on the target table based on one or more pre-determined conditions.

9. The computer program product of claim 8, wherein the constraint is one of a check constraint, a referential integrity constraint, a generated column, or an enforced functional dependency.

10. The computer program product of claim 8, wherein the one or more pre-determined conditions includes verification that the constraint defined on the target table includes a similar definition to a constraint defined on the source table.

11. The computer program product of claim 8, wherein the instructions to eliminate any logic to validate a constraint defined on the target table based on one or more pre-determined conditions include instructions to:

verify that there is a constraint defined on the source table that has a similar definition to the constraint defined on the target table;

verify that data from a column of the source table involved in the constraint is to be inserted into a corresponding column of the target table that is involved in the similar constraint definition on the target table; and

verify that the data in the column of the source table is not modified during loading of the data.

12. The computer program product of claim 8, wherein the instructions to eliminate any logic to validate a constraint defined on the target table based on one or more pre-determined conditions include instructions to:

verify that a referential integrity constraint defined between two target tables is similar to a referential integrity constraint defined between two source tables;

verify that data from one or more columns of the source tables is to be inserted into the corresponding columns of the target tables and a parent-child relationship of the referential integrity constraint is similar between the source tables and the target tables; and

verify that the data in the columns from one or more columns of the source tables involved in the constraint are not modified during loading of the data.

13. The computer program product of claim 12, wherein the instructions to eliminate any logic to validate a constraint defined on the target table based on one or more pre-determined conditions further include instructions to verify that corresponding parent and child table rows which satisfy the referential constraint between the two source tables are loaded together in an appropriate order with no relevant data modifying operations defined on the two target tables during loading of the data.

14. The product of claim 8, wherein the instructions to eliminate any logic to validate a constraint defined on the target table based include instructions to remove pre-existing logic to validate the constraint.

15. A database management system comprising:

a target table;

a source table including data to be loaded into the target table; and

constraint validation logic operable to eliminate any logic to validate a constraint defined on the target table based on one or more pre-determined conditions during loading of the data from the source table into the target table.

16. The database management system of claim 15, wherein the constraint is one of a check constraint, a referential integrity constraint, a generated column, or an enforced functional dependency.

17. The database management system of claim 15, wherein the one or more pre-determined conditions includes verification that the constraint defined on the target table includes a similar definition to a constraint defined on the source table.

18. The database management system of claim 15, wherein the constraint validation logic is operable to eliminate any logic to validate a constraint defined on the target table based on one or more pre-determined conditions by verifying that there is a constraint defined on the source table that has a similar definition to the constraint defined on the target table, verifying that data from a column of the source table involved in the constraint is to be inserted into a corresponding column of the target table that is involved in the similar constraint definition on the target table, and verifying that the data in the column of the source table is not modified during loading of the data.

19. The database management system of claim 15, wherein the constraint validation logic is operable to eliminate any logic to validate a constraint defined on the target table by removing pre-existing logic to validate the constraint.

20. The database management system of claim 15, wherein the source table and the target table are associated with a relational database.