CHECK & UNIQUE Constraints

Enforce rules close to data. Practical PostgreSQL patterns for production queries.

Recipe

ALTER TABLE app.accounts ADD CONSTRAINT accounts_name_len CHECK (char_length(name) <= 200);
CREATE UNIQUE INDEX accounts_name_active_unique ON app.accounts (name) WHERE deleted_at IS NULL;

When to reach for this: This pattern appears in application or reporting SQL you maintain.

Working Example

INSERT INTO app.accounts (name) VALUES ('Acme'); -- second 'Acme' fails while active

What this demonstrates:

Runnable SQL against sample schemas
Idiomatic PostgreSQL syntax
Patterns safe for EXPLAIN review

Deep Dive

How It Works

PostgreSQL parses SQL into a query tree.
The planner estimates costs using statistics.
Executor returns rows to the client protocol.

Notes

Prefer readable SQL; optimizers rewrite internally.
Test with production-like row counts.

Gotchas

SELECT * in apps - New columns break serializers.. Fix: List columns explicitly.
NULL comparisons - = NULL is always unknown.. Fix: Use IS NULL or IS DISTINCT FROM.
Implicit casts - Surprise seq scans on mismatched types.. Fix: Match predicate types to column types.
Unbounded queries - Missing LIMIT on large tables.. Fix: Paginate or aggregate intentionally.
Stale statistics - Bad plans after bulk load.. Fix: Run ANALYZE on affected tables.

Alternatives

Alternative	Use When	Don't Use When
ORM query builder	Team standardizes on one stack	Complex SQL becomes opaque
Materialized view	Repeat expensive aggregates	Needs refresh strategy
Warehouse replica	Heavy BI scans	Not for OLTP latency

FAQs

Should I format SQL in application strings?

Use multiline template strings or SQL files; lint in CI.

When is DISTINCT enough?

When you need unique rows without aggregates.

Does ORDER BY hurt indexes?

Sort can use index order if query matches index columns.

Schema Design Basics - naming and keys

Current Version Documentation - Full manual for the latest stable release.
PostgreSQL Tutorial (Official) - Official hands-on introduction to PostgreSQL and SQL.
Stack Overflow – PostgreSQL Tag - Q&A for practical PostgreSQL problems.
Mastering PostgreSQL 17 (6th Edition) - Hans-Jürgen Schönig.
Practical SQL, 2nd Edition - Anthony DeBarros.

Stack versions: This page was written for PostgreSQL 18.4 (stable 18, maintenance 17), pgvector 0.8+, PgBouncer 1.x, Patroni 3.x, and PostGIS 3.5+.

Reviewed by Chris St. John·Last updated Jul 18, 2026

Was this article helpful?

Sql Schema Design Constraints Check And Unique Constraints

CHECK & UNIQUE Constraints

Enforce rules close to data. Practical PostgreSQL patterns for production queries.

Recipe

ALTER TABLE app.accounts ADD CONSTRAINT accounts_name_len CHECK (char_length(name) <= 200);
CREATE UNIQUE INDEX accounts_name_active_unique ON app.accounts (name) WHERE deleted_at IS NULL;

When to reach for this: This pattern appears in application or reporting SQL you maintain.

Working Example

INSERT INTO app.accounts (name) VALUES ('Acme'); -- second 'Acme' fails while active

What this demonstrates:

Runnable SQL against sample schemas
Idiomatic PostgreSQL syntax
Patterns safe for EXPLAIN review

Deep Dive

How It Works

PostgreSQL parses SQL into a query tree.
The planner estimates costs using statistics.
Executor returns rows to the client protocol.

Notes

Prefer readable SQL; optimizers rewrite internally.
Test with production-like row counts.

Gotchas

SELECT * in apps - New columns break serializers.. Fix: List columns explicitly.
NULL comparisons - = NULL is always unknown.. Fix: Use IS NULL or IS DISTINCT FROM.
Implicit casts - Surprise seq scans on mismatched types.. Fix: Match predicate types to column types.
Unbounded queries - Missing LIMIT on large tables.. Fix: Paginate or aggregate intentionally.
Stale statistics - Bad plans after bulk load.. Fix: Run ANALYZE on affected tables.

Alternatives

Alternative	Use When	Don't Use When
ORM query builder	Team standardizes on one stack	Complex SQL becomes opaque
Materialized view	Repeat expensive aggregates	Needs refresh strategy
Warehouse replica	Heavy BI scans	Not for OLTP latency

FAQs

Should I format SQL in application strings?

Use multiline template strings or SQL files; lint in CI.

When is DISTINCT enough?

When you need unique rows without aggregates.

Does ORDER BY hurt indexes?

Sort can use index order if query matches index columns.

Schema Design Basics - naming and keys

Current Version Documentation - Full manual for the latest stable release.
PostgreSQL Tutorial (Official) - Official hands-on introduction to PostgreSQL and SQL.
Stack Overflow – PostgreSQL Tag - Q&A for practical PostgreSQL problems.
Mastering PostgreSQL 17 (6th Edition) - Hans-Jürgen Schönig.
Practical SQL, 2nd Edition - Anthony DeBarros.

Stack versions: This page was written for PostgreSQL 18.4 (stable 18, maintenance 17), pgvector 0.8+, PgBouncer 1.x, Patroni 3.x, and PostGIS 3.5+.

Reviewed by Chris St. John·Last updated Jul 18, 2026

Was this article helpful?

CHECK & UNIQUE Constraints

Search Documentation

CHECK & UNIQUE Constraints