SQL Domains

In the database we store values that belong to certain domains in the real world. We store email addresses, company addresses, home addresses etc. all as varchar2 values. The database has no idea that these values all represent different domains. Email addresses follow different rules than company or home addresses. But a company and a home address follow similar rules.

We can establish the rules by implementing check constraints on the column of the table. If you want to register multiple addresses or multiple email addresses, you have to implement a check constraint for every individual instance of the address. This goes against the Single Point Of Definition (SPOD) principle, but you had no choice, until Oracle Database 23c. Also, you cannot apply a check constraint on multiple columns (some exceptions apply).

Built-in Domains

Oracle Database 23c already comes with a couple of built-in domains. Note that these are oriented on the English speaking part of the world, especially the PHONE_NUMBER_D, SSN_D, DAY(_SHORT)_D, and MONTH(_SHORT)_D and they do NOT follow any NLS settings.

Single Column Domain

Let's create a domain to validate a Dutch zip code. The domain consists of the datatype of the column, in this case varchar2( 7 char ), a default value (not used here), and a check constraint, which can be deferrable (or not). You can define multiple check constraints on a domain.

create domain zipcode_d as varchar2(7 char)
constraint zipcode_c check (regexp_like(zipcode_d, '^[1-9][[:alnum:]]{3}[ ]{0,1}[[:alpha:]]{2}$'))
deferrable initially deferred
display upper(replace(zipcode_d, ' ', ''))
order lower(replace(zipcode_d, ' ', ''))
annotations (Description 'Domain for Dutch zip codes')

Multi Column Domain

You can create a domain that spans multiple columns, for example a currency value, including the currency code. In this case we only use the domain for the display and order functionality. You cannot define a Multi Column Domains as strict.

create domain currency_d as
(amount as number
, iso_currency_code as char(3 char))
display iso_currency_code || to_char(amount, '999,999,990.00')
order to_char(amount, '999,999,990.00') || iso_currency_code

Flexible Usage Domain

You can create a flexible usage domain referencing other, non-flexible, domains. Both single- and multi column domains can be used. If you would like to build a flexible domain for the zip code check you can do this in a procedural fashion. Build a domain for every different kind of zip code you want to check and then build a flexible usage domain that references these domains.

create flexible domain zipcode_d (zipcode)
choose domain using (country char(2))
from (case country
        when 'NL' then zipcode_nl_d(zipcode)
        when 'US' then zipcode_us_d(zipcode)
        when 'ES' then zipcode_es_d(zipcode)
      end)