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.

A Schema may contain zero or more Domains. An SQL Domain is a named, user-defined set of valid values. Domains are dependent on some Schema the <Domain name> must be unique within the Schema the Domain belongs to (it may not be the same as any <UDT name> in its Schema either) and are created, altered and dropped using standard SQL statements. The Objects that may belong to a Domain are known as Domain Constraints; they depend on some Domain.

A Domain is defined by a descriptor that contains six pieces of information:

  1. The <Domain name>, qualified by the <Schema name> of the Schema to which it belongs.
  2. The Domain's SQL <data type> specification, including its name, length, precision and scale, as applicable.
  3. The name of the Character set that the Domain's set of values must belong to (for character string types).
  4. The name of the Domain's default Collation. (This is the Collation that may be used to compare a character string Domain's values in the absence of an explicit COLLATE clause.)
  5. Whether reference values must be checked and whether <reference scope check action> specifies RESTRICT or SET NULL (for REF types).
  6. The Domain's default value (if any).
  7. A descriptor for every Constraint that belongs to the Domain.

To create a Domain, use the CREATE DOMAIN statement (either as a stand-alone SQL statement or within a CREATE SCHEMA statement). CREATE DOMAIN specifies the enclosing Schema, names the Domain and identifies the Domain's set of valid values. To change an existing Domain, use the ALTER DOMAIN statement. To destroy a Domain, use the DROP DOMAIN statement.

There is a one-to-many association between Domains and Columns: one Domain can be used to identify the set of valid values for multiple Columns.

Domain names

A <Domain name> identifies a Domain. The required syntax for a <Domain name> is as follows:

<Domain name> ::=
[ <Schema name>. ] unqualified name

A <Domain name> is a <regular identifier> or a <delimited identifier> that is unique (for all Domains and UDTs) within the Schema it belongs to. The <Schema name> which qualifies a <Domain name> names the Schema that the Domain belongs to and can either be explicitly stated, or a default will be supplied by your DBMS as follows:

  • If a <Domain name> in a CREATE SCHEMA statement isn't qualified, the default qualifier is the name of the Schema you're creating.
  • If the unqualified <Domain name> is found in any other SQL statement in a Module, the default qualifier is the name of the Schema identified in the SCHEMA clause or AUTHORIZATION clause of the MODULE statement that defines that Module.

Here are some examples of <Domain name>s:

DOMAIN_1
-- a <Domain name>

SCHEMA_1.DOMAIN_1
-- a simple qualified <Domain name>

CATALOG_1.SCHEMA_1.DOMAIN_1
-- a fully qualified <Domain name>

Comments

Comments loading...