CHECK Constraint
In this page:
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
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
CREATE TABLE products (price DECIMAL(10,2) CHECK (price > 0));
INSERT INTO products VALUES (-5); -- rejected: violates CHECKMultiple 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
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
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
ALTER TABLE products DROP CHECK chk_price;Chapter Quiz — Complete all 8 topics to unlock
0/8 topics done
Complete these topics first: