Check Constraint

Check Constraint

A check constraint (also known as table 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.

Contents

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 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 are able to optimize performance when the NOT NULL constraint syntax is used as opposed to the CHECK constraint syntax given above.[1]

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. 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 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

  1. ^ 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

Wikimedia Foundation. 2010.

Игры ⚽ Нужно решить контрольную?

Look at other dictionaries:

  • constraint — [n1] force a must*, coercion, compulsion, driving, duress, goad, hang up*, impelling, impulsion, monkey*, motive, necessity, no no*, pressure, repression, restraint, spring, spur, suppression, violence; concepts 14,121 constraint [n2] shyness… …   New thesaurus

  • check — [n1] inspection, examination analysis, audit, checkup, control, inquiry, investigation, poll, rein, research, review, scrutiny, test; concept 103 check [n2] restraint, hindrance blow, constraint, control, curb, damper, disappointment, frustration …   New thesaurus

  • Constraint logic programming — Programming paradigms Agent oriented Automata based Component based Flow based Pipelined Concatenative Concurrent computing …   Wikipedia

  • Constraint — Mit Constraints (deutsch „Zwangsbedingungen“) werden in diversen Programmiersprachen Bedingungen definiert, die zwingend vom Wert einer Variablen erfüllt werden müssen, damit der Wert ins System übernommen werden kann. In Datenbanksystemen finden …   Deutsch Wikipedia

  • check — Synonyms and related words: CD, IOU, MO, X, a reckoning of, abrade, abrasion, abysm, abyss, acceptance, acceptance bill, accord, account, account of, agree, allophone, alveolar, answer to, aperture, apico alveolar, apico dental, arrest,… …   Moby Thesaurus

  • check — 1. verb 1) troops checked all vehicles I checked her background Syn: examine, inspect, look at/over, scrutinize, survey; study, investigate, research, probe, look into, inquire into; informal check out, give something a once over 2) …   Thesaurus of popular words

  • check — I (New American Roget s College Thesaurus) v. t. control, test, verify, tally, count; restrain, repress, halt, stop, arrest, impede, interrupt, curb; stunt. n. draft, money order; interruption, rebuff; setback, reverse, stop, restraint;… …   English dictionary for students

  • check — v 1. stop, arrest, stay, stall, bring to a standstill; halt, pause, hesitate, balk, brake; put a stop or an end to, terminate, end, quash, quell, squash, Inf. put the kibosh on, nip in the bud; cut, cut back on, decrease, diminish, reduce, lessen …   A Note on the Style of the synonym finder

  • check — 1. verb 1) troops checked all vehicles Syn: examine, inspect, look at/over, scrutinize, study, investigate, probe, look into, enquire into; informal check out, give something a/the once over 2) he checked that the gun was cocked Syn …   Synonyms and antonyms dictionary

  • constraint — noun Etymology: Middle English, from Middle French constrainte, from constraindre Date: 15th century 1. a. the act of constraining b. the state of being checked, restricted, or compelled to avoid or perform some action < the constraint and… …   New Collegiate Dictionary

Share the article and excerpts

Direct link
Do a right-click on the link above
and select “Copy Link”