Skip to content
Quantoros

What is a CHECK constraint?

SQLLevel: IntermediateDatabase Design

What is a CHECK constraint?

Hints
  • Define one output row and the source-table keys.
  • Check duplicates, NULLs and the order of filtering and aggregation.

Answer

A CHECK constraint ensures that values in a column satisfy a specified condition at insert/update time.

CREATE TABLE products (
    product_id   SERIAL PRIMARY KEY,
    name         VARCHAR(200) NOT NULL,
    price        NUMERIC(10,2) NOT NULL CHECK (price >= 0),
    stock        INT NOT NULL CHECK (stock >= 0),
    rating       NUMERIC(3,1) CHECK (rating BETWEEN 1 AND 5),
    status       VARCHAR(20) CHECK (status IN ('active', 'inactive', 'discontinued'))
);

Common follow-up: "Why put validation in the database instead of the application?" — Because multiple applications might share the database, and application-level validation can be bypassed. The database is the last line of defense.

Source and licence: SQL Interview Questions — Arda Kocadoru · MIT

Related questions