{"id":105700,"date":"2025-03-27T04:27:30","date_gmt":"2025-03-27T04:27:30","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=105700"},"modified":"2025-02-19T04:31:48","modified_gmt":"2025-02-19T04:31:48","slug":"the-create-domain-statement","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/theory-and-design\/the-create-domain-statement\/","title":{"rendered":"The CREATE DOMAIN Statement"},"content":{"rendered":"

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

Some definitions from other smart people<\/h2>\n

Chris Date defined a domain as \u201ca 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.<\/p>\n

Terry Halpern defined domains as the semantic glue that holds everything together. Bob Schmidt Defined it as \u201ca linguistic construct or system intended to describe a specific phenomenon in terms of some otherwise meaningless base unit.\u201d<\/p>\n

For example, if there though is that there is a domain called voltage which has a base unit called \u201cvolt\u201d that\u2019s 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\u2019ve discussed scales and types of measurements in a previous article<\/a>, It\u2019s 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\u00b0, or it can be between zero and 2\u03c0 radians.<\/p>\n

Domains are not attributes<\/h2>\n

I also don\u2019t 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.<\/p>\n

The advantage of a CREATE DOMAIN<\/code> 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.<\/p>\n

The power of a domain<\/h2>\n

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 <\/code>column that \u201corder_qty > 0<\/code>\u201d, right?<\/p>\n

Well, not quite. We needed \u201corder_qty >= 0<\/code>\u201d To handle the CASE<\/code> 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<\/code> expressions to handle items that really were refunds. But the nice part of having this put into a CREATE DOMAIN<\/code> statement was to make absolutely sure that was all done the same way, everywhere in the system.<\/p>\n

Furthermore, the rules could get fairly elaborate when you factor in coupons, promotional sales, and so forth.<\/p>\n

Creating a domain<\/h2>\n

The syntax is quite straightforward<\/p>\n

CREATE DOMAIN  [ AS ] domain_name <data type>\n[  NOT NULL ]\n[ DEFAULT <default value>]\n[ CHECK (<condition> )] ;<\/pre>\n
CREATE DOMAIN<\/code> is defined in the SQL\/2008 standard, but you can find CREATE DATATYPE<\/code> as legacy syntax in some SQL dialects. Do not use it.<\/p>\n
Editor note: This is typical because all the <\/em>CREATE DATATYPE<\/code> statement does is create an alias to a datatype, and a default <\/em>NULL<\/code> setting that can be overridden. All it tends to do is add work, and because it does not allow a <\/em>CHECK<\/code> expression, it offers no value for data integrity and hence is not worth the hassle if you want to change the datatype.<\/em><\/p>\n
Likewise, you will find an explicit NULL<\/code> 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\u2019s just leave it as a bad idea to have duplicate names in the same name space, even for different types of objects.<\/p>\n
Domains can have CHECK<\/code> conditions and DEFAULT<\/code> values, and you can indicate whether the data type permits NULL<\/code> 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.<\/p>\n
To drop the domain from the database, use the DROP DOMAIN<\/code> 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<\/code> statement.<\/p>\n
If you are going to use a domain, use it to its fullest<\/h2>\n
Most of the examples you will see in discussions of this statement are very simple short hands in a column declaration. However, it\u2019s quite possible to use the full power of a CHECK ()<\/code> clause. Given CASE<\/code> expressions, a creative programmer can write the equivalent of a function in the DDL for things like check digits, elaborate evaluations and whatever.<\/p>\n
As always though, only put completely immutable rules in a domain. There is no overriding the rules you have coded into a check constraint!<\/p>\n","protected":false},"excerpt":{"rendered":"
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 \u201ca pool of values from which……<\/p>\n","protected":false},"author":96214,"featured_media":105701,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[53,143539],"tags":[159072],"coauthors":[6781],"class_list":["post-105700","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-featured","category-theory-and-design","tag-database-theory"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/105700","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/96214"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=105700"}],"version-history":[{"count":1,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/105700\/revisions"}],"predecessor-version":[{"id":105702,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/105700\/revisions\/105702"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media\/105701"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=105700"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=105700"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=105700"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=105700"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}