Check constraint: Difference between revisions

Revision as of 16:36, 21 June 2007

Latest revision as of 20:58, 25 January 2025

(29 intermediate revisions by 28 users not shown)

Line 1:

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

A '''check constraint''' (also known as '''table check constraint''') is a conditiontype thatof defines[[integrity validconstraint]] in [[dataSQL]] whenwhich addingspecifies ora updatingrequirement anthat entrymust inbe amet tableby of a relationaleach [[row (database)|row]]. Ain checka constraintdatabase is applied to each row in the[[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</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−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 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 possiblypossible to specify the constraint as part of the column definition.

CREATE TABLE ''table_name'' (

...

''column_name'' ''type'' CHECK ( ''predicate'' ),

...

)

==NOT NULL Constraintconstraint==

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 [[Relationalrelational database management system|relational database management systems]]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 Restrictionsrestrictions ==

Most database management systems restrict check constraints to a single row., with Referencesaccess to data stored in other tables is not allowed. At most constants and deterministic functions, (that dobut not refer to data in other tables), areor supported.to data Suchinvisible constraintsto arethe notcurrent trulytransaction ''tablebecause check constraints'' but rather ''row check constraints''.of [[Databasetransaction trigger|Triggersisolation]] can be used to work around this restriction.

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. Furthermore, otherwise-valid modifications to these records would then be prevented by the <code>CHECK</code> constraint. Some examples of dangerous constraints include:

== References ==

<references/>

* {{code|2=sql|1=CHECK ((select count(*) from invoices where invoices.customerId = customerId) < 1000)}}

== External Links ==

* {{code|2=sql|1=CHECK (dateInserted = CURRENT_DATE)}}

* [http://publib.boulder.ibm.com/infocenter/db2luw/v9/topic/com.ibm.db2.udb.admin.doc/doc/t0004984.htm Defining a table check constraint in DB2]

* {{code|2=sql|1=CHECK (countItems = RAND())}}

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.

[[Category:Databases]]

== References ==

<references/>

[[jaCategory:CHECK制約SQL]]