8.1 Domains

Domains are the classic way to specify user-defined types. Conceptually, a domain only allows an existing type to be used as a "carrier" for new types. In addition to a data type, a domain can optionally specify a default value, a collation, and a set of constraints to further restrict the values of that column. The SQL:1999 form of defining a domain is:

CREATE DOMAIN <domain-name> [AS] <data-type>
  [DEFAULT <expression>]
  [<constraint-definition>]
  [COLLATE <collation-name>]

Note, that the PostgreSQL syntax is equivalent to SQL:1999, execept for the COLLATE clause.

Domains do not allow the definition of structured data types. For example, domains do not allow you to define structured types like:

CREATE DOMAIN complex
(
  re FLOAT,
  im FLOAT
)

According to one of my major sources, domains were deprecated SQL:1999 and they may be removed from the SQL standard sometime in the future. Melton and Simon, the standard SQL:1999 editors, stated the following about domains:

We were once fans of SQL's domain capabilities. However, they have proved to be less useful than originally hoped, and future editions of the SQL standard may actually delete the facility entirely. For this reason, we do not spend much energy in this book explaining domains, nor do we recommend that you use them - even if you happen to use one of the few SQL products that have implemented them.

While this statement comes directly out of the hands of the SQL:1999 editors, it leaves no actual reasons as to why domains proved to be less useful than hoped.

As this is the original quote, it clearly states it may be removed from the standard, but that is not necessarily true. It occurrs to me, that no one really knows for sure. Another source states the opposite of Melton and Simon: However, the move towards user-defined data types and, in particular, classes, in SQL:1999 has meant that domains are not included in the most recent standard. Domains have not been deprecated explicitly, but there seems to be tacit agreement that no further development of domain support will occurr.

DOMAIN, which has formerly been a reserved keyword in SQL-92 and SQL:1999, was made a non-reserved keyword in SQL:2003 and SQL:2008. This means, domains were not officially deprecated, but domain support seems to have moved into the standard SQL non-core feature list.

It will likely take several years before domains will actually be removed from the SQL standard and it will take even more time before they will disappear from current DBMS implementations (on DBMSs that have them). Thus, there is no need to rush deleting domains from your databases and/or code. I still recommend using domains. Even though they were officially superseded by SQL:1999's object-relational data types, there are cases where even the object-relational syntax cannot provide an equivalent solution. Please refer to the section about pseudo booleans for examples.

Using domains is still a problem in practice, because only a few DBMSs implement them, e.g. PostgreSQL. MySQL, Oracle, SQL Server, and other DBMSs have never done so. Most DBMSs have in-built types that are said to provide everything a database designer needs. Some DBMSs have their own syntaxes for specifying user-defined types, e.g. Oracle and SQL Server. On DBMSs that do not have domains, you will either have to use the proprietary user-defined type syntax, fallback to non-reusable column definitions, or use the SQL:1999 object-relational types.

MySQL and Oracle do not have proprietary syntaxes for creating domain-like types. For MySQL, this is not that much of a problem, because it supports most of the standard SQL types, e.g. booleans. For Oracle however, this is a major letdown. Please refer to the section about booleans for one of the most frustrating issues in databases.

SQL Server supports specifying user-defined types with the following syntax:

CREATE TYPE [ schema_name. ] type_name
{ 
    FROM base_type 
    [ ( precision [ , scale ] ) ]
    [ NULL | NOT NULL ] 
  | EXTERNAL NAME assembly_name [ .class_name ] 
  | AS TABLE ( { <column_definition> | <computed_column_definition> }
        [ <table_constraint> ] [ ,...n ] )  
} [  ]

...

To my surprise, SQL Server user-defined types allow almost domain-like types by allowing all regular data types and the [ NULL | NOT NULL ] option. However, I miss the most important feature of user-defined types: check constraints. This way, SQL Server's user-defined types are only slightly "better" than the missing MySQL and the Oracle object-relational types.

Oracle is technologically behind with standardization and low-level requirements (mine, not those defined by the SQL standard), e.g. booleans and domain-like types. I do not know the reasons, but it looks to me as if Oracle does not care at all. As I mentioned before, I suspect they do not want to rewrite major parts of their products, given that this includes giving up treating empty strings and NULL as equal, booleans, etc. To an extent, it is comprehensible, but not satisfying.

References for Domains:

Last updated: 2010-08-04