Check constraint: Difference between revisions

Revision as of 23:57, 24 July 2016

Latest revision as of 20:58, 25 January 2025

(3 intermediate revisions by 3 users not shown)

Line 1:

{{Short description|Type of integrity constraint in SQL}}

A '''check constraint''' is a type of [[integrity constraint]] in [[SQL]] which specifies a requirement that must be met by each [[row (database)|row]] in a database [[table (database)|table]]. The constraint must be a [[Predicate (mathematical logic)|predicate]]. It can refer to a single column, 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 <code>[[Where (SQL)|WHERE]]</code> clauses in <code>[[Select (SQL)|SELECT]]</code> or <code>[[Update (SQL)|UPDATE]]</code> 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 price >= 0

QUANTITY quantity >= 0

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

Line 15 ⟶ 16:

Each check constraint has to be defined in the <code>CREATE TABLE</code> or <code>ALTER TABLE</code> 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 <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.3devel13 Documentation, Chapter 5. ''Data Definition'', Section 5.34.2. ''Not-Null Constraints'', Website: httphttps://developerwww.postgresql.org/pgdocsdocs/postgres13/ddl-constraints.html, Accessed on MayJan 59, 20072021</ref>

== Common restrictions ==