CheckConstraints

CHECK constraints are a database-level mechanism that enforces domain integrity by validating each row against a boolean expression. Implemented as part of a table's schema, they can be defined as a table constraint or as an inline constraint on a single column, and are evaluated on INSERT and UPDATE operations. If the expression is false, the database rejects the change.

Syntax can vary slightly by database system, but common forms include: CREATE TABLE with a named table constraint, for example: CREATE TABLE employees ( id INT PRIMARY KEY, age INT, CONSTRAINT chk_age CHECK (age >= 0) ); Alternatively, a column-level inline constraint: age INT CHECK (age >= 0). Constraints can reference multiple columns, such as: CONSTRAINT chk_dates CHECK (start_date <= end_date).

Notes: Some constraints permit complex expressions using arithmetic, function calls, and comparisons; however, most systems disallow subqueries inside a CHECK expression and cannot reference other tables. Constraints are usually not deferrable, though PostgreSQL supports deferrable constraints; MySQL historically did not enforce CHECK until version 8.0.16; SQLite enforces CHECK constraints in most cases.

Management: You can view constraints via system catalogs; drop with ALTER TABLE ... DROP CONSTRAINT constraint_name. If you add a CHECK constraint to a table with existing data, the database will check existing rows and may reject the addition if any row violates the condition.

Common use cases include enforcing nonnegative values, date ranges, or enumerated sets. CHECK constraints are a lightweight tool for preserving data quality without requiring triggers or application-side validation.