Check constraint: Difference between revisions

Revision as of 22:54, 23 September 2006

Revision as of 19:01, 6 November 2006

Line 1:

A '''check constraint''' (also known as '''table check constraint''') is a rulecondition that defines valid [[data]] when adding or updating an entry in a table of a relational [[database]]. A check constraint is applied to aeach columnrow in the databasetable. The constraint must be a predicate. The result of the predicate can be either TRUE, FALSE, or UNKNOWN, depending on the presence of [[BooleanNull expression(SQL)|NULLs]]. If the predicate evaluates to UNKNOWN, then the constraint is not violated and the row can be inserted or updated in the table. This is contrary to predicates in WHERE clauses in [[SELECT|Select (SQL)]] or [[UPDATE|Update (SQL)]] statements.

For example, in a databasetable containing products, one could add a check constraint such that the price of a product and quantity of a product is either zero or a positivenon-negative value:

PRICE >= 0

Line 9:

If these constraints were not in place, it would be possible to have a negative price (-$30) or quantity (-3 items).

Check constraints are used to ensure the validity of data in a database and to provide [[data integrity]]. If they are used at the database level, applications that use the database will not be able to add invalid data or modify valid data so the data becomebecomes invalid, even if the application itself accepts invalid data.

==External link ==