CHECK Constraint

CHECK is like a bouncer with a rule, such as 'you must be at least 10 years old'. Any value that fails the rule is turned away.

In this page:

Understanding CHECK
Violating CHECK Constraints
Multiple CHECK Constraints
Adding CHECK to Existing Tables
Dropping CHECK Constraints

Syntax

sql

column_name datatype CHECK (condition)

Understanding CHECK

CHECK lets you restrict what values are allowed in a column by defining a boolean condition — for example, requiring a price column to always be greater than zero — that MySQL evaluates on every insert or update.

Example: Understanding CHECK

sql

CREATE TABLE products (price DECIMAL(10,2) CHECK (price > 0));

Violating CHECK Constraints

If an insert or update would violate a CHECK condition, MySQL rejects the entire statement and returns an error, rather than silently storing a value that breaks your business rules.

Example: Violating CHECK Constraints

sql

CREATE TABLE products (price DECIMAL(10,2) CHECK (price > 0));
INSERT INTO products VALUES (-5); -- rejected: violates CHECK

Multiple CHECK Constraints

You can attach multiple independent CHECK constraints to the same table, and naming each one explicitly makes the resulting error messages far easier to diagnose when a violation occurs.

Example: Multiple CHECK Constraints

sql

CREATE TABLE products (
  price DECIMAL(10,2),
  stock INT,
  CONSTRAINT chk_price CHECK (price > 0),
  CONSTRAINT chk_stock CHECK (stock >= 0)
);

Adding CHECK to Existing Tables

Adding a CHECK constraint to a table that already has data causes MySQL to validate every existing row against the new rule, and the ALTER TABLE will fail if any current row doesn't comply.

Example: Adding CHECK to Existing Tables

sql

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

Dropping CHECK Constraints

Removing a CHECK constraint requires the DROP CHECK clause along with the constraint's specific name, since a table may have several checks active and MySQL needs to know which one to lift.

Example: Dropping CHECK Constraints

sql

ALTER TABLE products DROP CHECK chk_price;

Chapter Quiz — Complete all 8 topics to unlock

0/8 topics done

Complete these topics first:

← Previous Lesson DEFAULT Constraint Next Lesson → AUTO_INCREMENT