SQL: constraints keep the data valid
Use PRIMARY KEY, NOT NULL, UNIQUE, CHECK, and REFERENCES so the database itself rejects bad rows.
CREATE TABLE dept (id INTEGER PRIMARY KEY, name TEXT NOT NULL UNIQUE);
CREATE TABLE emp (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
salary INTEGER NOT NULL CHECK (salary > 0),
dept_id INTEGER NOT NULL REFERENCES dept(id)
);
INSERT INTO dept VALUES (1, 'Eng');
INSERT OR IGNORE INTO dept VALUES (2, 'Eng');
INSERT OR IGNORE INTO emp VALUES (1, 'Ada', 120, 1);
INSERT OR IGNORE INTO emp VALUES (2, 'Bo', -5, 1);
INSERT OR IGNORE INTO emp VALUES (3, 'Cy', 90, 99);
SELECT (SELECT COUNT(*) FROM dept) AS departments, (SELECT COUNT(*) FROM emp) AS employees;
PRAGMA foreign_key_check;1|2 emp|3|dept|0
Explanation
A constraint is a rule that the database enforces on every change. PRIMARY KEY identifies each row, NOT NULL forbids missing values, UNIQUE forbids duplicates, CHECK requires a condition on the row, and REFERENCES (a foreign key) requires a value to match a row in another table. Putting these rules in the schema protects the data no matter which program writes to it.
INSERT OR IGNORE skips a row that would violate a UNIQUE, NOT NULL, CHECK, or PRIMARY KEY constraint, instead of stopping with an error. Here the second department named Eng and the employee with a negative salary are both skipped. SQLite does not enforce foreign keys unless PRAGMA foreign_keys = ON is set on the connection, so the employee with department 99 gets in, and PRAGMA foreign_key_check lists such orphan rows so they can be found and repaired. Server databases such as PostgreSQL enforce foreign keys by default.
Explore the dry run
CREATE TABLE dept (id INTEGER PRIMARY KEY, name TEXT NOT NULL UNIQUE);CREATE TABLE emp ( id INTEGER PRIMARY KEY, name TEXT NOT NULL, salary INTEGER NOT NULL CHECK (salary > 0), dept_id INTEGER NOT NULL REFERENCES dept(id)); INSERT INTO dept VALUES (1, 'Eng');INSERT OR IGNORE INTO dept VALUES (2, 'Eng');INSERT OR IGNORE INTO emp VALUES (1, 'Ada', 120, 1);INSERT OR IGNORE INTO emp VALUES (2, 'Bo', -5, 1);INSERT OR IGNORE INTO emp VALUES (3, 'Cy', 90, 99); SELECT (SELECT COUNT(*) FROM dept) AS departments, (SELECT COUNT(*) FROM emp) AS employees;PRAGMA foreign_key_check;Follow the example
Start to inspect the source, values, and output. These prepared steps explain the published sample; grouped loop steps describe multiple iterations.
View the complete dry-run table
| Step / line | What happens | State after step | Output so far |
|---|---|---|---|
| Every department has a unique id and a unique, non-empty name. | — | — | |
| A CHECK constraint: a row with a salary of zero or less is invalid. | — | — | |
| The name Eng already exists, so this row is skipped silently. | — | — | |
| The CHECK fails (−5 is not positive), so Bo is skipped. | — | — | |
| One department and two employees were stored: Ada and Cy. | — | 1|2 | |
| Cy's department 99 does not exist. With foreign keys not enforced, the check reports the orphan row as table, row id, parent table, and key index. | — | 1|2 emp|3|dept|0 |
Cost and common mistakes
Each constraint check is O(1) for CHECK and NOT NULL, and O(log n) for UNIQUE and foreign keys, which use an index.
- OR IGNORE hides problems. Use it when skipping duplicates is intended, and let errors surface when they are not.
- SQLite checks foreign keys only after PRAGMA foreign_keys = ON, which applies to the current connection only.
- Constraints are not a replacement for validating input in the application, but they are the last line of defence that other programs cannot bypass.
Try it yourself
What would the count of employees be with PRAGMA foreign_keys = ON at the start?
Reveal answer and reasoning
One. The employee with department 99 would be rejected with a foreign key error, so only Ada would remain.