How do you use a CHECK constraint, and what does it ignore?
CHECK rejects a row when a boolean expression on its columns is false. It passes when the result is true or NULL, so pair it with NOT NULL when the column must always be present. It cannot see other rows.
Explain it simply
A CHECK is a rule at the gate: "marks must be between 0 and 100". Every insert and update is tested. The catch is NULL: a check passes unless the expression is definitely false, so NULL marks get through. If NULL should be rejected, add NOT NULL as well.
Also, a CHECK looks only at the row being written, not at other rows or other tables.
Example
CREATE TABLE exam_results (
student_id int NOT NULL,
marks int CHECK (marks BETWEEN 0 AND 100),
grade text,
CONSTRAINT grade_valid CHECK (grade IN ('A','B','C','F')),
CONSTRAINT pass_has_grade CHECK (marks < 40 OR grade <> 'F')
);
INSERT INTO exam_results VALUES (1, 105, 'A');
-- ERROR: new row violates check constraint "exam_results_marks_check"
INSERT INTO exam_results VALUES (2, NULL, 'B'); -- accepted: NULL passes CHECKName constraints so errors are readable. For rules that span rows (for example "no overlapping bookings") use an exclusion constraint or unique index instead.
What to say in the interview
"CHECK validates a boolean expression per row and treats NULL as pass, so I combine it with NOT NULL when needed. It cannot compare with other rows; for that I use UNIQUE, foreign keys or exclusion constraints."
Common mistakes
- Expecting CHECK (marks > 0) to reject NULL.