Database table column annotation
According to an example, database constraint generation may include receiving data related to a table in a database, analyzing the data to determine a row count for a column of the table, and analyzing the data to determine a number of distinct values for the column of the table. A comparison value may be determined by comparing the row count to the number of distinct values. The database constraint generation may further include determining if the comparison value is within a threshold. If the comparison value is within the threshold, an annotation may be added to the column of the table such that the column is considered unique during a cardinality estimation process involving the table.
1. A method for database constraint generation, the method comprising:
receiving data related to a table in a database;
analyzing the data to determine a row count for a column of the table;
analyzing the data to determine a number of distinct values for the column of the table;
determining a comparison value by comparing the row count to the number of distinct values;
determining if the comparison value is within a threshold; and
if the comparison value is within the threshold, adding, by a processor, an annotation to the column specifying a soft-unique constraint for the column,
wherein the soft-unique constraint indicates the column is considered unique during a cardinality estimation process involving the table, and further indicates to restrict enforcement of a unique property of the column upon data insertions or updates to the column.
2. The method of claim 1 , wherein in response to a data insertion or update for the column and upon determining the column is annotated with the soft-unique constraint,
restricting enforcement of a unique property of the column for the data insertion or the update to the column.
3. The method of claim 2 , wherein restricting enforcement of the unique property of the column comprises:
restricting enforcement of a unique key or a primary key of the column upon the data insertion or the update to the column.
4. The method of claim 1 , wherein analyzing the data to determine the number of distinct values for the column of the table further comprises:
analyzing the data to determine the number of distinct values for each column of the table.
5. The method of claim 1 , wherein determining the comparison value by comparing the row count to the number of distinct values further comprises:
determining the comparison value as a percentage value based on a difference between the row count and the number of distinct values.
6. The method of claim 1 , wherein the threshold is greater than approximately 0%.
7. The method of claim 1 , further comprising:
configuring the threshold based on user input related to a percentage difference between the row count and the number of distinct values.
8. A database constraint generation apparatus comprising:
a memory storing machine-readable instructions to:
receive data related to a table in a database;
analyze the data to determine a row count for a column of the table;
analyze the data to determine a number of distinct values for the column of the table;
determine a comparison value by comparing the row count to the number of distinct values;
determine if the comparison value is within a threshold;
if the comparison value is within the threshold, add an annotation to the column specifying a soft-unique constraint for the column,
wherein the soft-unique constraint indicates the column is considered unique during a cardinality estimation process involving the table, and further indicates to restrict enforcement of a unique property of the column upon data insertions or updates to the column; and
in response to a data insertion or update for the column and in response to determining the column is annotated with the soft-unique constraint, restrict enforcement of a unique property of the column for the data insertion or the update to the column; and
a processor to implement the machine-readable instructions.
9. The database constraint generation apparatus of claim 8 , wherein the machine-readable instructions are further to:
consider the column unique during a cardinality estimation process involving the table responsive to determining the column is annotated with the soft-unique constraint of the column.
10. The database constraint generation apparatus of claim 8 , wherein to restrict enforcement of the unique property of the column upon the data insertion or update, the machine-readable instructions are further to:
restrict enforcement of a unique key or a primary key of the column upon the data insertion or the data update to the column.
11. The database constraint generation apparatus of claim 8 , wherein to analyze the data to determine the number of distinct values for the column of the table, the machine-readable instructions are further to:
analyze the data to determine the number of distinct values for each column of the table.
12. The database constraint generation apparatus of claim 8 , wherein to determine the comparison value by comparing the row count to the number of distinct values, the machine-readable instructions are further to:
determine the comparison value as a percentage value based on a difference between the row count and the number of distinct values.
13. The database constraint generation apparatus of claim 8 , wherein the threshold is greater than approximately 0%.
14. The database constraint generation apparatus of claim 8 , the machine-readable instructions are further to:
configure the threshold based on user input related to a percentage difference between the row count and the number of distinct values.
15. A non-transitory computer readable medium having stored thereon machine-readable instructions to provide database constraint generation, the machine-readable instructions, when executed, cause a computer system to:
receive data related to a table in a database;
analyze the data to determine a row count for a column of the table;
analyze the data to determine a number of distinct values for the column of the table;
determine a comparison value by comparing the row count to the number of distinct values;
determine if the comparison value is within a threshold; and
if the comparison value is within the threshold, add, by a processor, an annotation to the column of the table to:
restrict enforcement of a unique property of the column upon data insertions or updates to the column, and
consider the column unique during a cardinality estimation process involving the table.
16. The non-transitory computer readable medium of claim 15 , wherein the machine-readable instructions, when executed, cause a computer system to:
in response to a data insertion or update for the column and in response to determining the column is annotated with the soft-unique constraint, restrict enforcement of a unique property of the column for the data insertion or the update to the column; and
consider the column unique during a cardinality estimation process involving the table responsive to determining the column is annotated with the soft-unique constraint of the column.