SQL Check

SQL CHECK Constraint

The CHECK constraint is used to limit the value range that can be placed in a column. If a value violates the condition, the database rejects the insert or update.

Basic Syntax

CREATE TABLE table_name (
  column_name datatype,
  CHECK (condition)
);

Example: Age Must Be 18 or More

CREATE TABLE users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  age INT,
  CHECK (age >= 18)
);

Example: Price Must Be Positive

CREATE TABLE products (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(120) NOT NULL,
  price DECIMAL(10,2) NOT NULL,
  CHECK (price > 0)
);

Example: Column Must Be One of Specific Values

CREATE TABLE customers (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  status VARCHAR(20) NOT NULL,
  CHECK (status IN ('active', 'inactive', 'trial'))
);

Named CHECK Constraint

Naming constraints can make maintenance easier.

CREATE TABLE orders (
  id INT AUTO_INCREMENT PRIMARY KEY,
  total DECIMAL(10,2) NOT NULL,
  CONSTRAINT chk_orders_total CHECK (total >= 0)
);

Add CHECK with ALTER TABLE

ALTER TABLE products
ADD CONSTRAINT chk_products_price CHECK (price > 0);

Drop CHECK Constraint

Dropping CHECK constraints depends on database system support and syntax.

MySQL / MariaDB Note

Support for CHECK constraints can depend on the database version and storage engine. Always verify behavior in your environment.

Common Mistakes

• Assuming CHECK behaves the same in all databases
• Adding CHECK to a table with existing invalid data
• Forgetting to name constraints (harder to drop later)

Best Practice

Use CHECK for simple, predictable validation rules (ranges, allowed values). For complex validation, combine constraints with application-level checks.

Next Step

Continue with SQL DEFAULT to learn how to set automatic values when none are provided.