This page is part of the book SQL-99 Complete, Really, by Peter Gulutzan & Trudy Pelzer. The authors have graciously allowed us to reproduce the contents of the book here. Because the book is about the SQL-99 standard, the contents of this and other pages in the book may not directly apply to MariaDB. Use the navigation bar to navigate the book.

The CREATE DOMAIN statement names a new Domain and defines the Domain's set of valid values. The required syntax for the CREATE DOMAIN statement is:

CREATE DOMAIN <Domain name> [ AS ] <data type>
     [ DEFAULT default value ]
     [ <Domain Constraint> list ]
     [ COLLATE <Collation name> ]

   <Domain constraint> list::=
   <Domain Constraint> [ <Domain Constraint>... ]

      <Domain constraint> ::=
      [ CONSTRAINT <Constraint name> ]
      Constraint_type
      [ <constraint attributes> ]

CREATE DOMAIN defines a new Domain: a named set of valid data values that can be used somewhat like a macro to replace the <data type> specification in subsequent <Column definition>s.

  • The <Domain name> identifies the Domain and the Schema to which it belongs. A <Domain name> that includes an explicit <Schema name> qualifier belongs to the Schema named. A <Domain name> that does not include an explicit <Schema name> qualifier belongs to the SQL-session default Schema. The <Domain name> must be unique (for all Domains and UDTs) within the Schema that owns it.

If CREATE DOMAIN is part of a CREATE SCHEMA statement, the <Domain name>, if explicitly qualified, must include the <Schema name> of the Schema being created; that is, it isn't possible to create a Domain belonging to a different Schema from within CREATE SCHEMA. For example, this SQL statement will not return an error because the <Domain name> will default to include the qualifying <Schema name>:

CREATE SCHEMA bob
  CREATE DOMAIN domain_1 AS SMALLINT;
-- creates a Domain called BOB.DOMAIN_1 in Schema BOB

The following SQL statement will not return an error either because the <Domain name> explicitly includes a qualifying <Schema name> that matches the name of the Schema being created:

CREATE SCHEMA bob
  CREATE DOMAIN bob.domain_1 AS SMALLINT;
-- creates a Domain called BOB.DOMAIN_1 in Schema BOB

But this SQL statement will return an error because the <Domain name> explicitly includes a qualifying <Schema name> that is different from the name of the Schema being created:

CREATE SCHEMA bob
  CREATE DOMAIN sam.domain_1 AS SMALLINT;
-- tries to create a Domain belonging to Schema SAM inside Schema BOB; illegal syntax

Privileges

If CREATE DOMAIN is executed as a stand-alone SQL statement, the current <AuthorizationID> must either be the owner of the Schema to which this new Domain belongs or the Schema's owner must be a Role that the current <AuthorizationID> may use. That is, only the owner of a Schema can create Domains for that Schema. In addition to creating a Domain, CREATE DOMAIN also causes the SQL special grantor, "_SYSTEM", to grant the USAGE Privilege on the new Domain to the Schema owner <AuthorizationID> (that is, the <AuthorizationID creating the Domain). This USAGE Privilege will be grantable if (a) the grantee also has a grantable REFERENCES Privilege for each Column named in the Domain definition and (b) the grantee also has a grantable USAGE Privilege for each Domain, Collation, Character set and Translation named in a <Domain Constraint> in the Domain definition.

<data type>

A Domain must be defined to accept a certain type of data. The Domain's <data type> specification constrains the values that can be accepted by the Domain. The <data type> specification includes length, precision and scale as applicable. Valid <data type>s are: INT, SMALLINT, NUMERIC(p,s), DECIMAL(p,s), FLOAT(p), REAL, DOUBLE PRECISION, BIT(l), BIT VARYING(l), BLOB(l), CHAR(l), NCHAR(l), VARCHAR(l), NCHAR VARYING(l), CLOB(l), NCLOB(l), DATE, TIME(p), TIME(p) WITH TIME ZONE, TIMESTAMP(p), TIMESTAMP(p) WITH TIME ZONE, INTERVAL <interval qualifier>, BOOLEAN, ARRAY, ROW, and REF.

The <keyword> AS in the <data type> clause is noise and can be omitted. For example, these two SQL statements are equivalent:

CREATE DOMAIN domain_1 AS CHAR(10);

CREATE DOMAIN domain_1 CHAR(10);

[Obscure Rule] If the <data type> of a Domain is CHAR, VARCHAR or CLOB, the Character set that the Domain's values must belong to is determined as follows:

  • If your CREATE DOMAIN statement includes a CHARACTER SET clause, the Domain's Character set is the Character set named. Your current <AuthorizationID> must have the USAGE Privilege on that Character set.
  • If your CREATE DOMAIN statement does not include a CHARACTER SET clause, the Domain's Character set is the Character set named in the DEFAULT CHARACTER SET clause of the CREATE SCHEMA statement that defines the Schema to which the Domain belongs.

For example, the effect of these two SQL statements:

CREATE SCHEMA bob AUTHORIZATION bob
  DEFAULT CHARACTER SET INFORMATION_SCHEMA.LATIN1;

CREATE DOMAIN domain_1 AS CHAR(10);

is to create a Domain in Schema BOB. The Domain's set of valid values are fixed length character strings, exactly 10 characters long, all of whose characters must be found in the INFORMATION_SCHEMA.LATIN1 Character set the Schema's default Character set. The effect of these two SQL statements:

