Constraints in PostgreSQL

There's a difference between "we validate this in the application" and "the database won't let this happen." I used to assume application-level validation was enough — check the input in Flask, reject anything weird, move on. Then I had a bug where a background job inserted a closure record for a venue that had already been deleted, because the validation logic lived in one code path and the background job used a different one that skipped it. The database happily accepted it, because nothing at the database level said it couldn't.

Constraints are how you stop trusting your application code to always remember the rules, and instead make the rules structurally true no matter what code is writing to the table.

What happens without them

Picture a closures table that references venues by ID, but with no foreign key set up — just a plain integer column that's supposed to match an existing venue. For a while, everything looks fine, because your application code is careful about it. Then someone writes a one-off script to backfill some historical data, or a bug in some unrelated feature deletes a venue without cleaning up its closures first, and now you've got closure records pointing at venues that don't exist. Nothing errors. Nothing crashes. The data is just quietly wrong, and you don't find out until a report starts showing null venue names where there shouldn't be any.

This is the actual argument for constraints — not that they're a best practice you're supposed to follow, but that they close off entire categories of bugs from ever happening in the first place, instead of relying on every single piece of code that touches the table to remember the rules correctly.

The constraints worth knowing

Constraint	What it enforces	Example
PRIMARY KEY	uniqueness + not null, one per table	id SERIAL PRIMARY KEY
FOREIGN KEY	referential integrity between tables	venue_id INTEGER REFERENCES venues(id)
UNIQUE	no duplicate values in a column (or column combo)	UNIQUE(name, city)
NOT NULL	column can't be empty	name VARCHAR(100) NOT NULL
CHECK	custom rule on a value	CHECK (latitude BETWEEN -90 AND 90)
DEFAULT	value used when none is provided	DEFAULT NOW()

CREATE TABLE closures (
    id SERIAL PRIMARY KEY,
    venue_id INTEGER REFERENCES venues(id) ON DELETE CASCADE,
    closed_on DATE NOT NULL,
    risk_score NUMERIC CHECK (risk_score BETWEEN 0 AND 1)
);

A few of these are pretty self-explanatory — NOT NULL and DEFAULT don't need much unpacking. CHECK is worth pointing out specifically because it's underused. Most people know about primary and foreign keys, fewer people think to add something like CHECK (latitude BETWEEN -90 AND 90) even when the column obviously has a valid range. It's a small thing, but it means a typo that puts in 230 instead of 23.0 gets rejected immediately instead of silently breaking a map rendering somewhere downstream.

Deciding what happens when a parent row disappears

This is the part of foreign keys that actually requires some thought, rather than just copying REFERENCES table(column) and moving on.

venue_id INTEGER REFERENCES venues(id) ON DELETE CASCADE

CASCADE — deleting a venue deletes its closures too. Makes sense when the child data genuinely has no meaning without the parent. A closure record for a venue that no longer exists isn't useful information, it's just orphaned data.

RESTRICT (the default-ish behavior if you don't specify anything) — blocks deleting a venue if closures still reference it. This forces whoever's doing the deleting to think about what should happen to the related data first, instead of it disappearing silently.

SET NULL — keeps the closure row, but sets venue_id to null. Makes sense when the historical record matters even after the venue itself is gone — say, for reporting purposes, where you still want to count "there was a closure event" even if you can no longer say which venue it was.

None of these is universally correct. It depends on whether the child data is meaningless without the parent, needs explicit cleanup, or should survive on its own. I've seen people default to CASCADE everywhere because it's the path of least resistance, and then lose data they actually needed later because a parent row got deleted for an unrelated reason.

Where constraints get uncomfortable in practice

Constraints are great until you're trying to add one to a table that already has bad data in it. Adding NOT NULL to an existing column, or a CHECK constraint, fails outright if even one existing row violates it — Postgres won't just apply it going forward, it validates the whole table. On a small table that's a non-issue. On a large one, you sometimes need to clean up the offending rows first, or add the constraint as NOT VALID and validate it separately later, so you're not holding a lock on the table while every existing row gets checked.

The other thing that catches people off guard: constraints add real overhead on writes, not just reads. Every INSERT or UPDATE against a table with a foreign key means Postgres also has to check that the referenced row actually exists. It's usually not noticeable, but on a very high-throughput table, it's worth being aware that constraints aren't free — they're a trade, correctness for a small amount of write cost, and it's almost always worth it, but it's not nothing.

Designing the venues/closures relationship from scratch

Say you're setting this schema up for the first time, deciding what constraints actually belong on it, not just what the syntax looks like.

venues.id is the primary key — straightforward, every table needs a way to uniquely identify a row. venues.name gets NOT NULL, because a venue with no name isn't meaningful data, it's a placeholder that shouldn't exist. If venues need to be unique per city — no duplicate "DB Mall" entries within the same city — that's UNIQUE(name, city), which catches accidental duplicate entries from a sync job without blocking two venues with the same name in different cities.

closures.venue_id references venues.id. The decision that actually takes thought is the ON DELETE behavior. If venues in this system are only ever marked is_closed = TRUE rather than actually deleted — which is the more common pattern in practice, since you usually want the history — then this decision barely matters, because venues rarely get hard-deleted in the first place. But if hard deletes are a real possibility, RESTRICT is probably the safer default here, since it forces a conscious decision about what happens to the closure history instead of losing it silently through a CASCADE.

closures.risk_score, if it's meant to represent a probability, gets CHECK (risk_score BETWEEN 0 AND 1). This is exactly the kind of constraint that's easy to skip because "the application already validates this," and exactly the kind that saves you when some other code path — a script, a migration, an admin tool — doesn't.

Where this leaves you

Constraints move the responsibility for correctness out of application code, where it has to be remembered and re-implemented correctly everywhere data gets written, and into the database itself, where it's enforced no matter what's doing the writing. The ON DELETE decision in particular is worth actually thinking through per relationship instead of defaulting to whatever's fastest to type.

A good exercise, and one that comes up constantly in interviews, is being asked to design a small schema with two or three tables and their constraints out loud. It's worth practicing that out loud, not just reading through examples like this one, because explaining why you chose CASCADE over RESTRICT is a different skill than recognizing the syntax.

Next up: filtering, sorting, and pattern matching — the WHERE clause in more depth, and a few things that are genuinely Postgres-specific.