One of the least known, least used features of SQL is the CREATE DOMAIN statement. It does not exist in SQL Server as of SQL Server 2022, but it has been a part of PostgreSQL since 7.3.

Some definitions from other smart people

Chris Date defined a domain as “a pool of values from which the actual values appearing in a column (relational table) may be drawn. This is really weaker than it needs to be. For example, this definition would let a list of possible integers greater than zero, which are used model the distance domain.

Terry Halpern defined domains as the semantic glue that holds everything together. Bob Schmidt Defined it as “a linguistic construct or system intended to describe a specific phenomenon in terms of some otherwise meaningless base unit.”

For example, if there though is that there is a domain called voltage which has a base unit called “volt” that’s otherwise meaningless. Yes, you can get a voltmeter you can watch the needle, you can be told what the IEEE specification for defining how much work a volt should do or shock you. I’ve discussed scales and types of measurements in a previous article, It’s worth mentioning that you should not confuse domain with the representation and symbols of the units being used. Some domains are limited, such as degrees that measure planar angles. An angle can be from 0 to 360°, or it can be between zero and 2π radians.

Domains are not attributes

I also don’t want to confuse a domain with an attribute. An attribute or column in a table is particular to that entity, while a domain is not so attached to particular implementation. It is a higher-level abstraction from which we draw attributes. To make this a bit clearer, consider height. It is pretty obvious that every employee in a database that modelled an enterprise will have a particular height. The particular implementation will depend on the choice of the scale of measurement; do you use centimeters or inches? However, height has to be attached to a particular entity and was abstracted from a global generalization, a domain.

The advantage of a CREATE DOMAIN statement is that it turns out what could have been done, by column, in the DDL. This guarantees that definition of the domain appears in only one place, only once in schema, and can be altered or validated with certainty.

The power of a domain

Many years ago I had to deal with the system that had a quantity column without any checks on it. The problem was that you could fill out an order with a negative quantity, generate a negative extension so the order became a refund. One employee discovered this and began placing bogus orders. All that would have been required to prevent this was a constraint on the order_qty column that “order_qty > 0”, right?

Well, not quite. We needed “order_qty >= 0” To handle the CASE with an item on an order with something that was not an inventory, such as coupons or flyers that got stuffed into the same shipment. Then we needed some CASE expressions to handle items that really were refunds. But the nice part of having this put into a CREATE DOMAIN statement was to make absolutely sure that was all done the same way, everywhere in the system.

Furthermore, the rules could get fairly elaborate when you factor in coupons, promotional sales, and so forth.

Creating a domain

The syntax is quite straightforward

CREATE DOMAIN is defined in the SQL/2008 standard, but you can find CREATE DATATYPE as legacy syntax in some SQL dialects. Do not use it.

Editor note: This is typical because all the CREATE DATATYPE statement does is create an alias to a datatype, and a default NULL setting that can be overridden. All it tends to do is add work, and because it does not allow a CHECK expression, it offers no value for data integrity and hence is not worth the hassle if you want to change the datatype.

Likewise, you will find an explicit NULL clause In some dialects, but this is the assumption in SQL. Domains are objects within the database. By definition, domain names can be the same as a table name; do not do that. It is probably worth a separate article to discuss the name spaces in SQL, but let’s just leave it as a bad idea to have duplicate names in the same name space, even for different types of objects.

Domains can have CHECK conditions and DEFAULT values, and you can indicate whether the data type permits NULL values or not. These conditions and values are inherited by any column defined on the domain. Any conditions or values explicitly specified in the column definition override those specified for the domain. This is actually pretty useful, since you may want to qualify a particular domain in a particular table. For example, given a domain of customers, we might want to qualify children in one table and senior citizens in another table based on their birth date and current age.

To drop the domain from the database, use the DROP DOMAIN statement. You must be either the owner of the domain, or have DBA authority, to drop a domain. This can be pretty dangerous, so use it sparingly. Likewise, be careful when you use the ALTER DOMAIN statement.

If you are going to use a domain, use it to its fullest

Most of the examples you will see in discussions of this statement are very simple short hands in a column declaration. However, it’s quite possible to use the full power of a CHECK () clause. Given CASE expressions, a creative programmer can write the equivalent of a function in the DDL for things like check digits, elaborate evaluations and whatever.

As always though, only put completely immutable rules in a domain. There is no overriding the rules you have coded into a check constraint!