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.