SQL Domains in Oracle 23ai/26ai: Centralized Data Validation Done Right

What Are SQL Domains?

SQL Domains, introduced in Oracle 23ai and enhanced in 26ai, let you define reusable, named data type definitions — complete with constraints, display expressions, ordering logic, and annotations — as first-class schema objects. Instead of scattering identical CHECK constraints across dozens of tables, you define the rule once and reference it everywhere. Think of them as lightweight, centralized business rules that travel with the data type itself.

This solves a real problem: duplicated validation logic for email formats, phone numbers, currency codes, and status values leads to inconsistency, human error, and maintenance nightmares. SQL Domains shift that validation into the database layer where it belongs.

Standard Domains: Define Once, Apply Everywhere

A standard domain encapsulates a data type, constraint, display expression, and ordering logic into a single reusable object.

-- Define an email domain with validation and display logic
CREATE DOMAIN email_dom AS VARCHAR2(320)
  CONSTRAINT email_chk CHECK (
    REGEXP_LIKE(email_dom, '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$')
  )
  DISPLAY LOWER(email_dom)
  ANNOTATIONS (TITLE 'Email Address', DESCRIPTION 'RFC 5321 compliant email');

-- Apply it to columns in any table
CREATE TABLE customers (
  customer_id   NUMBER GENERATED ALWAYS AS IDENTITY,
  customer_name VARCHAR2(200) NOT NULL,
  primary_email VARCHAR2(320) DOMAIN email_dom,
  billing_email VARCHAR2(320) DOMAIN email_dom
);

-- This succeeds
INSERT INTO customers (customer_name, primary_email)
VALUES ('Ahmed Baraka', 'Ahmed@AhmedBaraka.com');

-- This fails with a clear error referencing email_dom.email_chk
INSERT INTO customers (customer_name, primary_email)
VALUES ('Test User', 'not-an-email');

Both email columns now share identical validation. The DISPLAY expression means calling DOMAIN_DISPLAY(primary_email) automatically returns the lowercased form — presentation logic lives in the database, not duplicated across every consuming application.

Flexible Domains: Discriminator-Based Validation

Flexible domains are where things get powerful. They let you apply different constraints to the same column based on another column’s value — eliminating complex trigger-based conditional logic.

-- Different validation rules based on identifier type
CREATE FLEXIBLE DOMAIN id_document_dom (id_value VARCHAR2(20), id_type VARCHAR2(10))
  CHOOSE DOMAIN USING (id_type)
  FROM
    CASE 'SSN'      THEN ssn_dom(id_value)
    CASE 'EIN'      THEN ein_dom(id_value)
    CASE 'PASSPORT' THEN passport_dom(id_value);

-- Supporting standard domains
CREATE DOMAIN ssn_dom AS VARCHAR2(20)
  CONSTRAINT ssn_chk CHECK (REGEXP_LIKE(ssn_dom, '^\d{3}-\d{2}-\d{4}$'));

CREATE DOMAIN ein_dom AS VARCHAR2(20)
  CONSTRAINT ein_chk CHECK (REGEXP_LIKE(ein_dom, '^\d{2}-\d{7}$'));

CREATE DOMAIN passport_dom AS VARCHAR2(20)
  CONSTRAINT passport_chk CHECK (REGEXP_LIKE(passport_dom, '^[A-Z]{1,2}\d{6,9}$'));

-- Apply the flexible domain
CREATE TABLE person_ids (
  person_id  NUMBER,
  id_type    VARCHAR2(10),
  id_value   VARCHAR2(20),
  DOMAIN id_document_dom(id_value, id_type)
);

-- Validates against ssn_dom
INSERT INTO person_ids VALUES (1, 'SSN', '123-45-6789');

-- Validates against passport_dom — fails if format is wrong
INSERT INTO person_ids VALUES (2, 'PASSPORT', 'AB1234567');

Without flexible domains, you’d need BEFORE INSERT triggers with conditional logic or unwieldy compound CHECK constraints. This is cleaner, faster, and self-documenting.

Querying Domain Metadata and Annotations

Domains are fully introspectable through data dictionary views, enabling automated documentation and governance tooling.

-- View all domains
SELECT domain_name, data_type, data_display FROM user_domains;

-- View domain constraints
SELECT domain_name, constraint_name, search_condition FROM user_domain_constraints;

-- View which columns use which domains
SELECT table_name, column_name, domain_name FROM user_domain_cols;

-- Query annotations for governance/UI metadata
SELECT object_name, annotation_name, annotation_value
FROM user_annotations_usage
WHERE object_type = 'DOMAIN';

Annotations make your schema self-documenting. UI frameworks can discover field labels and descriptions directly from the database — no external metadata catalog required.

Key Takeaways

• Define once, enforce everywhere. Standard domains eliminate redundant CHECK constraints and guarantee consistent validation across all tables.
• Flexible domains replace triggers. Discriminator-based validation handles conditional logic declaratively — cleaner and more performant than BEFORE INSERT triggers.
• Annotations enable self-documenting schemas. Queryable metadata powers documentation generation, UI discovery, and data governance without external tools.
• Better error messages. Constraint violations reference the domain name, making debugging across large schemas significantly easier.
• Full lifecycle management. ALTER DOMAIN and DROP DOMAIN let you evolve domains as requirements change — all dependent columns inherit the updates.
• Database-driven presentation. DOMAIN_DISPLAY() and DOMAIN_ORDER() push formatting and sorting logic into the database, reducing duplication across consuming applications.