| <!-- |
| $PostgreSQL: pgsql/doc/src/sgml/ref/create_domain.sgml,v 1.29 2006/09/16 00:30:17 momjian Exp $ |
| PostgreSQL documentation |
| --> |
| |
| <refentry id="SQL-CREATEDOMAIN"> |
| <refmeta> |
| <refentrytitle id="sql-createdomain-title">CREATE DOMAIN</refentrytitle> |
| <refmiscinfo>SQL - Language Statements</refmiscinfo> |
| </refmeta> |
| |
| <refnamediv> |
| <refname>CREATE DOMAIN</refname> |
| <refpurpose>define a new domain</refpurpose> |
| </refnamediv> |
| |
| <indexterm zone="sql-createdomain"> |
| <primary>CREATE DOMAIN</primary> |
| </indexterm> |
| |
| <refsynopsisdiv> |
| <synopsis> |
| CREATE DOMAIN <replaceable class="parameter">name</replaceable> [ AS ] <replaceable class="parameter">data_type</replaceable> |
| [ DEFAULT <replaceable>expression</replaceable> ] |
| [ <replaceable class="PARAMETER">constraint</replaceable> [ ... ] ] |
| |
| where <replaceable class="PARAMETER">constraint</replaceable> is: |
| |
| [ CONSTRAINT <replaceable class="PARAMETER">constraint_name</replaceable> ] |
| { NOT NULL | NULL | CHECK (<replaceable class="PARAMETER">expression</replaceable>) } |
| </synopsis> |
| </refsynopsisdiv> |
| |
| <refsect1> |
| <title>Description</title> |
| |
| <para> |
| <command>CREATE DOMAIN</command> creates a new domain. A domain is |
| essentially a data type with optional constraints (restrictions on |
| the allowed set of values). |
| The user who defines a domain becomes its owner. |
| </para> |
| |
| <para> |
| If a schema name is given (for example, <literal>CREATE DOMAIN |
| myschema.mydomain ...</literal>) then the domain is created in the |
| specified schema. Otherwise it is created in the current schema. |
| The domain name must be unique among the types and domains existing |
| in its schema. |
| </para> |
| |
| <para> |
| Domains are useful for abstracting common constraints on fields into |
| a single location for maintenance. For example, several tables might |
| contain email address columns, all requiring the same CHECK constraint |
| to verify the address syntax. |
| Define a domain rather than setting up each table's constraint |
| individually. |
| </para> |
| </refsect1> |
| |
| <refsect1> |
| <title>Parameters</title> |
| |
| <variablelist> |
| <varlistentry> |
| <term><replaceable class="parameter">name</replaceable></term> |
| <listitem> |
| <para> |
| The name (optionally schema-qualified) of a domain to be created. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><replaceable class="PARAMETER">data_type</replaceable></term> |
| <listitem> |
| <para> |
| The underlying data type of the domain. This may include array |
| specifiers. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><literal>DEFAULT <replaceable>expression</replaceable></literal></term> |
| |
| <listitem> |
| <para> |
| The <literal>DEFAULT</literal> clause specifies a default value for |
| columns of the domain data type. The value is any |
| variable-free expression (but subqueries are not allowed). |
| The data type of the default expression must match the data |
| type of the domain. If no default value is specified, then |
| the default value is the null value. |
| </para> |
| |
| <para> |
| The default expression will be used in any insert operation |
| that does not specify a value for the column. If a default |
| value is defined for a particular column, it overrides any |
| default associated with the domain. In turn, the domain |
| default overrides any default value associated with the |
| underlying data type. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><literal>CONSTRAINT <replaceable class="PARAMETER">constraint_name</replaceable></literal></term> |
| <listitem> |
| <para> |
| An optional name for a constraint. If not specified, |
| the system generates a name. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><literal>NOT NULL</literal></term> |
| <listitem> |
| <para> |
| Values of this domain are not allowed to be null. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><literal>NULL</literal></term> |
| <listitem> |
| <para> |
| Values of this domain are allowed to be null. This is the default. |
| </para> |
| |
| <para> |
| This clause is only intended for compatibility with |
| nonstandard SQL databases. Its use is discouraged in new |
| applications. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><literal>CHECK (<replaceable class="PARAMETER">expression</replaceable>)</literal></term> |
| <listitem> |
| <para> |
| <literal>CHECK</literal> clauses specify integrity constraints or tests |
| which values of the domain must satisfy. |
| Each constraint must be an expression |
| producing a Boolean result. It should use the key word <literal>VALUE</literal> |
| to refer to the value being tested. |
| </para> |
| |
| <para> |
| Currently, <literal>CHECK</literal> expressions cannot contain |
| subqueries nor refer to variables other than <literal>VALUE</literal>. |
| </para> |
| </listitem> |
| </varlistentry> |
| </variablelist> |
| </refsect1> |
| |
| <refsect1> |
| <title>Examples</title> |
| |
| <para> |
| This example creates the <type>us_postal_code</type> data type and |
| then uses the type in a table definition. A regular expression test |
| is used to verify that the value looks like a valid US postal code. |
| |
| <programlisting> |
| CREATE DOMAIN us_postal_code AS TEXT |
| CHECK( |
| VALUE ~ '^\\d{5}$' |
| OR VALUE ~ '^\\d{5}-\\d{4}$' |
| ); |
| |
| CREATE TABLE us_snail_addy ( |
| address_id SERIAL PRIMARY KEY, |
| street1 TEXT NOT NULL, |
| street2 TEXT, |
| street3 TEXT, |
| city TEXT NOT NULL, |
| postal us_postal_code NOT NULL |
| ); |
| </programlisting> |
| </para> |
| </refsect1> |
| |
| <refsect1 id="SQL-CREATEDOMAIN-compatibility"> |
| <title>Compatibility</title> |
| |
| <para> |
| The command <command>CREATE DOMAIN</command> conforms to the SQL |
| standard. |
| </para> |
| </refsect1> |
| |
| <refsect1 id="SQL-CREATEDOMAIN-see-also"> |
| <title>See Also</title> |
| |
| <simplelist type="inline"> |
| <member><xref linkend="sql-alterdomain" endterm="sql-alterdomain-title"></member> |
| <member><xref linkend="sql-dropdomain" endterm="sql-dropdomain-title"></member> |
| </simplelist> |
| </refsect1> |
| |
| </refentry> |