23ai New Database Object: Application Usage Domains

To help application development with data consistency and data quality, a new database object type was introduced with 23c and 23ai: domains. Domains can be referred to as SQL Domains or Application Usage Domains, Use Case Domains, all of these are correct.

Domains are promoting a Single Point of Definition, a new terminology I would say, which basically means: define it once and use it multiple times. Domains are also associated and used by table columns.
So with that in mind, you define the column properties and constraints once, and you use it multiple time in your application, this results in consistency throughout the database and application.

To prove the point, here is a very basic example:
Think of hr.employees table, that has the dept_id column as number(4) data type. In the departments table dept_id column has number(5) data type. The 2 definitions of the column don’t match, as a result we have inconsistency in the application and database. Ideally we want consistency: all dept_id columns have the same properties and constraints for the same application.

With the help of domain objects you can define a column one time in the database, and use this definition in all the tables where that column is used or needed. Some of the things you can define for domains are:

• properties
• constraints
• annotations
• how to display it

Let’s dive in a bit more into the new world of domains.

What are Domains?

A domain is high level dictionary objects, that belongs to a schema and contains a set of properties and constraints, that can be applied to columns of tables. The domain must specify at least a built-in data type as a property and the domain can have only one data type. The domains can enforce certain rules, like postal code has 6 characters, or email address has to have an @ sign. You can use domains instead of data types, or use it together with defined data types.

Let’s look at a very simple example, with the minimum requirements:

create domain dept_id_domain as number(4);

Once you defined the domain, the next steps is to use it for table columns: associate the column with the domain. This way all the properties and constraints of the domain will be applied to the column.

create table d1 (dept_id dept_id_domain);
or
create table d2 (dept_id domain dept_id_domain);

The dept_id column now will have all the properties of the domain dept_id_domain.

Let’s create a more complex domain, this way you can see the benefits easier:

create domain dept_id_complex_dom as varchar2(4)
constraint dept_id_chk check (dept_id_complex_dom is not null)
display lower(dept_id_complex_dom)
annotations (Description 'Domain Department Ids');

There are 4 types of domains:

• single column domain
• multi column domain
• flexible domain
• enumeration domain

Will look at these more closely in the next post!

If you enjoyed this article, and would like to learn more about databases, please sign up to my weekly email, and you will receive my FREE guide: 7 Questions to Ask When Troubleshooting Database Performance Problems!

If you are interested in improving your Oracle Tuning skills, check out my course theultimatesqltuningformula.com. Follow the link to get Today‘s Special, only $12.99 CAD !