Unique constraints

What it is

Unique constraints prevent duplicate keys at the database boundary.

When to use it

Use for business references and actor-scoped idempotency keys.

Syntax

CREATE UNIQUE INDEX tx_request_unique ON transactions (account_id, idempotency_key);

Example

CREATE UNIQUE INDEX tx_request_unique
ON transactions (account_id, idempotency_key);
-- Make idempotency_key NOT NULL when a key is mandatory.

Common mistakes

Many engines permit multiple NULL values; validation alone cannot prevent concurrent duplicates.

Related topics