From e5a304b2008e34a4386f5896d3a702aa4b71b33a Mon Sep 17 00:00:00 2001
From: Peter Eisentraut <peter@eisentraut.org>
Date: Wed, 16 Aug 2023 10:46:23 +0200
Subject: [PATCH 2/2] Update ddl.sgml for named not-null constraints

---
 doc/src/sgml/ddl.sgml | 55 ++++++++++++++++++++++++++++++++++---------
 1 file changed, 44 insertions(+), 11 deletions(-)

diff --git a/doc/src/sgml/ddl.sgml b/doc/src/sgml/ddl.sgml
index 58aaa691c6..bf331cafd5 100644
--- a/doc/src/sgml/ddl.sgml
+++ b/doc/src/sgml/ddl.sgml
@@ -651,17 +651,38 @@ <title>Not-Null Constraints</title>
     price numeric
 );
 </programlisting>
+    An explicit constraint name can also be specified, for example:
+<programlisting>
+CREATE TABLE products (
+    product_no integer NOT NULL,
+    name text <emphasis>CONSTRAINT products_name_not_null</emphasis> NOT NULL,
+    price numeric
+);
+</programlisting>
+   </para>
+
+   <para>
+    A not-null constraint is usually written as a column constraint.  The
+    syntax for writing it as a table constraint is
+<programlisting>
+CREATE TABLE products (
+    product_no integer,
+    name text,
+    price numeric,
+    <emphasis>NOT NULL product_no</emphasis>,
+    <emphasis>NOT NULL name</emphasis>
+);
+</programlisting>
+    But this syntax is not standard and mainly intended for use by
+    <application>pg_dump</application>.
    </para>
 
    <para>
-    A not-null constraint is always written as a column constraint.  A
-    not-null constraint is functionally equivalent to creating a check
+    A not-null constraint is functionally equivalent to creating a check
     constraint <literal>CHECK (<replaceable>column_name</replaceable>
     IS NOT NULL)</literal>, but in
     <productname>PostgreSQL</productname> creating an explicit
-    not-null constraint is more efficient.  The drawback is that you
-    cannot give explicit names to not-null constraints created this
-    way.
+    not-null constraint is more efficient.
    </para>
 
    <para>
@@ -678,6 +699,10 @@ <title>Not-Null Constraints</title>
     order the constraints are checked.
    </para>
 
+   <para>
+    However, a column can have at most one explicit not-null constraint.
+   </para>
+
    <para>
     The <literal>NOT NULL</literal> constraint has an inverse: the
     <literal>NULL</literal> constraint.  This does not mean that the
@@ -871,7 +896,7 @@ <title>Primary Keys</title>
 
    <para>
     A table can have at most one primary key.  (There can be any number
-    of unique and not-null constraints, which are functionally almost the
+    of unique constraints, which combined with not-null constraints are functionally almost the
     same thing, but only one can be identified as the primary key.)
     Relational database theory
     dictates that every table must have a primary key.  This rule is
@@ -1531,11 +1556,16 @@ <title>Adding a Constraint</title>
 ALTER TABLE products ADD CONSTRAINT some_name UNIQUE (product_no);
 ALTER TABLE products ADD FOREIGN KEY (product_group_id) REFERENCES product_groups;
 </programlisting>
-    To add a not-null constraint, which cannot be written as a table
-    constraint, use this syntax:
+   </para>
+
+   <para>
+    To add a not-null constraint, which is normally not written as a table
+    constraint, this special syntax is available:
 <programlisting>
 ALTER TABLE products ALTER COLUMN product_no SET NOT NULL;
 </programlisting>
+    Unlike the <literal>ADD</literal> syntax above, this command silently does
+    nothing if the column already has a not-null constraint.
    </para>
 
    <para>
@@ -1576,12 +1606,15 @@ <title>Removing a Constraint</title>
    </para>
 
    <para>
-    This works the same for all constraint types except not-null
-    constraints. To drop a not null constraint use:
+    Simplified syntax is available to drop a not-null constraint:
 <programlisting>
 ALTER TABLE products ALTER COLUMN product_no DROP NOT NULL;
 </programlisting>
-    (Recall that not-null constraints do not have names.)
+    This mirrors the <literal>SET NOT NULL</literal> syntax for adding a
+    not-null constraints.  This command will silently do nothing if the column
+    does not have a not-null constraint.  (Recall that a column can have at
+    most one not-null constraint, so it is never ambigous which constraint
+    this command acts on.)
    </para>
   </sect2>
 
-- 
2.41.0