SQL CHECK

What is the SQL CHECK constraint?

CHECK enforces a boolean condition on each row, preventing inserts or updates that violate the rule.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Compatible dialects for SQL CHECK: PostgreSQL, MySQL (8.0+), SQL Server, Oracle, SQLite, MariaDB, DB2, Snowflake, Redshift

SQL CHECK Full Explanation

The SQL CHECK constraint lets you embed custom validation logic directly in a table definition. For every INSERT or UPDATE, the database evaluates the supplied boolean expression; if the expression does not return TRUE (or UNKNOWN when NULLs are allowed with the NOT VALID option in some dialects), the statement fails with an error. CHECK can be declared at the column level (applies only to that column) or at the table level (can reference multiple columns). Multiple CHECK constraints may coexist on a single table. Because the logic lives in the database, it protects data integrity regardless of the application writing to it.Important caveats:- Expressions cannot contain subqueries in some dialects (e.g., MySQL).- Functions used in the expression must be deterministic for optimal performance.- NULL handling: if any operand is NULL, most dialects treat the overall result as UNKNOWN, which passes the check unless the column is declared NOT NULL.- Disabling or dropping a CHECK requires ALTER TABLE. Some engines accept NOT VALID to add a constraint without scanning existing rows, followed by VALIDATE to enforce retrospectively.

SQL CHECK Syntax

-- Column-level
col_name data_type CHECK (boolean_expression)

-- Table-level (inside CREATE TABLE)
CONSTRAINT constraint_name CHECK (boolean_expression)

-- Add later
ALTER TABLE table_name
  ADD CONSTRAINT constraint_name CHECK (boolean_expression);

-- Disable (engine-specific)
ALTER TABLE table_name NOCHECK CONSTRAINT constraint_name;

SQL CHECK Parameters

constraint_name (optional) - identifier - User-defined name for the constraint
boolean_expression (Boolean) - Condition that must evaluate to TRUE for every row

Example Queries Using SQL CHECK

-- 1. Column-level check
CREATE TABLE accounts (
  id            SERIAL PRIMARY KEY,
  balance       NUMERIC(12,2) CHECK (balance >= 0)
);

-- 2. Table-level check referencing two columns
CREATE TABLE employees (
  id          INT PRIMARY KEY,
  salary      NUMERIC(10,2),
  bonus       NUMERIC(10,2),
  CONSTRAINT positive_comp CHECK (salary + bonus >= 0)
);

-- 3. Add later and validate
ALTER TABLE orders
  ADD CONSTRAINT chk_future_date CHECK (order_date <= CURRENT_DATE) NOT VALID;
ALTER TABLE orders VALIDATE CONSTRAINT chk_future_date;

Expected Output Using SQL CHECK

If a new row violates the CHECK (e
g
, negative balance), the statement aborts and the database returns an error like "ERROR: new row for relation 'accounts' violates check constraint 'accounts_balance_check'"

Use Cases with SQL CHECK

Enforce domain rules (e.g., quantity > 0, rating BETWEEN 1 AND 5)
Guarantee cross-column consistency (start_date <= end_date)
Prevent future or past dates outside allowed ranges
Replace some application-layer validations for stronger security
Add safety nets during migrations when schemas evolve

Common Mistakes with SQL CHECK

Forgetting that NULL bypasses the CHECK if the column is nullable
Using non-deterministic functions like NOW() in a CHECK, which some engines forbid
Trying to reference other tables (CHECK cannot contain subqueries in many dialects)
Assuming CHECK automatically adds an index (it does not)
Misnaming the constraint and then struggling to disable or drop it

First Introduced In

SQL-92

Frequently Asked Questions

What happens if the CHECK expression evaluates to UNKNOWN?

If any part of the expression is NULL, the result becomes UNKNOWN. Most engines treat UNKNOWN the same as TRUE for CHECK evaluation, so the row is accepted unless a NOT NULL constraint also fails.

Can I add a CHECK constraint to a table that already contains invalid data?

Yes. Add it with NOT VALID (PostgreSQL) or WITH NOCHECK (SQL Server) to skip existing rows, then fix the data and run VALIDATE to enforce.

Is a CHECK constraint the same as a trigger?

No. A CHECK is simpler, declarative, and runs automatically without procedural code. Triggers are more flexible but require writing and maintaining procedural logic.

Do CHECK constraints hurt performance?

The overhead is usually negligible for simple expressions. Complex expressions or the use of functions can increase CPU cost, but the trade-off is stronger data integrity.