Constraints With Hidden Rows in a Database

• US 20090292711A1
• Filed: 05/20/2008
• Published: 11/26/2009
• Est. Priority Date: 05/20/2008
• Status: Active Grant

• Current Assignee: International Business Machines Corporation
• Original Assignee: International Business Machines Corporation
• Inventors: Konik, Rafal P., Venz, Michael A., Theuer, Mark W.
• Granted Patent: US 8,108,367 B2
• Time in Patent Office: Days
• US Class Current: 1/1
• CPC Class Codes: G06F 16/24565 Triggers; Constraints

Abstract

In an embodiment, a constraint is created for a database table. The constraint specifies a condition for a first column in the database table and an action. The action specifies whether data that violates the condition is allowed to be stored in the first column. A value and a specification of a second column in the database table are received from a data source. If the second column is identical to the first column, the value violates the condition, and the action specifies that data that violates the condition is allowed to be stored, the value is stored in a row in the database, the row is marked as hidden, and an identification of the constraint that was violated is stored in the row. A query does not return the row that is marked as hidden.

Claims

1. A method comprising:

creating a constraint for a database table, wherein the constraint specifies a condition for a first column in the database table and an action, wherein the action specifies whether data that violates the condition is allowed to be stored in the first column;

receiving a value and a specification of a second column in the database table from a data source; and

if the second column is identical to the first column, the value violates the condition, and the action specifies that data that violates the condition is allowed to be stored, storing the value in a row in the database and marking the row as hidden.

2. The method of claim 1, further comprising:

if the second column is identical to the first column, the value violates the condition, and the action specifies that data that violates the condition is not allowed to be stored, refraining from storing the value in the row in the database and returning an error to the data source.

3. The method of claim 2, further comprising:

if the second column is identical to the first column and the value satisfies the condition, storing the value in the second column of the row in the database and marking the row as readable.

4. The method of claim 3, wherein a query returns the row if the row is marked as readable, and wherein a query does not return the row if the row is marked as hidden.

5. The method of claim 1, further comprising:

if the second column is identical to the first column, the value violates the condition, and the action specifies that data that violates the condition is allowed to be stored, storing an identification of the constraint that was violated in the row.

6. The method of claim 1, further comprising:

recommending a change to the constraint that causes the value in the row to satisfy the constraint.

7. The method of claim 3, further comprising:

altering the condition into a new condition; and

if the row is marked as hidden, marking the row as readable if the value satisfies the new condition.

8. The method of claim 1, further comprising:

if the second column is not identical to the first column, storing the value in the second column of the row in the database and marking the row as readable.

9. A storage medium encoded with instructions, wherein the instructions when executed comprise:

creating a constraint for a database table, wherein the constraint specifies a condition for a first column in the database table and an action, wherein the action specifies whether data that violates the condition is allowed to be stored in the first column;

receiving a value and a specification of a second column in the database table from a data source; and

if the second column is identical to the first column, the value violates the condition, and the action specifies that data that violates the condition is allowed to be stored, storing the value in a row in the database, marking the row as hidden, and storing an identification of the constraint that was violated in the row, wherein a query does not return the row that is marked as hidden.

10. The storage medium of claim 9, further comprising:

if the second column is identical to the first column and the value violates the condition, refraining from storing the value in the row in the database and returning an error to the data source if the action specifies that data that violates the condition is not allowed to be stored; and

if the second column is identical to the first column and the value satisfies the condition, storing the value in the second column of the row in the database and marking the row as readable, wherein the query returns the row that is marked as readable.

11. The storage medium of claim 9, further comprising:

recommending a change to the constraint that causes the value in the row to satisfy the constraint.

12. The storage medium of claim 9, wherein the recommending the change to the constraint that causes the value in the row to satisfy the constraint further comprises:

searching a plurality of rows that comprise a plurality of values that violate the constraint, wherein the searching finds an extreme value in the plurality of values, wherein the extreme value has a greatest difference from an operand specified by the condition; and

recommending that the operand of the condition be changed to the extreme value.

13. The storage medium of claim 10, further comprising:

altering the condition into a new condition; and

if the row is marked as hidden, marking the row as readable if the value satisfies the new condition.

14. The storage medium of claim 9, further comprising:

deleting all of a plurality of rows in the database table that comprise a plurality of values that violate the constraint.

15. A computer system comprising:

a processor; and

memory communicatively connected to the processor, wherein the memory encodes instructions that when executed by the processor comprise;

creating a constraint for a database table, wherein the constraint specifies a condition for a first column in the database table and an action, wherein the action specifies whether data that violates the condition is allowed to be stored in the first column,

receiving a value and a specification of a second column in the database table from a data source, and

if the second column is identical to the first column, the value violates the condition, and the action specifies that data that violates the condition is allowed to be stored, storing the value in a row in the database, marking the row as hidden, and storing an identification of the constraint that was violated in the row, wherein a query does not return the row that is marked as hidden.

16. The computer system of claim 15, wherein the instructions further comprise:

recommending a change to the constraint that causes the value in the row to satisfy the constraint, wherein the recommending the change to the constraint that causes the value in the row to satisfy the constraint further comprises searching a plurality of rows that comprise a plurality of values that violate the constraint, wherein the searching finds an extreme value in the plurality of values, wherein the extreme value has a greatest difference from an operand specified by the condition, and recommending that the operand of the condition be changed to the extreme value.

17. The computer system of claim 15, wherein the instructions further comprise:

altering the condition into a new condition; and

if the row is marked as hidden, marking the row as readable if the value satisfies the new condition.

18. The computer system of claim 15, wherein the instructions further comprise:

deleting all of a plurality of rows in the database table that comprise a plurality of values that violate the constraint.

19. The computer system of claim 15, wherein the instructions further comprise:

in response to the receiving, determining whether the second column is identical to the first column and the value satisfies the condition, wherein the determining whether the value satisfies the condition further comprises determining whether the value exists in a parent column of a parent table, wherein the first column comprises a foreign key, the condition specifies a parent table, and a parent column in the parent table matches the foreign key.

20. The computer system of claim 15, wherein the instructions further comprise:

in response to the receiving, determining whether the second column is identical to the first column and the value satisfies the condition, wherein the determining whether the value satisfies the condition further comprises substituting the value for a variable in the condition and evaluating truth of the condition.