A recommendation for ENUMERATION DOMAINS

June 5, 2024

5–7 minutes

Use case domains is one the very cool features in Oracle Database 23ai. When this release was still in the Beta phase, I did a video covering the basics which you can see below.

But now that we are at production, one enhancement that has come along for the ride is the concept of enumeration domains.

As per the Wiki for enumeration, “An enumeration is a complete, ordered listing of all the items in a collection”.

Taking an example from the docs, you can see how we define such a domain

and how we can then reference the particular enum values in our SQL statements.

Now before you start thinking “Hey, this post is just a cut/paste of the docs“, let me switch to an example which will then lead to the motivation for this blog post, namely a recommendation for all enumeration domains that you create.

Let’s say I’m running a small corner store and I’m going to have a database table to handle sales by employee. One of the columns will be the day of the week the sale was made. (Yes, I know this could be just a DATE column, but bear with me, I’m trying to keep this super simple to demonstrate a point).

As the shop owner, I know that currently I only open the store on weekdays, so I create a domain to handle this.


SQL> create domain open_day as enum (
  2    monday,
  3    tuesday,
  4    wednesday,
  5    thursday,
  6    friday
  7  );

Domain created.

Now I create my CORNER_STORE table with the DOW (day of week) column being bound to this domain.


SQL> create table corner_store (
  2    product      varchar2(10),
  3    sales_person varchar2(10),
  4    dow          open_day
  5  );

Table created.

I can test that I can use the enumeration domain values in an INSERT, and you can see the resultant domain values that are produced with a SELECT.


SQL> insert into corner_store
  2  values ('Milk','Joe', open_day.monday);

1 row created.

SQL> insert into corner_store
  2  values ('Bread','Sue', open_day.wednesday);

1 row created.

SQL> select product, sales_person, domain_display(dow)
  2  from corner_store;

PRODUCT    SALES_PERS DOMAIN_DI
---------- ---------- ---------
Milk       Joe        MONDAY
Bread      Sue        WEDNESDAY

So far, all is well. A few months later, due to the overwhelming success of selling Bread and Milk during the week :-), I’ve decided to now open my store on Sundays as well! To do that I need to recreate my domain to handle the extra day, but I’ll use FORCE PRESERVE so that my table is still obeying the rules of the outgoing definition.


SQL> drop domain open_day force preserve;

Domain dropped.

SQL> create domain open_day as enum (
  2    sunday,
  3    monday,
  4    tuesday,
  5    wednesday,
  6    thursday,
  7    friday
  8  );

Domain created.

SQL> alter table corner_store
  2    modify ( dow domain open_day );

Table altered.

Everything seems to be fine, but take a look at what happens when I query my table. Remember I inserted two rows for MONDAY and WEDNESDAY


SQL> select product, sales_person, domain_display(dow)
  2  from corner_store;

PRODUCT    SALES_PERS DOMAIN_DI
---------- ---------- ---------
Milk       Joe        SUNDAY
Bread      Sue        TUESDAY

I’ve unknowingly just corrupted my entire table.

The key thing to note with enumeration domains is that (just like the Wiki page told us)

“An enumeration is a complete, ordered listing of all the items in a collection”.

When you create an enumeration domain, in the absence of anything in the DDL to the contrary, the values that you are physically storing in the table will be the sequential index entry for the enumeration you specified. Thus when I initially create the domain, the entries were


SQL> select * from open_day;

ENUM_NAME ENUM_VALUE
--------- ----------
MONDAY             1
TUESDAY            2
WEDNESDAY          3
THURSDAY           4
FRIDAY             5

and then when I dropped and recreated the domain, but put Sunday first (since intuitively that seems to be the most appropriate place)


SQL> select * from open_day;

ENUM_NAME ENUM_VALUE
--------- ----------
SUNDAY             1
MONDAY             2
TUESDAY            3
WEDNESDAY          4
THURSDAY           5
FRIDAY             6

you can see that I’ve shifted the mapping for the MONDAY through FRIDAY entries all by 1. The physical values in the table have not changed, but you’re still unlikely to have happy customers or auditors when all of a sudden the data no longer seems to match reality.

Hence my recommendation for any enumeration domain that you create, is that you always explicitly include the mapping values, even if you are simply following the defaults. For example, my OPEN_DAY domain would be


