Constraints, Audit Trails & Soft Deletes
You'll learn to
- -Use CHECK, UNIQUE, and NOT NULL constraints to push data validity into the schema instead of application code
- -Design audit columns and soft deletes so history is preserved without complicating every ordinary query
Everything so far has been about correctly shaping data. Constraints are about correctly restricting it - encoding business rules directly into the schema so invalid data cannot exist, regardless of which application code path writes it, echoing the encapsulation chapter from Phase 1: a class enforces its own invariants; a schema enforces its own constraints, for exactly the same reason.
CHECK, UNIQUE, and NOT NULL
CREATE TABLE lifeboats (
id INTEGER PRIMARY KEY,
boat_number TEXT NOT NULL UNIQUE, -- no two lifeboats share a number
capacity INTEGER NOT NULL CHECK (capacity > 0),
current_occupancy INTEGER NOT NULL DEFAULT 0
CHECK (current_occupancy <= capacity) -- can never exceed physical capacity
);The CHECK on current_occupancy is the interesting one: it is a business rule ("a lifeboat cannot hold more people than its capacity"), not just a data-shape rule, and enforcing it in the schema means no application bug - a missing validation check, a race condition between two concurrent boarding requests - can ever result in an overloaded lifeboat record existing in the database, even briefly.
Audit Trails: Who Changed What, and When
An audit trail records the history of changes to important data - critical for anything involving safety, money, or compliance. The lightweight version adds created_at and updated_at timestamp columns directly to a table; the fuller version writes an immutable log row to a separate audit table on every insert/update/delete, preserving the complete before-and-after history rather than just the current state.
CREATE TABLE sos_communication_log (
id INTEGER PRIMARY KEY,
message TEXT NOT NULL,
sent_by TEXT NOT NULL,
sent_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
-- no UPDATE or DELETE ever performed on this table - append-only by convention
);An SOS log is a natural fit for append-only design specifically: the whole point of the table is an unmodifiable historical record, so there should be no update path at all, enforced by convention (and, in a production system, by database permissions that revoke UPDATE/DELETE privileges on this table entirely).
Soft Deletes: Marking Gone Without Actually Deleting
A hard DELETE permanently removes a row - simple, but it destroys history that might be needed later (a passenger record for insurance purposes, even after the voyage). A soft delete instead adds a deleted_at timestamp column, NULL by default, set to the current time when a row is "deleted" - the row physically remains, but every query needs to filter WHERE deleted_at IS NULL to behave as if it were gone.
CREATE TABLE survivor_registry (
id INTEGER PRIMARY KEY,
passenger_id INTEGER NOT NULL,
status TEXT NOT NULL,
deleted_at TIMESTAMP, -- NULL means "active"
FOREIGN KEY (passenger_id) REFERENCES passengers(id)
);
-- every normal query must remember this filter:
SELECT * FROM survivor_registry WHERE deleted_at IS NULL;The real cost of soft deletes is that every single query against the table must remember the deleted_at IS NULL filter, forever - forget it once in a report or an admin tool, and "deleted" records silently reappear. Some teams handle this with a database view that already applies the filter, so ordinary queries go through the view and never have to remember it themselves.
Interview Signal is part of Pro
See a real weak answer next to a real strong one for this exact topic.
Quiz is part of Pro
Test what you just read with a short quiz, and bank the XP.
Design Lifeboat Allocation, the SOS Communication Log, and the Survivor Registry in the LLD Lab's Titanic act.