CHECK constraint
A CHECK constraint is a rule attached to a table column (or the table as a whole) that requires every row to satisfy a given boolean expression.
Overview
A CHECK constraint lets you enforce business rules directly in the schema instead of relying on application code to validate every write. Any INSERT or UPDATE that would produce a row violating the expression is rejected by the database with a constraint error, so invalid data can never make it into the table regardless of which application or script wrote it.
Syntax
CHECK constraints can be attached to a single column or defined at the table level to reference multiple columns:
Copy code
CREATE TABLE orders (
id INTEGER PRIMARY KEY,
quantity INTEGER CHECK (quantity > 0),
discount_pct DECIMAL,
CHECK (discount_pct >= 0 AND discount_pct <= 100)
);
Use Cases
CHECK constraints are commonly used to enforce value ranges (a price can't be negative), simple cross-column invariants (a start date must precede an end date), or restricting a column to a fixed set of values when an enum type isn't a good fit.
DuckDB Support
DuckDB supports CHECK constraints with the same syntax shown above, evaluated at insert and update time. Because DuckDB is often used for bulk analytical loads, it's worth remembering that a CHECK constraint violation anywhere in a batch load will fail the whole statement — for very large loads it can be faster to validate data before loading and rely on CHECK as a safety net rather than a primary cleaning step.
Related terms
A UNIQUE constraint ensures that all values in a column, or combination of columns, are distinct across every row in a table.
primary key →A primary key uniquely identifies each database row. Learn SQL definitions, examples, UUID vs auto-increment best practices, and how to fix constraint errors.
Referential integrity →Referential integrity is the guarantee that a reference from one table to another — typically a foreign key — always points to a row that actually exists, preventing orphaned records.
UPDATE statement →The UPDATE statement modifies existing rows in a table, setting one or more column values for rows that match an optional WHERE condition.
ALTER TABLE statement →The ALTER TABLE statement allows you to modify the structure of an existing database table without having to recreate it from scratch.
CREATE TABLE statement →The CREATE TABLE statement is a fundamental SQL command that defines a new table in a database, specifying its structure including column names, data types,…
FAQS
The INSERT or UPDATE statement fails with a constraint error and no rows from that statement are written.
