Types, Constraints & Database Objects

Boolean, Enum, Domain, and UUID Types

Specialized scalar types can communicate and enforce a domain, but portability, evolution, ordering, generation, and nullability determine whether a native type, reusable domain, lookup relation, or constraint is the durable choice.

Boolean is a two-value domain only when NULL is excluded.
Enums close a value set into the type definition.
A domain packages a base type with reusable constraints.
Lookup relations handle evolving reference data.
UUIDs decentralize generation but do not prove business identity.
Native syntax is not the logical model.

Integrity and portability trade-offs
Choice	Strength	Cost / portability
Boolean + NOT NULL	Clear binary fact	Some engines expose integer-like syntax
Native enum	Compact closed vocabulary	Evolution and ordering are vendor-specific
Domain	Reusable semantic constraint	Not uniformly supported
Lookup relation + FK	Metadata and controlled evolution	Join and lifecycle management
UUID	Distributed key generation	Larger indexes; generation/version choices

CREATE DOMAIN positive_quantity AS INTEGER CHECK (VALUE > 0);
CREATE TYPE fulfillment_state AS ENUM ('PENDING', 'PACKED', 'SHIPPED');
CREATE TABLE shipments (
  shipment_id UUID PRIMARY KEY,
  order_id BIGINT NOT NULL REFERENCES orders(order_id),
  quantity positive_quantity NOT NULL,
  state fulfillment_state NOT NULL,
  insured BOOLEAN NOT NULL DEFAULT FALSE
);