diff options
| author | Alvaro Herrera <alvherre@alvh.no-ip.org> | 2018-11-26 12:27:07 -0300 |
|---|---|---|
| committer | Alvaro Herrera <alvherre@alvh.no-ip.org> | 2018-11-26 12:38:19 -0300 |
| commit | 36d442a25a1aed39d2c7c3fb24c22640a9a6545d (patch) | |
| tree | 686d24c4978919fc2cdcfc1e3699655a9fd885ce | |
| parent | 664f01b61326eebc0d7b4ce9c89b99d3f18c3b76 (diff) | |
| download | postgresql-36d442a25a1aed39d2c7c3fb24c22640a9a6545d.tar.gz postgresql-36d442a25a1aed39d2c7c3fb24c22640a9a6545d.zip | |
Clarify that cross-row constraints are unsupported
Maybe we'll implement them later, or maybe not, but let's make the statu
quo clear for now.
Author: Lætitia Avrot, Patrick Francelle
Reviewers: too many to list
Discussion: https://postgr.es/m/CAB_COdhUuzNFOJfc7SNNso5rOuVA3ui93KMVunEM8Yih+K5A6A@mail.gmail.com
| -rw-r--r-- | doc/src/sgml/ddl.sgml | 27 | ||||
| -rw-r--r-- | doc/src/sgml/ref/create_table.sgml | 3 |
2 files changed, 29 insertions, 1 deletions
diff --git a/doc/src/sgml/ddl.sgml b/doc/src/sgml/ddl.sgml index 61c4a254603..bfe89ef8ae0 100644 --- a/doc/src/sgml/ddl.sgml +++ b/doc/src/sgml/ddl.sgml @@ -403,6 +403,33 @@ CREATE TABLE products ( ensure that a column does not contain null values, the not-null constraint described in the next section can be used. </para> + + <note> + <para> + <productname>PostgreSQL</productname> does not support + <literal>CHECK</literal> constraints that reference table data other than + the new or updated row being checked. While a <literal>CHECK</literal> + constraint that violates this rule may appear to work in simple + tests, it cannot guarantee that the database will not reach a state + in which the constraint condition is false (due to subsequent changes + of the other row(s) involved). This would cause a database dump and + reload to fail. The reload could fail even when the complete + database state is consistent with the constraint, due to rows not + being loaded in an order that will satisfy the constraint. If + possible, use <literal>UNIQUE</literal>, <literal>EXCLUDE</literal>, + or <literal>FOREIGN KEY</literal> constraints to express + cross-row and cross-table restrictions. + </para> + + <para> + If what you desire is a one-time check against other rows at row + insertion, rather than a continuously-maintained consistency + guarantee, a custom <link linkend="triggers">trigger</link> can be used + to implement that. (This approach avoids the dump/reload problem because + <application>pg_dump</application> does not reinstall triggers until after + reloading data, so that the check will not be enforced during a dump/reload.) + </para> + </note> </sect2> <sect2> diff --git a/doc/src/sgml/ref/create_table.sgml b/doc/src/sgml/ref/create_table.sgml index 50d55970020..d3e33132f37 100644 --- a/doc/src/sgml/ref/create_table.sgml +++ b/doc/src/sgml/ref/create_table.sgml @@ -754,7 +754,8 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM <para> Currently, <literal>CHECK</literal> expressions cannot contain subqueries nor refer to variables other than columns of the - current row. The system column <literal>tableoid</literal> + current row (see <xref linkend="ddl-constraints-check-constraints"/>). + The system column <literal>tableoid</literal> may be referenced, but not any other system column. </para> |