DataslopeCheck Constraints
Learn how CHECK constraints encode domain rules such as valid prices, statuses, and date ranges directly in the schema.
A product with a negative price is not a discounted product. It is bad
data. An order with status banana is not creative; it is invalid.
A CHECK constraint lets the schema say those things out loud.
CHECK encodes a domain rule
A CHECK constraint is a boolean expression that must be true for every row. If the expression is false, PostgreSQL rejects the write.
The expression can refer to columns in the same row. That makes CHECK a natural fit for rules like:
price >= 0quantity > 0status IN ('draft', 'published', 'archived')end_date > start_date
These are domain rules: they define which values make sense for the kind of thing the table stores.
Business rule to schema rule
The most important design move is translating a sentence about the business into a rule the database can enforce.
Once the rule lives in the schema, it applies to every writer. No import job, admin script, or forgotten form can sneak around it.
CHECK is a guardrail
A CHECK constraint is especially useful when a value has a small valid range, a minimum or maximum, or a relationship to another column in the same row.
Watch CHECK reject bad values
This table protects two rules: product prices cannot be negative, and status must be one of a small set of allowed values.
The error is the lesson: the schema refuses to contain rows that break its definition of a valid product.
CHECK with multiple columns
CHECK constraints can compare columns in the same row. For an event,
end_date should come after start_date.
This is still one-row logic. CHECK is excellent for rules that can be decided by looking at the row being written.
Quick check
Which business rule is a natural fit for a CHECK constraint?
Every order must reference an existing customer.
A product price must be greater than or equal to zero.
Every email must be different from every other email.
Every row must have a primary key.
CHECK vs. other constraints
CHECK is one member of the constraint family. It does not replace the others.
If the rule is "this value is required," use NOT NULL. If the rule is
"this value cannot repeat," use UNIQUE. If the rule is "this reference
must exist," use FOREIGN KEY. If the rule is "this expression must be
true for the row," use CHECK.
A note about NULL and CHECK
At a beginner level, the safe pattern is simple: combine CHECK with
NOT NULL when the value is required.
Why? SQL treats NULL as unknown. A CHECK constraint rejects false, but a
NULL expression may be unknown rather than false. If the column must be
present, say that directly with NOT NULL.
For example, prefer price NUMERIC NOT NULL CHECK (price >= 0) when
every product must have a price.
Quick check
Why are NOT NULL and CHECK (price >= 0) often used together for a required price?
CHECK automatically creates a price when it is missing.
NOT NULL requires the value to exist, and CHECK requires the existing value to be valid.
NOT NULL prevents duplicate prices.
CHECK makes the column a primary key.
Check your understanding
What does a CHECK constraint do?
It checks whether a query returned at least one row.
It requires a boolean expression to be true for each row being inserted or updated.
It checks spelling in text columns.
It checks whether the database has a backup.
Which SQL fragment best enforces that status is one of three allowed values?
status TEXT UNIQUE
status TEXT DEFAULT 'draft'
status TEXT CHECK (status IN ('draft', 'active', 'archived'))
status TEXT REFERENCES status
A rule says end_date must be after start_date in the same row. Which constraint type fits best?
DEFAULT.
UNIQUE.
CHECK.
FOREIGN KEY.