A useful schema states the states the application can accept. Client-side checks improve feedback, but a database constraint protects every write path using that enforced schema. Choose a constraint according to the question: PRIMARY KEY identifies, UNIQUE forbids repeated candidate values, NOT NULL requires a value, CHECK constrains a row, and FOREIGN KEY connects a child to an existing parent key. No one constraint answers all five questions.
This lesson's ticket has two references to the same people table with different meanings. Requester is required and deleting a referenced requester is restricted. Assignee is optional; deleting an assignee clears the assignment while retaining the ticket. A nullable foreign key accepts NULL without requiring a matching parent. That is useful for assignment, but insufficient for a mandatory requester.
Nullability also changes row checks. In SQLite, a CHECK rejects an expression evaluating to zero; NULL does not reject the row. Therefore CHECK(qty>0) alone is not a complete “required positive quantity” rule. Use NOT NULL as well. For cross-column state rules, make the intended alternatives explicit with IS NULL and IS NOT NULL so unknown does not accidentally pass an invalid case.
CREATE TABLE tickets(
id INTEGER PRIMARY KEY,
state TEXT NOT NULL CHECK(state IN ('open','closed')),
closed_at INTEGER,
CHECK((state='open' AND closed_at IS NULL)
OR (state='closed' AND closed_at IS NOT NULL))
);
INSERT INTO tickets VALUES(1,'open',NULL),(2,'closed',0);
SELECT id,state,closed_at FROM tickets ORDER BY id;Zero is a present timestamp in this bounded model. Replacing “present” with “positive” would reject a valid boundary. Similarly, a status DEFAULT would fill omitted values but would not by itself restrict explicitly supplied statuses. Distinguish value generation from value validation.
Foreign-key actions express a lifecycle contract. RESTRICT prevents losing a required referenced entity. SET NULL preserves the child while removing an optional association. CASCADE removes dependent rows. If one person is both requester and assignee, the required-requester restriction still prevents their deletion; the optional role does not override the required one. Foreign keys do not implement authorization or decide which user may see the ticket.
A row CHECK cannot query another table in ordinary SQLite. Put cross-table existence in a foreign key, and reference a parent primary/unique key. More complex cross-row workflow invariants need an explicitly designed mechanism outside this lab; inventing a CHECK with a subquery does not create one.
The build accepts valid open/closed combinations, rejects unknown parents and missing required values, and inspects the two declared deletion actions. Its trusted verifier deletes an assignee inside a rolled-back probe. Learner SQL cannot change PRAGMA settings; the harness enables foreign-key enforcement for each isolated connection. In an application, connection configuration is still part of the integrity boundary. Use the conceptual checks to explain why each rule exists, then repair the weakened constraints without silently fixing invalid source data.