0Pricing
SQL Academy · Lesson

NOT NULL and CHECK Constraints

Forbid missing values with NOT NULL and validate domain rules with CHECK (price > 0, status IN ('active','archived')).

NOT NULL and CHECK Constraints is a free SQL Academy lesson on CoddyKit — lesson 1 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the SQL Academy learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.

Why Constraints?

Constraints push validation INTO the database. The benefit is huge: every app, script, and ad-hoc query writes valid data — or fails loudly.

NOT NULL

The simplest constraint: this column must have a value:

CREATE TABLE users (
  id    BIGSERIAL PRIMARY KEY,
  email VARCHAR(255) NOT NULL,
  age   INT                                  -- NULL allowed
);

Adding NOT NULL Later

Backfill first, then add the constraint:

UPDATE users SET email = 'unknown@example.com' WHERE email IS NULL;
ALTER TABLE users ALTER COLUMN email SET NOT NULL;

CHECK Constraint

An arbitrary boolean expression that every row must satisfy:

CREATE TABLE products (
  id BIGSERIAL PRIMARY KEY,
  name TEXT NOT NULL,
  price NUMERIC(10,2) NOT NULL CHECK (price > 0),
  discount NUMERIC(5,2) DEFAULT 0 CHECK (discount BETWEEN 0 AND 100)
);

CHECK with Enum-Like Values

Constrain a column to a fixed set:

CREATE TABLE orders (
  id BIGSERIAL PRIMARY KEY,
  status TEXT NOT NULL
    CHECK (status IN ('pending','paid','shipped','cancelled'))
);

NULL Passes CHECK Silently

A CHECK constraint passes when the expression is NULL (unknown). Combine with NOT NULL when you need a hard guarantee:

CREATE TABLE invoices (
  amount NUMERIC(10,2) NOT NULL CHECK (amount > 0)
);

Multi-Column CHECK

You can reference multiple columns:

CREATE TABLE bookings (
  start_at TIMESTAMPTZ NOT NULL,
  end_at   TIMESTAMPTZ NOT NULL,
  CHECK (start_at < end_at)
);

Adding CHECK Later

Validate existing rows first, then add the constraint:

ALTER TABLE products
  ADD CONSTRAINT positive_price CHECK (price > 0);
-- fails if any row violates it

NOT VALID Trick for Big Tables

Skip the upfront full scan; validate later in the background:

ALTER TABLE products
  ADD CONSTRAINT positive_price CHECK (price > 0) NOT VALID;
ALTER TABLE products VALIDATE CONSTRAINT positive_price;

Named Constraints

Always name your constraints so error messages and migrations are predictable:

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

Don't Replicate Logic in the App

If the DB enforces it, every code path benefits — no "I forgot to validate". Keep duplicate validation in the app for UX (instant feedback), but make the DB the source of truth.

Recap

NOT NULL and CHECK lock out the most common bad data.

  • NOT NULL: no missing values
  • CHECK: arbitrary per-row rules
  • Use NOT VALID + VALIDATE for online additions

Quick Check

What happens if you write CHECK (price > 0) on a column with no NOT NULL?

Frequently asked questions

Is the “NOT NULL and CHECK Constraints” lesson free?

Yes — the full text of “NOT NULL and CHECK Constraints” is free to read here on the web, and the SQL Academy course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the SQL Academy course, upgrade to CoddyKit PRO.

What will I learn in “NOT NULL and CHECK Constraints”?

Forbid missing values with NOT NULL and validate domain rules with CHECK (price > 0, status IN ('active','archived')). You practise SQL Academy with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.

Do I need any experience to start SQL Academy?

No prior experience is required. SQL Academy on CoddyKit is structured for beginners through advanced learners; this is — lesson 1 of 4, so you can start here or from the beginning and move at your own pace.

How long does the “NOT NULL and CHECK Constraints” lesson take?

Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.

Can I write and run code in this SQL Academy lesson?

Yes. Every SQL Academy lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.

All lessons in this course

  1. NOT NULL and CHECK Constraints
  2. UNIQUE Constraints and Composite Keys
  3. FOREIGN KEY and Referential Actions
  4. DEFAULT Values and Generated Columns
← Back to SQL Academy