Check constraint: Difference between revisions

Revision as of 17:05, 14 April 2008

Revision as of 17:02, 3 September 2009

Line 41:

== Common Restrictions ==

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 not truly ''table check constraints'' but rathercurrent ''rowtransaction checkbecause constraints''.of [[DatabaseTransaction_isolation|transaction 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 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 [[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.

== References ==