CHECK and EXCLUDE Constraints: Database Rules Beyond Foreign Keys

Foreign keys are the constraint most engineers think about when they think about database integrity. They are the most visible, the most discussed, and the one most likely to come up in an interview. They are also only one type of constraint that relational databases support, and the others are systematically underused. CHECK constraints encode arbitrary boolean invariants directly into the schema. NOT NULL constraints encode the simplest of all invariants. UNIQUE constraints handle the cases where two rows must not share a value. EXCLUDE constraints, available in PostgreSQL, handle the cases where uniqueness is conditional or interval-based and where the standard UNIQUE constraint cannot express the requirement.

This post covers the constraint types beyond foreign keys, the patterns where each earns its cost, the migration considerations, and the operational discipline that keeps them working. The patterns apply across the four products in our studio — DocuMint, CronPing, FlagBit, and WebhookVault — and are general enough to apply to any system using PostgreSQL or, with some adjustments, any standards-compliant relational database.

NOT NULL is the most underrated constraint

The simplest constraint is the one most often skipped. Every column that should always have a value should be declared NOT NULL. The reasons are mechanical and operational. Mechanical: nullable columns require every query, every join condition, and every aggregation to consider what the NULL case means, which is almost never what the engineer wants and almost never the same across different queries. Operational: nullable columns drift toward being NULL more often than the design intended, because every code path that creates a row has the option of leaving the column unset.

The pattern that holds up is to declare every column NOT NULL by default, and explicitly allow NULL only where the absence of a value carries meaning distinct from the presence of the value. A user's email should be NOT NULL because every user has an email; a user's deleted_at column for soft deletes should be nullable because the absence of a value is meaningful (the user is not deleted). The discipline pays for itself within months, because every query that operates on a NOT NULL column avoids the trap of NULL-handling logic.

CHECK constraints encode the rules

CHECK constraints are arbitrary boolean expressions that every row must satisfy. The classic examples are domain restrictions: a price column should be non-negative; a percentage column should be between 0 and 100; a status column should be one of a small set of allowed values; a start_date should be less than or equal to end_date when both are present.

The case for CHECK constraints is that the rule lives in the schema, not in the application code, not in the ORM, not in the migration script. The rule is enforced by the database regardless of which application writes the row, which user runs the query, or whether the application has a bug. The case against is that CHECK constraints make schema changes slightly more complex — adding a CHECK constraint to an existing table requires a scan to verify all existing rows pass the check, and PostgreSQL handles this gracefully through the NOT VALID followed by VALIDATE CONSTRAINT pattern that lets the scan happen later under a weaker lock.

Where CHECK constraints frequently get added in retrospect: anywhere the application has a comment explaining a non-obvious rule about the column. A comment that says "must be one of three values" should be a CHECK constraint. A comment that says "must be in the future" should be a CHECK constraint. A comment that says "must be greater than the column above" should be a CHECK constraint. The pattern of moving rules from comments to constraints is one of the highest-leverage refactorings available in a relational schema.

EXCLUDE constraints for conditional uniqueness

The standard UNIQUE constraint says that no two rows may share a value in a column or set of columns. EXCLUDE constraints, a PostgreSQL feature, generalize this to arbitrary operators. The classic case is interval overlap: a booking system needs to ensure no two bookings overlap for the same room. The standard UNIQUE constraint cannot express this — there is no shared value, just an overlap relationship. The EXCLUDE constraint can: EXCLUDE USING gist (room_id WITH =, during WITH &&) declares that no two rows may share the same room_id AND have overlapping intervals in the during column.

The mechanics use a GiST index to check the constraint efficiently. The cost is comparable to a regular index plus a small overhead for the constraint check on writes. The benefit is that the rule is enforced unconditionally, which application-layer checks cannot do under concurrent writes — two transactions can both check that no overlap exists, both decide to insert, and both succeed, leaving the database in a state that violates the rule. The EXCLUDE constraint prevents this at the database layer.

EXCLUDE is also useful for conditional uniqueness: a users table where the email column should be unique among non-deleted users but where deleted users may have duplicate emails. The constraint EXCLUDE (email WITH =) WHERE (deleted_at IS NULL) expresses this directly. The standard UNIQUE constraint cannot.

The migration story

Adding any of these constraints to an existing table requires that all existing rows satisfy the constraint, which means a scan. PostgreSQL handles this gracefully:

• NOT NULL on an existing column: in PostgreSQL 12+, you can add a NOT NULL constraint as part of an existing CHECK constraint marked NOT VALID, then VALIDATE CONSTRAINT, then formally promote it. Or you can backfill the column to remove NULLs and then add the NOT NULL directly.
• CHECK constraints: ADD CONSTRAINT ... NOT VALID skips the scan; VALIDATE CONSTRAINT later runs the scan under a weaker lock that does not block writes.
• EXCLUDE constraints: more complex because they require a GiST index. The pattern is to create the index CONCURRENTLY first, then add the constraint using that index.

The discipline that holds up is to add constraints incrementally as part of regular schema migrations, with NOT VALID where supported, and to write the validation as a separate migration that can be deferred until the system can tolerate the scan. The discipline does not hold up if the team treats schema migrations as a special event that happens rarely; the constraints accumulate as TODOs that never get done.

UNIQUE constraints and partial indexes

UNIQUE constraints have a subtlety that deserves explicit mention. A UNIQUE constraint creates an index, and the index is treated as a regular index for query planning. This means that adding a UNIQUE constraint adds a free index on the constrained columns, which is often what you want anyway. It also means that removing the constraint removes the index, which can have unexpected query-planning consequences if the index was load-bearing for other queries.

Partial UNIQUE indexes, created via CREATE UNIQUE INDEX ... WHERE, are the equivalent of conditional UNIQUE constraints. They are not formally constraints in the PostgreSQL sense, but they enforce the same rule. The two-deleted-emails case can be handled with CREATE UNIQUE INDEX users_active_email ON users (email) WHERE deleted_at IS NULL, which is simpler than the EXCLUDE approach and has comparable performance.

The operational discipline

Three habits hold up across years of production use. First, every constraint should be named, with a name that includes the table and the rule it enforces, so that constraint violation messages are self-explanatory. The default auto-generated names are unhelpful. Second, the schema should be the source of truth for which constraints exist; constraints should not be added at runtime by application code or by trigger logic. Third, the test suite should include integration tests that confirm constraints are doing what they should, by attempting to insert violating rows and confirming the database rejects them.

The deeper observation

The argument for using the constraint types beyond foreign keys is the same as the argument for foreign keys themselves: the rules are part of the schema, the schema is the source of truth, and rules in the schema do not drift over time the way rules in code do. The cost is small — a slight slowdown on writes, a small amount of additional schema-migration complexity, and a slightly steeper learning curve for the team — and the benefit is that the data stays correct under conditions that application-layer logic cannot reliably handle.

The empirical evidence over decades is that schemas with rich constraints age better than schemas without them. The data stays cleaner, the bugs surface faster, and the recovery from any mistake is easier because the constraint either prevents the mistake or makes it visible immediately. The marginal cost of one more CHECK constraint is small. The accumulated benefit of having the rules in the schema rather than in the comments is large.