Configuring check constraint and row violation logging using error tables
Provided herein are systems and methods for configuring integrity constraints (including a check constraint) and row violation logging using error tables. An example method includes decoding a query received at a network-based database system. The query includes a command to perform an operation on a base table. An integrity constraint associated with the base table is retrieved. The integrity constraint specifies a desired configuration for the base table. A verification of the integrity constraint is performed to detect erroneous data of the base table that violates the desired configuration. The erroneous data is input into an error table that is configured as a nested object of the base table. A notification that the erroneous data is available in the error table is generated and output.
1. A system comprising:
at least one hardware processor; and
at least one memory storing instructions that cause the at least one hardware processor to perform operations comprising:
decoding a query received at a network-based database system, the query including a command to perform an operation on a base table;
retrieving an integrity constraint associated with the base table, the integrity constraint specifying a desired configuration for the base table;
performing a verification of the integrity constraint to detect erroneous data of the base table, the erroneous data violating the desired configuration;
inputting the erroneous data into an error table, the error table being configured as a dynamic table maintained as a nested object of the base table; and
generating a notification that the erroneous data is available in the error table.
2. The system of claim 1 , wherein the integrity constraint comprises a check constraint and the desired configuration comprises at least one condition to be met by each row of a plurality of rows stored within the base table.
3. The system of claim 2 , the operations for performing the verification further comprising:
parsing each row of the plurality of rows to determine whether the row satisfies the at least one condition; and
designating the row as the erroneous data when the row does not satisfy the at least one condition.
4. The system of claim 1 , the operations further comprising:
decoding a configuration message received from an account of a user of the network-based database system, the configuration message including the desired configuration of the integrity constraint.
5. The system of claim 4 , the operations further comprising:
determining a Boolean expression based on the configuration message.
6. The system of claim 5 , the operations further comprising:
configuring the integrity constraint as a check constraint, the check constraint using the Boolean expression.
7. The system of claim 4 , the operations further comprising:
determining one or more remediation actions based on the configuration message, wherein a first remediation action of the one or more remediation actions comprises the inputting of the erroneous data into the error table.
8. The system of claim 7 , the operations further comprising:
determining a second remediation action of the one or more remediation actions comprises canceling the operation on the base table.
9. The system of claim 1 , the operations further comprising:
removing the erroneous data from the base table; and
executing the operation on remaining data in the base table.
10. The system of claim 1 , the operations further comprising:
updating the error table to include an identification of the integrity constraint and an expression of the desired configuration associated with the integrity constraint.
11. A method comprising:
decoding, by at least one hardware processor, a query received at a network-based database system, the query including a command to perform an operation on a base table;
retrieving an integrity constraint associated with the base table, the integrity constraint specifying a desired configuration for the base table;
performing a verification of the integrity constraint to detect erroneous data of the base table, the erroneous data violating the desired configuration;
inputting the erroneous data into an error table, the error table being configured as a dynamic table maintained as a nested object of the base table; and
generating a notification that the erroneous data is available in the error table.
12. The method of claim 11 , wherein the integrity constraint comprises a check constraint and the desired configuration comprises at least one condition to be met by each row of a plurality of rows stored within the base table.
13. The method of claim 12 , wherein the performing of the verification further comprises:
parsing each row of the plurality of rows to determine whether the row satisfies the at least one condition; and
designating the row as the erroneous data when the row does not satisfy the at least one condition.
14. The method of claim 11 , further comprising:
decoding a configuration message received from an account of a user of the network-based database system, the configuration message including the desired configuration of the integrity constraint.
15. The method of claim 14 , further comprising:
determining a Boolean expression based on the configuration message.
16. The method of claim 15 , further comprising:
configuring the integrity constraint as a check constraint, the check constraint using the Boolean expression.
17. The method of claim 14 , further comprising:
determining one or more remediation actions based on the configuration message, wherein a first remediation action of the one or more remediation actions comprises the inputting of the erroneous data into the error table.
18. The method of claim 17 , further comprising:
determining a second remediation action of the one or more remediation actions comprises canceling the operation on the base table.
19. The method of claim 11 , further comprising:
removing the erroneous data from the base table; and
executing the operation on remaining data in the base table.
20. The method of claim 11 , further comprising:
updating the error table to include an identification of the integrity constraint and an expression of the desired configuration associated with the integrity constraint.
21. A computer-storage medium comprising instructions that, when executed by one or more processors of a machine, configure the machine to perform operations comprising:
decoding a query received at a network-based database system, the query including a command to perform an operation on a base table;
retrieving an integrity constraint associated with the base table, the integrity constraint specifying a desired configuration for the base table;
performing a verification of the integrity constraint to detect erroneous data of the base table, the erroneous data violating the desired configuration;
inputting the erroneous data into an error table, the error table being configured as a dynamic table maintained as a nested object of the base table; and
generating a notification that the erroneous data is available in the error table.
22. The computer-storage medium of claim 21 , wherein the integrity constraint comprises a check constraint and the desired configuration comprises at least one condition to be met by each row of a plurality of rows stored within the base table.
23. The computer-storage medium of claim 22 , the operations for performing the verification further comprising:
parsing each row of the plurality of rows to determine whether the row satisfies the at least one condition; and
designating the row as the erroneous data when the row does not satisfy the at least one condition.
24. The computer-storage medium of claim 21 , the operations further comprising:
decoding a configuration message received from an account of a user of the network-based database system, the configuration message including the desired configuration of the integrity constraint.
25. The computer-storage medium of claim 24 , the operations further comprising:
determining a Boolean expression based on the configuration message.
26. The computer-storage medium of claim 25 , the operations further comprising:
configuring the integrity constraint as a check constraint, the check constraint using the Boolean expression.
27. The computer-storage medium of claim 24 , the operations further comprising:
determining one or more remediation actions based on the configuration message, wherein a first remediation action of the one or more remediation actions comprises the inputting of the erroneous data into the error table.
28. The computer-storage medium of claim 27 , the operations further comprising:
determining a second remediation action of the one or more remediation actions comprises canceling the operation on the base table.
29. The computer-storage medium of claim 21 , the operations further comprising:
removing the erroneous data from the base table; and
executing the operation on remaining data in the base table.
30. The computer-storage medium of claim 21 , the operations further comprising:
updating the error table to include an identification of the integrity constraint and an expression of the desired configuration associated with the integrity constraint.