Check constraint: Difference between revisions

Revision as of 07:59, 1 September 2012

Revision as of 15:32, 26 September 2012

Line 1:

A '''check constraint''' (also known as '''table check constraint''') is a condition that defines valid [[data]] when adding or updating an entry in a table of a relational [[relational database]]. A check constraint is applied to each [[Row (database)|row]] in the [[Table (database)|table]]. The constraint must be a [[Predicate (mathematical logic)|predicate]]. It can refer to a single or multiple [[Column (database)|columns]] of the table. The result of the predicate can be either TRUE, FALSE, or UNKNOWN, depending on the presence of [[Null (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 (SQL)|WHERE]] clauses in [[Select (SQL)|SELECT]] or [[Update (SQL)|UPDATE]] statements.

For example, in a table containing products, one could add a check constraint such that the price of a product and quantity of a product is a non-negative value:

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 [[Data validation|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 becomes invalid, even if the application itself accepts invalid data.

==Definition==

Line 32:

)

==NOT NULL Constraintconstraint==

A NOT [[Null (SQL)|NULL]] constraint is functionally equivalent to the following check constraint with an <code>IS NOT NULL</code> predicate:

Line 39:

Some [[relational database management system]]s are able to optimize performance when the <code>NOT NULL</code> constraint syntax is used as opposed to the CHECK constraint syntax given above.<ref>PostgreSQL 8.3devel Documentation, Chapter 5. ''Data Definition'', Section 5.3.2. ''Not-Null Constraints'', Website: http://developer.postgresql.org/pgdocs/postgres/ddl-constraints.html, Accessed on May 5, 2007</ref>

== Common Restrictionsrestrictions ==

Most database management systems restrict check constraints to a single row, with access to constants and deterministic functions, but not to data in other tables, or to data invisible to the current transaction because of [[transaction isolation]].