Skip to content
LLD Learn/Relational Modeling Foundations
Browsing as a guest. Sign in to save your progress and earn XP as you complete chapters.

Constraints, Audit Trails & Soft Deletes

4 min read

You'll learn to

  • -Use CHECK, UNIQUE and NOT NULL so the database itself refuses invalid data
  • -Explain an audit trail and an append-only table in terms of a receipt book
  • -Design a soft delete, and name its ongoing cost and one way to reduce it

So far we have shaped the data. Constraints restrict it. A constraint is a rule built into the schema so invalid data cannot exist, no matter which piece of application code writes it. This echoes the encapsulation chapter in Phase 1: a class protects its own invariants, and a schema protects its own constraints, for the same reason.

An Analogy: Guard Rails, a Receipt Book and a Recycle Bin

Constraints are guard rails on a mountain road. They do not depend on the driver paying attention. An audit trail is a receipt book: a permanent record of what happened and when. A soft delete is a recycle bin. The item is gone from view but can still be found and restored. The three ideas below map onto those three pictures.

CHECK, UNIQUE and NOT NULL

NOT NULL means a column must have a value. UNIQUE means no two rows may share it. CHECK means a condition must be true for every row. The lifeboat table below uses all three, plus DEFAULT, which fills in a value when none is given.

Business rules enforced at the database level
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, because it is a business rule, not just a data shape. Say a lifeboat holds 40 and currently has 39. Two boarding requests arrive at the same instant, and each reads 39 and adds one. Without the CHECK, the table could end up at 41. With it, the second write is rejected, whatever happened in the application. A missing validation, or a race between two requests, can never produce an overloaded lifeboat record, even briefly.

Audit Trails: Who Changed What, and When

An audit trail records the history of changes to important data, which matters for safety, money and compliance. The light version adds created_at and updated_at columns to a table. The fuller version writes an immutable log row to a separate table on every insert, update and delete, keeping the complete before-and-after history and not only the current state.

A separate, append-only audit table
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 suits an append-only design. Append-only means rows are only ever added; existing ones are never changed or removed. The whole point is an unmodifiable record, so there should be no update path at all. Convention is the minimum. In production, database permissions that remove UPDATE and DELETE rights on this table enforce it.

Soft Deletes: Marking Gone Without Deleting

A hard DELETE removes a row for good. That is simple, but it destroys history you may need later, such as a passenger record for insurance after the voyage. A soft delete adds a deleted_at timestamp, empty by default and set to the current time when the row is "deleted". The row stays, and every query must filter on deleted_at IS NULL to behave as though it were gone.

Soft delete via a nullable timestamp column
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 query on 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 come back.

One way to reduce that cost is a view, which is a saved query that behaves like a table. Define the filter once, and let ordinary queries read the view.

A view applies the soft-delete filter in one place
CREATE VIEW active_survivors AS
SELECT * FROM survivor_registry WHERE deleted_at IS NULL;

-- everyday queries use the view and cannot forget the filter:
SELECT * FROM active_survivors;

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.

Ready to Build This?

Design Lifeboat Allocation, the SOS Communication Log, and the Survivor Registry in the LLD Lab's Titanic act.

Discussion0

Join the Discussion

Sign in to leave comments, reply to others, or like insights.

Sign In to ScaleDojo

No comments yet. Be the first to start the thread!