SQL> create domain open_day as enum (
  2    monday = 1,
  3    tuesday = 2,
  4    wednesday = 3,
  5    thursday = 4,
  6    friday = 5
  7  );

Domain created.

because then when I subsequently want to add a new element to the domain for Sunday, the definition is (hopefully) prodding me to pick a new value for Sunday.


SQL> drop domain open_day force preserve;

Domain dropped.

SQL>
SQL> create domain open_day as enum (
  2    sunday = 10,
  3    monday = 1,
  4    tuesday = 2,
  5    wednesday = 3,
  6    thursday = 4,
  7    friday = 5
  8  );

Domain created.

SQL>
SQL> alter table corner_store
  2    modify ( dow domain open_day );

Table altered.

Now when I query my CORNER_TABLE, no data “corruption” will occur.


SQL> select product, sales_person, domain_display(dow)
  2  from corner_store;

PRODUCT    SALES_PERS DOMAIN_DI
---------- ---------- ---------
Milk       Joe        MONDAY
Bread      Sue        WEDNESDAY

Footnote:

As per the docs, an enumeration domain something like a “shortcut” way to define a standard domain.

“An enumeration domain has a default check-constraint, a display-expression, and an order-expression”

Thus when I created my CORNER_TABLE, the enumeration domain manifested itself as a CHECK constraint on the table.


SQL> select *
  2  from user_constraints
  3  where table_name = 'CORNER_STORE'
  4  @pr
==============================
OWNER                         : ADMIN
CONSTRAINT_NAME               : SYS_C0019548
CONSTRAINT_TYPE               : C
TABLE_NAME                    : CORNER_STORE
SEARCH_CONDITION              : "DOW">=1 AND "DOW"<=5 AND "DOW"=ROUND("DOW")
SEARCH_CONDITION_VC           : "DOW">=1 AND "DOW"<=5 AND "DOW"=ROUND("DOW")
R_OWNER                       :
R_CONSTRAINT_NAME             :
DOMAIN_OWNER                  :
DOMAIN_NAME                   :
DOMAIN_CONSTRAINT_NAME        :
DELETE_RULE                   :
STATUS                        : ENABLED
DEFERRABLE                    : NOT DEFERRABLE
DEFERRED                      : IMMEDIATE
VALIDATED                     : VALIDATED
GENERATED                     : GENERATED NAME
BAD                           :
RELY                          :
PRECHECK                      : NOPRECHECK
LAST_CHANGE                   : 23-MAY-24
INDEX_OWNER                   :
INDEX_NAME                    :
INVALID                       :
VIEW_RELATED                  :
ORIGIN_CON_ID                 : 25

PL/SQL procedure successfully completed.

Note that when you drop the domain with FORCE PRESERVE and then re-added the re-created domain, the original CHECK constraints remain in place. Thus after adding SUNDAY into my domain, and then attaching that domain to the DOW column in CORNER_TABLE, and then dropping the domain and correcting the entry for SUNDAY=10, I now have two surplus CHECK constraints. It is up to you to drop the now obsolete constraint(s).


SQL> select *
  2      from user_constraints
  3      where table_name = 'CORNER_STORE'
  4  @pr
