Content deleted Content added
No edit summary |
m Formatted SQL keywords. |
||
Line 1:
A '''check constraint''' is applied to each [[Row (database)|row]] in the [[Table (database)|table]].{{Clarify|April 2013|date=April 2013}} 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 <code>TRUE</code>, <code>FALSE</code>, or <code>UNKNOWN</code>, depending on the presence of [[Null (SQL)|NULLs]]. If the predicate evaluates to <code>UNKNOWN</code>, 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 33:
==NOT NULL constraint==
A <code>NOT [[Null (SQL)|NULL]]</code> constraint is functionally equivalent to the following check constraint with an <code>IS NOT NULL</code> predicate:
CHECK (''column'' IS NOT NULL)
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 <code>CHECK</code> 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 restrictions ==
Line 43:
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]].
Such constraints are not truly ''table check constraints'' but rather ''row check constraints''. Because these constraints are generally only verified when a row is directly updated (for performance reasons,) and often implemented as implied <code>INSERT</code> or <code>UPDATE</code> triggers, [[integrity constraints]] could be violated by indirect action were it not for these limitations.
* <code>CHECK ((select count(*) from invoices where invoices.customerId = customerId) < 1000)</code>
* <code>CHECK (dateInserted = CURRENT_DATE)</code>
* <code>CHECK (countItems = RAND())</code>
User-defined [[Database trigger|triggers]] can be used to work around these restrictions. Although similar in implementation, it is semantically clear that triggers will only be fired when the table is directly modified, and that it is the designer's responsibility to handle indirect, important changes in other tables; constraints on the other hand are intended to be "true at all times" regardless of the user's actions or the designer's lack of foresight.
| |||