Check Constraint
Encyclopedia
A check constraint is a condition that defines valid data
when adding or updating an entry in a table of a relational database
. A check constraint is applied to each row in the table. The constraint must be a predicate. It can refer to a single or multiple columns of the table. The result of the predicate can be either TRUE, FALSE, or UNKNOWN, depending on the presence of 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
or 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:
PRICE >= 0
QUANTITY >= 0
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 becomes invalid, even if the application itself accepts invalid data.
CREATE TABLE table_name (
...,
CONSTRAINT constraint_name CHECK ( predicate ),
...
)
ALTER TABLE table_name
ADD CONSTRAINT constraint_name CHECK ( predicate )
If the check constraint refers to a single column only, it is possible to specify the constraint as part of the column definition.
CREATE TABLE table_name (
...
column_name type CHECK ( predicate ),
...
)
constraint is functionally equivalent to the following check constraint with an
CHECK (column IS NOT NULL)
Some relational database management systems
are able to optimize performance when the
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 INSERT or UPDATE triggers, integrity constraints
could be violated by indirect action were it not for these limitations. Future, otherwise-valid modifications to these records would then be prevented by the CHECK constraint. Some examples of dangerous constraints include:
User-defined 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.
Data
The term data refers to qualitative or quantitative attributes of a variable or set of variables. Data are typically the results of measurements and can be the basis of graphs, images, or observations of a set of variables. Data are often viewed as the lowest level of abstraction from which...
when adding or updating an entry in a table of a relational database
Database
A database is an organized collection of data for one or more purposes, usually in digital form. The data are typically organized to model relevant aspects of reality , in a way that supports processes requiring this information...
. A check constraint is applied to each row in the table. The constraint must be a predicate. It can refer to a single or multiple columns of the table. The result of the predicate can be either TRUE, FALSE, or UNKNOWN, depending on the presence of NULLs
Null (SQL)
Null is a special marker used in Structured Query Language to indicate that a data value does not exist in the database. Introduced by the creator of the relational database model, E. F. Codd, SQL Null serves to fulfill the requirement that all true relational database management systems support...
. 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)
The SQL SELECT statement returns a result set of records from one or more tables.A SELECT statement retrieves zero or more rows from one or more database tables or database views. In most applications, SELECT is the most commonly used Data Manipulation Language command...
or UPDATE
Update (SQL)
An SQL UPDATE statement changes the data of one or more records in a table. Either all the rows can be updated, or a subset may be chosen using a condition.The UPDATE statement has the following form:...
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:
PRICE >= 0
QUANTITY >= 0
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
Data integrity
Data Integrity in its broadest meaning refers to the trustworthiness of system resources over their entire life cycle. In more analytic terms, it is "the representational faithfulness of information to the true state of the object that the information represents, where representational faithfulness...
. 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
Each check constraint has to be defined in theCREATE TABLE or ALTER TABLE statement using the syntax:CREATE TABLE table_name (
...,
CONSTRAINT constraint_name CHECK ( predicate ),
...
)
ALTER TABLE table_name
ADD CONSTRAINT constraint_name CHECK ( predicate )
If the check constraint refers to a single column only, it is possible to specify the constraint as part of the column definition.
CREATE TABLE table_name (
...
column_name type CHECK ( predicate ),
...
)
NOT NULL Constraint
A NOT NULLNull (SQL)
Null is a special marker used in Structured Query Language to indicate that a data value does not exist in the database. Introduced by the creator of the relational database model, E. F. Codd, SQL Null serves to fulfill the requirement that all true relational database management systems support...
constraint is functionally equivalent to the following check constraint with an
IS NOT NULL predicate:CHECK (column IS NOT NULL)
Some relational database management systems
Relational database management system
A relational database management system is a database management system that is based on the relational model as introduced by E. F. Codd. Most popular databases currently in use are based on the relational database model....
are able to optimize performance when the
NOT NULL constraint syntax is used as opposed to the CHECK constraint syntax given above.Common Restrictions
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 INSERT or UPDATE triggers, integrity constraints
Integrity constraints
Integrity constraints are used to ensure accuracy and consistency of data in a relational database. Data integrity is handled in a relational database through the concept of referential integrity...
could be violated by indirect action were it not for these limitations. Future, otherwise-valid modifications to these records would then be prevented by the CHECK constraint. Some examples of dangerous constraints include:
- CHECK ((select count(*) from invoices where invoices.customerId = customerId) < 1000)
- CHECK (dateInserted = CURRENT_DATE)
- CHECK (countItems = RAND)
User-defined triggers
Database trigger
A database trigger is procedural code that is automatically executed in response to certain events on a particular table or view in a database. The trigger is mostly used for keeping the integrity of the information on the database...
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.