An application form is not the only way data reaches a database. Constraints provide another line of defence by expressing required values, uniqueness, references and allowed conditions where records are stored.
Choose a constraint for a specific invariant
NOT NULL rejects missing values, UNIQUE restricts duplicates according to database semantics, CHECK tests an allowed condition, and foreign keys enforce references. Each solves a different problem. A constraint should reflect a genuine invariant rather than an assumption that later makes legitimate records impossible to represent.
Treat NULL as missing or unknown
NULL is not automatically zero or an empty string. Comparisons involving NULL can produce unknown results, so use IS NULL or IS NOT NULL when that is the intended test. The treatment of NULL in uniqueness and other details can vary between databases; verify the engine’s rules rather than relying on a remembered example.
Handle rejected writes clearly
A failed constraint should not become an unexplained 500 page for an ordinary user action. Translate expected conflicts into a useful message and keep the existing valid record intact. Application validation still improves feedback, while the database constraint protects against bypasses and competing requests.
Worked example
The example stores one missing email and one supplied email. IS NULL selects the missing value explicitly. The table also requires a name; an empty string remains a separate business-validation decision rather than being identical to SQL NULL.
CREATE TABLE contacts (id INTEGER PRIMARY KEY, name TEXT NOT NULL, email TEXT UNIQUE);
INSERT INTO contacts VALUES (1,'Ava',NULL), (2,'Noah','noah@example.test');
SELECT name FROM contacts WHERE email IS NULL;
Expected output
AvaCheck your understanding
Try each question before opening its explanation. These are free practice prompts; they do not use game chances.
Does NOT NULL reject an empty string?
Not by itself in the SQL model demonstrated here. NULL and an empty string are different values, so add an appropriate rule if empty text is not allowed.
Why is email = NULL not the right missing-value check?
Ordinary equality does not treat NULL as a known value to compare. Use IS NULL for the missing-value condition.
Which learner is returned by the example?
Ava is returned because that row’s email is NULL. Noah has a supplied string, so the missing-value condition does not match that row.
Sources and further reading
These original examples explain foundational concepts. Use the official references for complete, current details and version-specific behaviour.
About this guide: AI-assisted educational content with original examples and linked primary references. Found an issue? Tell us what to improve.