SQL Domains in Practice: Real-World Patterns

We introduced SQL Domains in February. Let’s look at how they work in realistic enterprise scenarios — because the design decisions around domain adoption matter as much as the feature itself.

Building a domain library:

Think of SQL Domains as a shared type system for your database. The first step is identifying recurring patterns:

-- Financial amount
CREATE DOMAIN d_amount_usd AS NUMBER(15,4)
    DEFAULT 0
    CONSTRAINT ck_amount_positive CHECK (VALUE >= 0);

-- Percentage (0-100)
CREATE DOMAIN d_percentage AS NUMBER(5,2)
    CONSTRAINT ck_pct_range CHECK (VALUE BETWEEN 0 AND 100);

-- Status fields
CREATE DOMAIN d_active_flag AS CHAR(1)
    DEFAULT 'Y'
    CONSTRAINT ck_active_flag CHECK (VALUE IN ('Y', 'N'));

-- Email
CREATE DOMAIN d_email AS VARCHAR2(254)
    CONSTRAINT ck_email_fmt CHECK (VALUE LIKE '%@%.%');

-- ISO country code
CREATE DOMAIN d_country_code AS CHAR(2)
    CONSTRAINT ck_country_upper CHECK (VALUE = UPPER(VALUE));

Using domains across tables:

CREATE TABLE invoices (
    invoice_id      NUMBER PRIMARY KEY,
    amount          d_amount_usd,
    tax_rate        d_percentage,
    customer_email  d_email,
    ship_country    d_country_code,
    is_paid         d_active_flag
);

CREATE TABLE purchase_orders (
    po_id           NUMBER PRIMARY KEY,
    total_value     d_amount_usd,     -- same domain reused
    billing_email   d_email,          -- same domain reused
    origin_country  d_country_code    -- same domain reused
);

Viewing domain usage across the schema:

SELECT table_name, column_name, domain_name
FROM   user_tab_columns
WHERE  domain_name IS NOT NULL
ORDER BY domain_name, table_name;

Governance tip: Store domain definitions in a versioned migration script, just like table DDL. Treat them as first-class schema objects that need change management. When a domain constraint needs updating, the change propagates automatically to all columns using it.

Author: matheusdba

Discover more from grepOra