==============================
OWNER                         : ADMIN
CONSTRAINT_NAME               : SYS_C0019711
CONSTRAINT_TYPE               : C
TABLE_NAME                    : CORNER_STORE
SEARCH_CONDITION              : "DOW">=1 AND "DOW"<=5 AND "DOW"=ROUND("DOW")
SEARCH_CONDITION_VC           : "DOW">=1 AND "DOW"<=5 AND "DOW"=ROUND("DOW")
R_OWNER                       :
R_CONSTRAINT_NAME             :
DOMAIN_OWNER                  :
DOMAIN_NAME                   :
DOMAIN_CONSTRAINT_NAME        :
DELETE_RULE                   :
STATUS                        : ENABLED
DEFERRABLE                    : NOT DEFERRABLE
DEFERRED                      : IMMEDIATE
VALIDATED                     : VALIDATED
GENERATED                     : GENERATED NAME
BAD                           :
RELY                          :
PRECHECK                      : NOPRECHECK
LAST_CHANGE                   : 05-JUN-24
INDEX_OWNER                   :
INDEX_NAME                    :
INVALID                       :
VIEW_RELATED                  :
ORIGIN_CON_ID                 : 25
==============================
OWNER                         : ADMIN
CONSTRAINT_NAME               : SYS_C0019712
CONSTRAINT_TYPE               : C
TABLE_NAME                    : CORNER_STORE
SEARCH_CONDITION              : "DOW">=1 AND "DOW"<=6 AND "DOW"=ROUND("DOW")
SEARCH_CONDITION_VC           : "DOW">=1 AND "DOW"<=6 AND "DOW"=ROUND("DOW")
R_OWNER                       :
R_CONSTRAINT_NAME             :
DOMAIN_OWNER                  :
DOMAIN_NAME                   :
DOMAIN_CONSTRAINT_NAME        :
DELETE_RULE                   :
STATUS                        : ENABLED
DEFERRABLE                    : NOT DEFERRABLE
DEFERRED                      : IMMEDIATE
VALIDATED                     : VALIDATED
GENERATED                     : GENERATED NAME
BAD                           :
RELY                          :
PRECHECK                      : NOPRECHECK
LAST_CHANGE                   : 05-JUN-24
INDEX_OWNER                   :
INDEX_NAME                    :
INVALID                       :
VIEW_RELATED                  :
ORIGIN_CON_ID                 : 25
==============================
OWNER                         : ADMIN
CONSTRAINT_NAME               : SYS_C0019713
CONSTRAINT_TYPE               : C
TABLE_NAME                    : CORNER_STORE
SEARCH_CONDITION              : ("DOW"=10 OR "DOW"=1 OR "DOW"=2 OR "DOW"=3 OR "DOW"=4 OR "DOW"=5)
SEARCH_CONDITION_VC           : ("DOW"=10 OR "DOW"=1 OR "DOW"=2 OR "DOW"=3 OR "DOW"=4 OR "DOW"=5)
R_OWNER                       :
R_CONSTRAINT_NAME             :
DOMAIN_OWNER                  : ADMIN
DOMAIN_NAME                   : OPEN_DAY
DOMAIN_CONSTRAINT_NAME        : SYS_DOMAIN_C0055
DELETE_RULE                   :
STATUS                        : ENABLED
DEFERRABLE                    : NOT DEFERRABLE
DEFERRED                      : IMMEDIATE
VALIDATED                     : VALIDATED
GENERATED                     : GENERATED NAME
BAD                           :
RELY                          :
PRECHECK                      : PRECHECK
LAST_CHANGE                   : 05-JUN-24
INDEX_OWNER                   :
INDEX_NAME                    :
INVALID                       :
VIEW_RELATED                  :
ORIGIN_CON_ID                 : 25

PL/SQL procedure successfully completed.

5 responses to “A recommendation for ENUMERATION DOMAINS”

JAYT22

June 5, 2024 at 4:14 pm

Why do you specify:

dow open_day

For the CREATE TABLE but:

dow domain open_day

For the MODIFY?

I suppose I could look it up to check if it’s just a typo. You also have a missing “of” I think: Use case domains is one the very cool features.

Interesting feature highlight though, thanks.

Reply
1. Connor McDonald
  
  June 7, 2024 at 6:23 pm
  
  It does seem to be an inconsistency but that is the way it is
  
  Reply
Ajaykumar Gupta

June 5, 2024 at 4:22 pm

Is the second check constraint output for the addition of just “sunday” in your domain or for addition of “sunday=10”? Cause thecheck constraint output doesn’t has anything related to 10.

Reply
1. Connor McDonald
  
  June 5, 2024 at 5:37 pm
  
  I trimmed too much of the output to omit the third row of output. That has been fixed.
  
  Reply
iudithd5bf8e4d8d

June 5, 2024 at 6:53 pm

Hi Connor,
It would be nice if an ALTER DOMAIN … would allow to ADD new value(s) to an existing domain, with the condition that they “do not conflict” with the existing values, and then, automatically propagate the constraint change to all the relevant table(s), or, maybe just allowing this by using
an optional CASCADE clause.

DROP-ing a value would also be nice, but, of course, it should succeed only if the dropped value is not effectively used in any table.
( same kind of check as when deleting a PK value from a table referenced by one or more FK-s).

It looks to me that such functionality will probably arrive soon 🙂

Cheers & Best Regards,
Iudith

Reply