Constraints encode business rules in the schema: uniqueness, allowed ranges, required fields, and valid references. They protect data quality when multiple apps write to the same database.
Common constraints
CREATE TABLE employees (
id INTEGER PRIMARY KEY,
email TEXT NOT NULL UNIQUE,
department TEXT CHECK (department IN ('eng', 'sales', 'ops')),
salary REAL CHECK (salary >= 0)
);Practice: Run DDL in a fresh SQLite file or DB Fiddle schema pane. Drop test tables when experimenting.
NOT NULL and UNIQUE
NOT NULL rejects missing values. UNIQUE rejects duplicate combinations—NULL handling in UNIQUE varies slightly by engine (Postgres allows multiple NULLs in unique columns).
CHECK in practice
INSERT INTO employees (email, department, salary)
VALUES ('a@x.com', 'eng', 80000); -- ok
-- Fails CHECK:
INSERT INTO employees (email, department, salary)
VALUES ('b@x.com', 'legal', 80000);Complex rules may live in application validation too—constraints are the last line of defense.
Important interview questions and answers
- Q: CHECK vs app validation?
A: Both—constraints catch bugs from any client; apps give friendlier error messages. - Q: UNIQUE on (a, b)?
A: Pair must be unique together; either column can repeat independently.
Self-check
- Which constraint ensures email appears only once?
- What does CHECK validate?
Tip: CHECK constraints catch bad data from any client, not just your app validation.
Interview prep
- UNIQUE vs PRIMARY KEY?
Both unique; PK is main identifier and NOT NULL.
- CHECK constraint?
Validates row data against a boolean expression.