Jan Mikoláš
Glossary
Database constraint
Database rules protect data beyond a single request path. Application validation does not replace them, and a constraint does not replace the entire business process.
Short definition
The last shared boundary protecting the integrity of stored data.
A constraint protects a rule that must hold on every write. It can require a currency, enforce a unique marketplace and external ID pair, ensure a non-negative price, or require an existing order for an order item. The database therefore evaluates the rule during imports, CLI operations, and concurrent requests as well.
Application validation has a different role: it can show the user an error before the write and work with the context of the form. By itself, however, it cannot safely protect shared state against every write path and concurrency race.
The problem it solves
Rules that must hold regardless of the input channel
Constraints allow the database to reject a state that is inherently invalid.
- NOT NULL for a required price, currency, or customer
- UNIQUE for an external ID that is unique within a marketplace
- CHECK for a non-negative amount and valid quantity range
- PRIMARY KEY for the row’s main identity
- FOREIGN KEY for an item referencing an existing order
- an idempotent import protected by a unique combination of values
SQL example
PostgreSQL rules for a marketplace order
CHECK on the amount does not replace NOT NULL: comparing an unknown NULL value alone would not establish a valid price.
CREATE TABLE marketplace_orders (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
marketplace text NOT NULL,
external_order_id text NOT NULL,
currency char(3) NOT NULL,
total_amount numeric(12, 2) NOT NULL,
customer_id bigint NOT NULL,
CONSTRAINT marketplace_orders_external_id_key
UNIQUE (marketplace, external_order_id),
CONSTRAINT marketplace_orders_total_amount_check
CHECK (total_amount >= 0),
CONSTRAINT marketplace_orders_customer_id_fkey
FOREIGN KEY (customer_id) REFERENCES customers (id)
);How it works
From an invariant to rejecting an invalid write
Text alternative: a form can catch the error in advance, but the database evaluates the final rule when the data is written.
- Defining the invariant A stable rule is identified, such as the uniqueness of an external order within a marketplace.
- Choosing the mechanism NOT NULL, UNIQUE, CHECK, PRIMARY KEY, and FOREIGN KEY express different kinds of restrictions.
- Application validation The form or API returns a clear error, but the system does not rely on it as the only safeguard.
- Writing in a transaction The database verifies the rule when inserting or modifying a row.
- Result A valid state is accepted; a constraint violation rejects the write, and the application translates the error into its own context.
Important concepts
Every rule needs a suitable mechanism.
Naming a constraint helps with migrations, diagnostics, and translating the error for the application.
NOT NULL and DEFAULT
NOT NULL prevents a missing value. DEFAULT only supplies a value when an INSERT omits it; it is not a constraint in itself.
UNIQUE and PRIMARY KEY
UNIQUE protects the uniqueness of a value or combination. PRIMARY KEY defines the main identity and does not permit NULL. PostgreSQL uses an index to enforce UNIQUE.
FOREIGN KEY
Protects a reference to a suitable row in another table. It is not the same as an ORM association or application authorization.
CHECK
Evaluates an expression over the row being written, such as total_amount >= 0. PostgreSQL CHECK is not intended to safely validate data in other rows or tables.
Constraint and transaction
The rule is evaluated as part of the write and transaction, protecting against some concurrency errors that validation performed in advance cannot resolve.
Benefits and limitations
Stronger data integrity does not replace application design.
Benefits
- protecting state during concurrency and across multiple write paths
- an invariant defined in one shared place
- more reliable idempotence and referential integrity
- earlier rejection of an inconsistent write
Common mistakes
- relying only on form validation
- using CHECK for a rule that depends on an external service
- displaying an unclear database error directly to the user
- an overly specific rule that blocks a future process
- assuming a constraint automatically handles permissions or every business condition
Practical example
Importing a marketplace order
Within a marketplace, an order has a unique external ID, a non-negative price, a required currency, and a required customer. The application service can look for a duplicate import first, but only a UNIQUE constraint protects against two workers trying to write the same message at the same time.
When a rule is violated, the application should recognise the type of error and return a meaningful result. The raw database error is unsuitable for customers and may expose the internal schema.
What to keep in mind
Name the rule first, then choose where to enforce it.
A database constraint should be stable, understandable, and verifiable against existing data.
- validate input for a good user experience and protect the final state with a constraint
- check existing data before adding the rule in a migration
- distinguish the uniqueness of business data from primary identity
- do not use CHECK for remote or rapidly changing state
- monitor constraint violations and translate them clearly
Common questions
Constraints in practice
Why isn’t API validation enough?
The same write can come from an import, CLI command, another service, or concurrent request. The database is the final shared boundary for integrity.
Is a UNIQUE constraint the same as a primary key?
No. A primary key defines the main identity and does not permit NULL; UNIQUE can protect an additional business identifier.
Does every business rule belong in a constraint?
No. A constraint suits stable rules on data stored in the database. A rule that depends on a remote service, time, or a complex process usually belongs in the application layer.
Is a default value a constraint?
No. DEFAULT supplies a value when data is inserted, but does not express or check a broader integrity rule by itself.
How I apply this principle in practice
I encode critical rules in the database model as well.
When designing e-commerce and integration systems, I combine application validation with constraints, transactions, and traceable data errors.