HOME

TheInfoList



OR:

A check constraint is a type of integrity constraint in
SQL Structured Query Language (SQL) (pronounced ''S-Q-L''; or alternatively as "sequel") is a domain-specific language used to manage data, especially in a relational database management system (RDBMS). It is particularly useful in handling s ...
which specifies a requirement that must be met by each row in a database
table Table may refer to: * Table (database), how the table data arrangement is used within the databases * Table (furniture), a piece of furniture with a flat surface and one or more legs * Table (information), a data arrangement with rows and column ...
. The constraint must be a predicate. It can refer to a single column, or multiple
columns A column or pillar in architecture and structural engineering is a structural element that transmits, through compression, the weight of the structure above to other structural elements below. In other words, a column is a compression member ...
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 Data integrity is the maintenance of, and the assurance of, data accuracy and consistency over its entire Information Lifecycle Management, life-cycle. It is a critical aspect to the design, implementation, and usage of any system that stores, proc ...
. 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 the CREATE 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
NULL Null may refer to: Science, technology, and mathematics Astronomy *Nuller, an optical tool using interferometry to block certain sources of light Computing *Null (SQL) (or NULL), a special marker and keyword in SQL indicating that a data value do ...
constraint is functionally equivalent to the following check constraint with an IS NOT NULL predicate: CHECK (''column'' IS NOT NULL) Some
relational database management system A relational database (RDB) is a database based on the relational model of data, as proposed by E. F. Codd in 1970. A Relational Database Management System (RDBMS) is a type of database management system that stores data in a structured for ...
s are able to optimize performance when the NOT NULL constraint syntax is used as opposed to the CHECK constraint syntax given above.PostgreSQL 13 Documentation, Chapter 5. ''Data Definition'', Section 5.4.2. ''Not-Null Constraints'', Website: https://www.postgresql.org/docs/13/ddl-constraints.html, Accessed on Jan 9, 2021


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 could be violated by indirect action were it not for these limitations. Furthermore, otherwise-valid modifications to these records would then be prevented by the CHECK constraint. Some examples of dangerous constraints include: * * * {{code, 2=sql, 1=CHECK (countItems = RAND()) 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.


References

SQL