Constraints and Triggers: Put Invariants Where Every Writer Must Obey

An invariant is a fact that must remain true regardless of which application, migration, script, or administrator writes the data. If the database can express it directly, the database is usually the strongest enforcement point.

Use the simplest mechanism that fits

CREATE TABLE account (
  account_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  email      text   NOT NULL UNIQUE,
  balance    numeric(12,2) NOT NULL CHECK (balance >= 0),
  status     text   NOT NULL CHECK (status IN ('active', 'frozen'))
);

CREATE TABLE transfer (
  transfer_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  from_id     bigint NOT NULL REFERENCES account(account_id),
  to_id       bigint NOT NULL REFERENCES account(account_id),
  amount      numeric(12,2) NOT NULL CHECK (amount > 0),
  CHECK (from_id <> to_id)
);

The hierarchy is intentional:

1. Type and NOT NULL define basic shape.
2. CHECK validates one row.
3. UNIQUE and primary keys prevent duplicate identities.
4. Foreign keys protect references between tables.
5. Triggers handle rules that cannot be expressed cleanly above.

In plain terms: use a lock before hiring a guard. Declarative constraints are visible, optimized, and automatically applied to every writer.

Constraints reject bad states

Application validation improves error messages but cannot replace database enforcement. Two requests can both pass an application-side “email is available” check and then race to insert. A unique constraint turns that race into one success and one explicit failure.

Foreign-key actions are business choices:

• RESTRICT or NO ACTION: reject deletion while children exist.
• CASCADE: delete or update dependent rows too.
• SET NULL: preserve the child but remove the relationship.

Do not select an action merely to make deletion convenient. It defines what historical data survives.

Use a trigger when the rule crosses an event boundary

A trigger may be justified for an audit record that must exist for every update:

CREATE FUNCTION audit_account_change() RETURNS trigger AS $$
BEGIN
  INSERT INTO account_audit(account_id, old_status, new_status, changed_at)
  VALUES (OLD.account_id, OLD.status, NEW.status, current_timestamp);
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER account_status_audit
AFTER UPDATE OF status ON account
FOR EACH ROW
WHEN (OLD.status IS DISTINCT FROM NEW.status)
EXECUTE FUNCTION audit_account_change();

The trigger is part of the same transaction. If the audit insert fails, the update fails too.

Why triggers become dangerous

Triggers are implicit: an apparently simple update can execute more writes, acquire more locks, or fail for a reason hidden from the caller. Trigger chains make order and recursion difficult to understand.

Avoid triggers for values that can be derived in a query, calls to external services, and long workflows. Prefer an explicit transaction or an outbox event for work that crosses the database boundary.

Test failure, not only success

For each rule, keep one statement that must fail:

INSERT INTO transfer(from_id, to_id, amount)
VALUES (10, 10, -5);

The exact constraint named in the error tells you whether enforcement is placed where intended.

Review card

• Put invariants at the lowest clear declarative level.
• Application validation and database constraints solve different problems.
• Foreign-key actions define data lifecycle.
• Triggers are transactional but implicit.
• Keep trigger work small, deterministic, and inside the database.
• Test expected rejection paths.

CREATE TABLE account (
  account_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  email      text   NOT NULL UNIQUE,
  balance    numeric(12,2) NOT NULL CHECK (balance >= 0),
  status     text   NOT NULL CHECK (status IN ('active', 'frozen'))
);

CREATE TABLE transfer (
  transfer_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  from_id     bigint NOT NULL REFERENCES account(account_id),
  to_id       bigint NOT NULL REFERENCES account(account_id),
  amount      numeric(12,2) NOT NULL CHECK (amount > 0),
  CHECK (from_id <> to_id)
);

CREATE FUNCTION audit_account_change() RETURNS trigger AS $$
BEGIN
  INSERT INTO account_audit(account_id, old_status, new_status, changed_at)
  VALUES (OLD.account_id, OLD.status, NEW.status, current_timestamp);
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER account_status_audit
AFTER UPDATE OF status ON account
FOR EACH ROW
WHEN (OLD.status IS DISTINCT FROM NEW.status)
EXECUTE FUNCTION audit_account_change();

INSERT INTO transfer(from_id, to_id, amount)
VALUES (10, 10, -5);