diff options
Diffstat (limited to 'doc/src')
| -rw-r--r-- | doc/src/sgml/ref/create_domain.sgml | 47 | ||||
| -rw-r--r-- | doc/src/sgml/ref/create_table.sgml | 8 |
2 files changed, 48 insertions, 7 deletions
diff --git a/doc/src/sgml/ref/create_domain.sgml b/doc/src/sgml/ref/create_domain.sgml index dd782e48dce..8b7848b820d 100644 --- a/doc/src/sgml/ref/create_domain.sgml +++ b/doc/src/sgml/ref/create_domain.sgml @@ -139,12 +139,8 @@ CREATE DOMAIN <replaceable class="parameter">name</replaceable> [ AS ] <replacea <term><literal>NOT NULL</></term> <listitem> <para> - Values of this domain are normally prevented from being null. - However, it is still possible for a domain with this constraint - to take a null value if it is assigned a matching domain type - that has become null, e.g. via a LEFT OUTER JOIN, or - <command>INSERT INTO tab (domcol) VALUES ((SELECT domcol FROM - tab WHERE false))</command>. + Values of this domain are prevented from being null + (but see notes below). </para> </listitem> </varlistentry> @@ -171,19 +167,56 @@ CREATE DOMAIN <replaceable class="parameter">name</replaceable> [ AS ] <replacea 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</> - to refer to the value being tested. + to refer to the value being tested. Expressions evaluating + to TRUE or UNKNOWN succeed. If the expression produces a FALSE result, + an error is reported and the value is not allowed to be converted + to the domain type. </para> <para> Currently, <literal>CHECK</literal> expressions cannot contain subqueries nor refer to variables other than <literal>VALUE</>. </para> + + <para> + When a domain has multiple <literal>CHECK</literal> constraints, + they will be tested in alphabetical order by name. + (<productname>PostgreSQL</> versions before 9.5 did not honor any + particular firing order for <literal>CHECK</literal> constraints.) + </para> </listitem> </varlistentry> </variablelist> </refsect1> <refsect1> + <title>Notes</title> + + <para> + Domain constraints, particularly <literal>NOT NULL</>, are checked when + converting a value to the domain type. It is possible for a column that + is nominally of the domain type to read as null despite there being such + a constraint. For example, this can happen in an outer-join query, if + the domain column is on the nullable side of the outer join. A more + subtle example is +<programlisting> +INSERT INTO tab (domcol) VALUES ((SELECT domcol FROM tab WHERE false)); +</programlisting> + The empty scalar sub-SELECT will produce a null value that is considered + to be of the domain type, so no further constraint checking is applied + to it, and the insertion will succeed. + </para> + + <para> + It is very difficult to avoid such problems, because of SQL's general + assumption that NULL is a valid value of every datatype. Best practice + therefore is to design a domain's constraints so that NULL is allowed, + and then to apply column <literal>NOT NULL</> constraints to columns of + the domain type as needed, rather than directly to the domain type. + </para> + </refsect1> + + <refsect1> <title>Examples</title> <para> diff --git a/doc/src/sgml/ref/create_table.sgml b/doc/src/sgml/ref/create_table.sgml index 3e6246da668..324d59371a7 100644 --- a/doc/src/sgml/ref/create_table.sgml +++ b/doc/src/sgml/ref/create_table.sgml @@ -442,6 +442,14 @@ CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXI A constraint marked with <literal>NO INHERIT</> will not propagate to child tables. </para> + + <para> + When a table has multiple <literal>CHECK</literal> constraints, + they will be tested for each row in alphabetical order by name, + after checking <literal>NOT NULL</> constraints. + (<productname>PostgreSQL</> versions before 9.5 did not honor any + particular firing order for <literal>CHECK</literal> constraints.) + </para> </listitem> </varlistentry> |