Types, Constraints & Database Objects

Sequences, Generated Columns, Triggers, and Routines

Database-generated values and executable objects centralize useful invariants, but identity allocation, derived-value timing, side effects, privileges, recursion, and concurrency must remain explicit and observable.

Identity and sequences allocate surrogate values safely under concurrency.
Generated columns derive a row value from an expression.
Triggers react implicitly to data events.
Routines expose an explicit database operation.
Triggers do not serialize arbitrary read-then-write logic.
Hidden work requires operational controls.

Choosing a database object
Mechanism	Best fit	Main caution
Identity / sequence	Concurrent surrogate allocation	Gaps; not business chronology
Generated column	Deterministic same-row expression	Vendor expression/storage limits
Constraint	Declarative invariant	Only supported predicate scope
Trigger	Unavoidable event reaction/audit	Implicit multi-row and concurrency behavior
Routine	Explicit set-based operation or privilege API	Vendor coupling and transaction semantics

CREATE TABLE inventory (
  product_id BIGINT PRIMARY KEY REFERENCES products(product_id),
  on_hand INTEGER NOT NULL CHECK (on_hand >= 0),
  reserved INTEGER NOT NULL DEFAULT 0 CHECK (reserved >= 0),
  available INTEGER GENERATED ALWAYS AS (on_hand - reserved) STORED,
  CHECK (reserved <= on_hand)
);

-- One atomic statement: competing writers cannot both reserve the same units.
UPDATE inventory SET reserved = reserved + :quantity
WHERE product_id = :product_id
  AND on_hand - reserved >= :quantity
RETURNING product_id, available;

Isolated allocator demonstration: retrieve the generated key

CREATE TABLE generated_key_allocator_demo (
  allocation_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  payload TEXT NOT NULL
);

INSERT INTO generated_key_allocator_demo (payload)
VALUES ($1)
RETURNING allocation_id;

This is an isolated allocator demonstration: it defines a separate table instead of changing how the canonical orders table allocates identifiers. The mechanisms are not interchangeable APIs. PostgreSQL and SQLite RETURNING and SQL Server OUTPUT produce rows; Oracle writes the value into an output bind; MySQL clients normally use their driver’s generated-keys API, whose connection and multirow semantics must be checked. LAST_INSERT_ID() is connection-specific and must run on the same physical connection; for a multirow insert it reports the first generated value. Never use SELECT MAX(allocation_id), because another session can insert concurrently.

The added inventory object is defined before use. Its checks protect row-local bounds, while the conditional update makes availability testing and reservation one write. A trigger that first selects availability and later updates would still require locking or serialization and must handle a statement affecting many rows.