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