SQL: constraints keep the data valid
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.
SQL
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;