CREATE SCHEMA bob AUTHORIZATION bob
  DEFAULT CHARACTER SET INFORMATION_SCHEMA.LATIN1;

CREATE DOMAIN domain_1 AS CHAR(10)
  CHARACTER SET INFORMATION_SCHEMA.SQL_CHARACTER);

is to create the same Domain with one difference: this time, its values must consist only of characters found in the INFORMATION_SCHEMA.SQL_CHARACTER Character set the explicit Character set specification in CREATE DOMAIN constrains the Domain's set of values. The Schema's default Character set does not.

[Obscure Rule] If the <data type> of a Domain is CHAR, VARCHAR, CLOB, NCHAR, NCHAR VARYING, or NCLOB, and your CREATE DOMAIN statement does not include a COLLATE clause, the Domain has a coercibility attribute of COERCIBLE but if your CREATE DOMAIN statement includes a COLLATE clause, the Domain has a coercibility attribute of IMPLICIT. In either case, the Domain's default Collation is determined as follows:

  • If your CREATE DOMAIN statement includes a COLLATE clause, the Domain's default Collation is the Collation named. Your current <AuthorizationID> must have the USAGE Privilege on that Collation.
  • If your CREATE DOMAIN statement does not include a COLLATE clause, the Domain's default Collation is the default Collation of the Domain's Character set.

[Obscure Rule] If the <data type> of a Domain is REF(UDT), your current <AuthorizationID> must have the USAGE Privilege on that UDT. If the <data type> of a Domain includes REF with a <scope clause>, your CREATE DOMAIN statement must also include a <reference scope check> clause, to indicate whether references are to be checked or not (don't add a <reference scope check> clause under any other circumstances). In this case, you may also add the optional <reference scope check action> clause, to indicate the action to be taken whenever a Column based on this Domain is the subject of a DELETE statement. If you omit the <reference scope check action> clause, it defaults to ON DELETE RESTRICT.

  • If a Domain is defined with REFERENCES ARE CHECKED and a <scope clause> naming one or more Tables is included in the CREATE DOMAIN statement, then there is an implied DEFERRABLE INITIALLY IMMEDIATE Constraint on the new Domain which checks that the values of every Column based on the Domain are also found in the system generated Column of each Table named in the <scope clause>. In this case, if the <reference scope check action> is SET NULL then, prior to deleting any rows from the Tables that own a Column based on this Domain, your DBMS will (a) execute a SET CONSTRAINT statement that sets the implied Constraint's constraint check time to DEFERRED, (b) DELETE the rows as required, (c) set the value of the system generated Column in each Table named in the <scope clause> to NULL, for each row that matched the deleted rows and (d) execute a SET CONSTRAINT statement that sets the implied Constraint's constraint check time to IMMEDIATE.

DEFAULT clause

The optional DEFAULT clause defines the Domain's default value: the value to insert whenever a Column based on this Domain is the target of an INSERT statement that doesn't include an explicit value for that Column. The DEFAULT options are: DEFAULT <literal>, DEFAULT CURRENT_DATE, DEFAULT CURRENT_TIME(p), DEFAULT CURRENT_TIMESTAMP(p), DEFAULT LOCALTIME(p), DEFAULT LOCALTIMESTAMP(p), DEFAULT USER, DEFAULT CURRENT_USER, DEFAULT SESSION_USER, DEFAULT SYSTEM_USER, DEFAULT CURRENT_PATH, DEFAULT ARRAY[], DEFAULT ARRAY??(??), and DEFAULT NULL see "<default clause>" in our chapter on Tables. For example, this SQL statement creates a Domain whose default value is the <character string literal> 'bobby':

CREATE DOMAIN domain_1 AS VARCHAR(15)
  DEFAULT 'bobby';

And this SQL statement creates a Domain whose default value is the value returned by the CURRENT_DATE function:

CREATE DOMAIN domain_1 AS DATE DEFAULT CURRENT_DATE;

<Domain Constraint>s

The optional <Domain Constraint> list clause of CREATE DOMAIN is used to define zero or more <Constraint>s on the Domain: the Constraint rules will restrict the Domain's set of valid values see our chapter on Constraints and Assertions. The syntax "CREATE DOMAIN <Domain name> AS <data type> DEFAULT default value <Domain Constraint> <Domain Constraint>" defines a Domain whose definition includes two <Domain Constraint>s. Here is an example:

CREATE DOMAIN domain_1 AS SMALLINT
  DEFAULT 150
  CONSTRAINT constraint_1
      CHECK (VALUE IS NOT NULL) NOT DEFERRABLE
  CONSTRAINT constraint_2
      CHECK (VALUE BETWEEN -1000 AND 9999) DEFERRABLE INITIALLY IMMEDIATE;

In this example, DOMAIN_1 has a default value of 150 and is constrained to accept only integers that fall into SMALLINT's range. The Domain is further constrained (by CONSTRAINT_1) not to accept null values and (by CONSTRAINT_2) to accept only values between -1000 and +9999. Since a <Domain Constraint>'s search condition may not be recursive, this SQL statement will return an error because the <Domain Constraint> refers to the Domain to which it belongs:

CREATE DOMAIN domain_1 AS FLOAT
  CONSTRAINT constraint_1
      CHECK (VALUE IN (domain_1) NOT DEFERRABLE);

If you want to restrict your code to Core SQL, don't use the CREATE DOMAIN statement.

Comments

Comments loading...