Keys, constraints, and valid rows

The idea

A schema defines more than column names. A key identifies a row, a UNIQUE constraint prevents duplicate accepted keys, and a foreign key describes a relationship to another table. These constraints protect against invalid writes from every application path, not just one form. Choose a stable identifier instead of assuming a mutable display name will remain unique forever.

NULL requires special care in constraints. In SQLite, a CHECK constraint rejects a zero result but does not reject a NULL result. A nonnegative required quantity therefore needs NOT NULL as well as CHECK(quantity >= 0). SQLite foreign-key enforcement must also be enabled for the connection when it is needed. State the intended deletion and update policies rather than assuming every related row should disappear automatically.

CREATE TABLE stock(id INTEGER PRIMARY KEY, quantity INTEGER NOT NULL CHECK(quantity >= 0));
INSERT INTO stock(id,quantity) VALUES(1,0),(2,4);
SELECT id,quantity FROM stock ORDER BY id;

id | quantity
1 | 0
2 | 4

Zero is a permitted quantity; the combination of constraints excludes both missing and negative quantities.