SQL Domains: Define Once, Reuse Everywhere

One of the more architecturally significant features in Oracle 23ai is SQL Domains. A domain is essentially a reusable column specification — a named type that bundles a data type, constraints, default values, display formatting, and ordering rules. Define it once, apply it to as many columns as you want across your schema.

Creating a domain:

CREATE DOMAIN email_address AS VARCHAR2(255)
    CONSTRAINT email_fmt CHECK (REGEXP_LIKE(VALUE, '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$'))
    DISPLAY UPPER(VALUE)
    ORDER LOWER(VALUE);

Using the domain in a table:

CREATE TABLE customers (
    customer_id   NUMBER,
    email         email_address,    -- domain applied here
    support_email email_address     -- same domain, second column
);

The constraint from the domain is automatically enforced on every column that uses it. If you later update the domain’s constraint, the change propagates.

More domain examples:

-- Positive monetary amounts
CREATE DOMAIN money_usd AS NUMBER(15,2)
    DEFAULT 0.00
    CONSTRAINT positive_amount CHECK (VALUE >= 0);

-- Status codes
CREATE DOMAIN order_status AS VARCHAR2(20)
    CONSTRAINT valid_status CHECK (VALUE IN ('PENDING','PROCESSING','SHIPPED','DELIVERED','CANCELLED'));

Querying domain metadata:

SELECT domain_name, data_type, char_length
FROM   user_domains;

Why domains matter

Without domains, every table that stores an email address has its own REGEXP constraint — or more likely, no constraint at all. Domains enforce consistency at the schema level. They’re the database’s way of saying “this concept means the same thing everywhere.”

This is a feature that benefits large teams and long-lived schemas the most. If you’re building from scratch in 2025, start using domains — your future self will thank you.