← read more

Postgres Constraints That Replace Application Code

Rules your database can enforce so no code path, queue worker, or manual SQL edit can break them: foreign keys with real actions, CHECK constraints, unique and partial indexes, exclusion constraints.

Postgres Constraints That Replace Application Code

Every app has rules that must never be broken, and most teams keep them in application code where every code path has to remember them. The controller checks, the API endpoint checks, the queued job checks, and the one-off script someone runs in a ticket forgot to. PostgreSQL can hold a large share of these rules itself, and once a rule lives in the schema, no path into the data can violate it. Here are the ones we reach for in every Laravel and Go project.

Foreign keys with explicit actions

The most common gap we find in inherited databases is a relation without a foreign key. Rows point at other rows that no longer exist, counts go wrong quietly, and every fix is a data cleanup. Then there's the opposite problem: a foreign key with the wrong action, blocking deletions the app legitimately needs to make.

The decisions are per relation, and they're worth writing down:

-- a comment dies with its post, but invoice rows are kept for accounting
ALTER TABLE comments
  ADD CONSTRAINT comments_post_fk FOREIGN KEY (post_id)
  REFERENCES posts(id) ON DELETE CASCADE;

ALTER TABLE invoice_items
  ADD CONSTRAINT invoice_items_invoice_fk FOREIGN KEY (invoice_id)
  REFERENCES invoices(id) ON DELETE RESTRICT;

The choice deserves a thought because the default (ON DELETE NO ACTION) silently turns every delete of a parent into a conditional check buried in application behavior. One caveat from production: cascading deletes through more than one level of depth get hard to reason about, and a delete of a parent with a million descendants can hold locks long enough to stall the app. Cascade one level where the child is clearly owned; where in doubt, restrict and delete explicitly in a job.

In Laravel, migrations make this cheap to define up front and painful to retrofit, so the moment to do it is the moment you create the table.

CHECK constraints: ranges and shapes the column must obey

A CHECK is one line that outlives every refactor of the code around it:

ALTER TABLE subscriptions
  ADD CONSTRAINT subscriptions_quantity_positive CHECK (quantity > 0);

ALTER TABLE invoices
  ADD CONSTRAINT invoices_dates_ordered CHECK (due_date >= issued_at);

The classic app-level version is a validation rule plus a "we'll get to it" code path, and the classic failure is the queued job or the admin panel that skipped the validation. With the constraint in place, an attempt to write negative quantities fails the same way from anywhere, raising a clean error your code maps to a 422. Migrations in Laravel accept them with ->check() (or a raw statement) ; the syntax differs by version and driver, but the idea doesn't.

Two habits come with it. Don't encode business rules that change with the seasons; a constraint is for invariants, not for current pricing policy. And when you do change a constraint later, PostgreSQL 12 and up validate NOT VALID constraints without a long table scan, so the change is deployable on a live system.

Uniqueness beyond the primary key

Unique indexes encode business rules, and they're the cheapest race-condition defense in the schema:

-- one subscription per customer per plan, enforced even under concurrent requests
CREATE UNIQUE INDEX subscriptions_active_unique
  ON subscriptions (customer_id, plan_id) WHERE canceled_at IS NULL;

-- a coupon a customer can redeem once
CREATE UNIQUE INDEX coupon_redemptions_unique
  ON coupon_redemptions (coupon_id, customer_id);

-- emails are case-insensitive; TEXT UNIQUE alone lets "Max@example.com" through twice
CREATE UNIQUE INDEX users_email_lower_unique ON users (lower(email));

The partial index (the first one) is the Postgres answer to "unique unless soft-deleted", a shape every SaaS has and MySQL makes you fake with nullable columns. When an insert violates one of these, Postgres returns a specific error you can catch, and your application translates it into a message instead of doing arithmetic on counts. We covered this pattern against double-spending in our race-conditions article: the constraint is what makes two simultaneous requests safe, the app check is only for nicer error text.

Exclusion constraints: rules no index can state

Some rules aren't equality. A meeting room can't have two overlapping bookings, a worker can't be assigned two shifts that overlap, a price for a given date range can't overlap its neighbors. EXCLUDE with a range type expresses exactly that, and the database refuses the bad insert:

CREATE EXTENSION IF NOT EXISTS btree_gist;

ALTER TABLE bookings
  ADD CONSTRAINT bookings_room_no_overlap
  EXCLUDE USING gist (
    room_id WITH =,
    daterange(check_in, check_out) WITH &&
  );

This is the rule we used to see implemented as "query for overlaps, then insert", which is the same timing gap as every other check-then-write bug. One constraint, no gaps, and the error is catchable and testable.

Not-validating, then validating: adding a constraint to a live table

Adding a constraint to a table with years of data hits a real problem: existing rows may violate the rule, and validating across them takes a table scan long enough to matter. Two steps make it deployable on production:

ALTER TABLE orders
  ADD CONSTRAINT orders_total_positive CHECK (total > 0) NOT VALID;
ALTER TABLE orders VALIDATE CONSTRAINT orders_total_positive;

NOT VALID applies the rule to new writes immediately and takes no scan; VALIDATE CONSTRAINT then checks existing rows under a weaker lock. Between the two statements is also the moment to fix the pre-existing rows the scan is about to hit. This is the schema-level cousin of the expand/contract pattern we use for column renames: two boring steps instead of one risky one.

What stays in the application

Constraints can't express everything and shouldn't try. Rules that depend on other tables' changing state, that need a policy check ("only the owner's editor may set this"), or that produce user-shaped error messages belong in the domain layer. The split we aim for is: the schema holds the invariants that must never break under any path, the application holds invariants plus everything the user needs to be told about.

The payoff for getting this split right looks like nothing: the one-off SQL script in a ticket, the queue worker, and the fresh joiner's pull request all write data the same way, because the database does the remembering.