The Schema & Constraints Blueprint
A PostgreSQL schema is more than a list of tables and columns.
//
Search across all documentation pages
A PostgreSQL schema is more than a list of tables and columns.
It is a set of promises about what data is allowed to exist, and constraints are the mechanism that keeps those promises true even when application code has bugs.
This page builds the mental model behind schema design and constraints before you touch the how-to pages in this section on keys, generated columns, soft deletes, or DDL transactions.
Understanding this model matters because every later decision, from choosing a primary key strategy to picking a CHECK constraint over an application check, flows from the same underlying question: where should the source of truth for correctness live?
A schema in the design sense is the set of tables, columns, types, and relationships that model your business domain.
This is distinct from a PostgreSQL "schema" the namespace object, though the two share a name for historical reasons.
Designing a schema starts with normalization, the practice of splitting data into tables so that each fact is stored in exactly one place.
A normalized orders and order_lines split, for example, avoids repeating a customer's shipping address on every line item.
Normalization reduces update anomalies, where changing one fact requires touching many rows, but it also means more joins at query time.
Most production schemas land somewhere between fully normalized and deliberately denormalized, and that landing point is a design decision, not an accident.
Constraints are the rules that make a schema more than a suggestion.
A NOT NULL constraint says a value is always required, a CHECK constraint says a value must satisfy a boolean expression, and a UNIQUE constraint says no two rows may share a value.
A PRIMARY KEY combines NOT NULL and UNIQUE into a single column or column set that identifies a row.
A FOREIGN KEY ties a column in one table to a key in another, so that a reference can never point at a row that does not exist.
Think of a schema without constraints as a filing cabinet with no labels, anyone can put anything anywhere, and think of constraints as the labels that make retrieval and trust possible.
The simplest illustration is a single table with a primary key and a check constraint working together.
CREATE TABLE app.order_lines (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
order_id bigint NOT NULL,
qty int NOT NULL CHECK (qty > 0)
);This one statement encodes three separate promises: every row has a stable identity, every line belongs to an order, and no line can ever claim a non-positive quantity.
Constraints are not passive documentation, they are enforced on every write by the executor before a transaction is allowed to commit.
When you insert or update a row, PostgreSQL checks NOT NULL and CHECK constraints immediately as part of that statement.
UNIQUE and PRIMARY KEY constraints are backed by an index, so uniqueness is enforced by looking up the new value in that index before the row is allowed to land.
FOREIGN KEY constraints are checked by a system trigger that queries the referenced table, which is why an unindexed parent key can make foreign key checks slow.
By default, most constraints are checked at the end of each statement, but DEFERRABLE constraints can be pushed to the end of the transaction instead.
This deferral matters when you need to insert rows that reference each other, since a strictly immediate foreign key check would reject the first row before the second one exists.
DDL, the statements that create or alter these objects, has its own mechanics worth understanding early.
In PostgreSQL, most DDL statements are transactional, meaning a CREATE TABLE or ALTER TABLE inside a BEGIN/COMMIT block can be rolled back like any other change.
This is unusual compared to many other relational databases, and it is a genuine advantage when a multi-step migration needs to be all-or-nothing.
Adding a constraint to an existing table, however, is not free at the moment you add it.
ALTER TABLE ... ADD CONSTRAINT typically takes an ACCESS EXCLUSIVE lock while it validates every existing row against the new rule.
On a small table this validation is instantaneous, but on a large, busy table it can block reads and writes for the duration of the scan.
PostgreSQL offers a two-step pattern for this exact problem: add the constraint as NOT VALID first, which only blocks new writes, then run VALIDATE CONSTRAINT separately to check existing rows with a much lighter lock.
Generated and identity columns extend this same enforcement model into computed values.
An identity column guarantees a server-generated, gap-tolerant surrogate key without the application ever choosing a value, while a generated column derives its value from other columns and cannot be written directly.
Both remove an entire category of bugs where two code paths disagree about how to compute the same thing.
At scale, the interesting schema questions move from "what constraints do I need" to "how do I change them without an outage."
Adding a NOT NULL constraint to a large table used to require a full table rewrite in older PostgreSQL versions, but modern PostgreSQL can validate it as a fast check against an existing CHECK constraint instead, so backfilling and constraining are often best done as separate, sequenced steps.
Multi-tenant schemas raise a related question about where tenant isolation lives, whether as a tenant_id column with row-level security, a constraint-enforced foreign key, or fully separate schemas per tenant.
Exclusion constraints, built on the same infrastructure as unique indexes but generalized to any operator, solve problems unique constraints cannot, such as preventing overlapping date ranges for the same resource.
Domains offer another layer of reuse, letting you define a named type with a built-in CHECK constraint once and apply it to many columns consistently, though they are less flexible than table-level constraints when a rule needs to reference multiple columns.
The deepest architectural question is where validation logic should live at all, and that question has no universally correct answer, only trade-offs.
| Approach | Strength | Weakness | Best Fit |
|---|---|---|---|
| Database constraints (CHECK, UNIQUE, FK) | Enforced for every writer, including scripts and other services | Migration and validation cost on large tables | Invariants that must never be violated, regardless of caller |
| Application-layer validation | Fast to iterate, rich error messages, easy to test in isolation | Bypassed by direct SQL, batch jobs, or a second service | User-facing input shaping and business rules that change often |
| Database triggers | Can enforce cross-row and cross-table rules constraints cannot express | Harder to reason about, can hide logic from readers of the schema | Rare invariants too complex for a declarative constraint |
| ORM-level validation | Convenient, colocated with model code | Only as strong as the one code path that uses the ORM | A second line of defense layered on top of real constraints |
The pattern that holds up best in production is layering: application validation for user experience, database constraints for the invariant that must never break, and triggers reserved for the rare case neither can express cleanly.
Schema evolution also has an observability dimension, since a constraint violation in production is a signal, not just an error, and tracking which constraints reject the most rows over time often points at a mismatch between what the application assumes and what actually happens upstream.
CREATE SCHEMA app) is a namespace that groups tables, views, and functions.A CHECK constraint is enforced for every writer against that table, including scripts, migrations, and other services, while application validation only protects the one code path that runs it.
NOT VALID plus VALIDATE CONSTRAINT pattern minimizes blocking on large, busy tables.A PRIMARY KEY is a UNIQUE constraint combined with NOT NULL, and a table may have only one primary key but many separate unique constraints.
An exclusion constraint generalizes uniqueness to any operator, which makes it the right tool when you need to prevent overlapping ranges, such as two bookings for the same resource sharing any overlapping time window.
Most DDL statements, including CREATE TABLE and ALTER TABLE, participate fully in transactions and roll back cleanly if the surrounding transaction is aborted, which is a genuine advantage over databases where DDL auto-commits.
NOT VALID that mirrors the NOT NULL rule first.VALIDATE CONSTRAINT, which takes a much lighter lock.A domain packages a base type with a built-in constraint so it can be reused consistently across many columns and tables, while a table-level CHECK constraint applies only to that one table and can reference multiple columns at once.
Normalization is not primarily about storage cost, it is about avoiding update anomalies where the same fact stored in multiple places can drift out of sync.
Soft deletes typically rely on a nullable deleted_at column paired with a partial index or partial unique constraint, so that uniqueness rules only apply to the still-active rows.
Treating constraints as optional documentation rather than as the actual enforcement mechanism is the most common mistake, since it leaves correctness dependent on every future writer behaving perfectly.
Stack versions: This page was written for PostgreSQL 18.4 (stable major 18, maintenance line 17 also supported).
Reviewed by Chris St. John·Last updated Jul 15, 2026