NOT NULL

not-null.sql
CREATE TABLE customers (
    id INT PRIMARY KEY,
    email VARCHAR(255) NOT NULL
);

NOT NULL is the simplest constraint: it forbids a column from ever holding NULL. Any INSERT or UPDATE that would leave email empty is rejected outright — there's no way to bypass it short of removing the constraint.

UNIQUE

unique-constraint.sql
CREATE TABLE customers (
    id INT PRIMARY KEY,
    email VARCHAR(255) UNIQUE
);

UNIQUE rejects any row whose value in that column already exists elsewhere in the table. A table can have several unique constraints, unlike the single primary key — see Primary Key vs Foreign Key vs Unique Key Explained for how the two relate.

CHECK

check-constraint.sql
CREATE TABLE products (
    id INT PRIMARY KEY,
    price DECIMAL(10,2) CHECK (price >= 0),
    discount_pct INT CHECK (discount_pct BETWEEN 0 AND 100)
);

CHECK enforces any boolean condition you write, evaluated on every insert and update — here, rejecting a negative price or a discount percentage outside 0–100 before either is ever stored.

DEFAULT

default-constraint.sql
CREATE TABLE orders (
    id INT PRIMARY KEY,
    status VARCHAR(20) DEFAULT 'pending',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

DEFAULT supplies a value automatically whenever an INSERT doesn't specify one for that column — it only fills a gap, it doesn't reject anything. Pairing it with NOT NULL is common: DEFAULT handles the normal case of an omitted value, while NOT NULL still blocks an explicit attempt to insert NULL on top of it.

The MySQL CHECK constraint history

Here's a fact worth knowing if you work across MySQL versions: MySQL parsed CHECK constraint syntax without error for years, but silently ignored it entirely — a table could declare CHECK (price >= 0) and still happily accept negative prices, with no warning at all. This changed in MySQL 8.0.16 (released 2019), which finally implemented real enforcement of table and column CHECK constraints. Any MySQL 8.0.16+ instance enforces them properly; anything older does not, regardless of what the schema appears to declare.

Adding constraints to an existing table

alter-add-constraint.sql
ALTER TABLE products
ADD CONSTRAINT chk_price_nonnegative CHECK (price >= 0);

Adding a constraint to a table that already has rows forces the database to validate every existing row against it first — the ALTER TABLE fails outright if even one existing row would violate the new rule, so cleaning up bad data ahead of time is usually a prerequisite.

Key takeaways

  • NOT NULL rejects missing values; UNIQUE rejects duplicates; CHECK rejects values failing a custom condition; DEFAULT fills in a value when none is given.
  • MySQL silently ignored CHECK constraints before version 8.0.16 (2019) — verify your MySQL version before relying on one.
  • DEFAULT and NOT NULL are commonly paired: DEFAULT covers the omitted case, NOT NULL still blocks an explicit NULL.
  • Adding a constraint via ALTER TABLE validates all existing rows first and fails if any of them violate it.