Improve documentation about PRIMARY KEY constraints.

tglsfdc · tglsfdc · commit abcb32d55ed8 · 2016-02-07T16:02:44.000-05:00
Get rid of the false implication that PRIMARY KEY is exactly equivalent to
UNIQUE + NOT NULL.  That was more-or-less true at one time in our
implementation, but the standard doesn't say that, and we've grown various
features (many of them required by spec) that treat a pkey differently from
less-formal constraints.  Per recent discussion on pgsql-general.

I failed to resist the temptation to do some other wordsmithing in the
same area.
diff --git a/doc/src/sgml/ddl.sgml b/doc/src/sgml/ddl.sgml
@@ -495,8 +495,8 @@ CREATE TABLE products (
    </indexterm>
 
    <para>
-    Unique constraints ensure that the data contained in a column or a
-    group of columns is unique with respect to all the rows in the
+    Unique constraints ensure that the data contained in a column, or a
+    group of columns, is unique among all the rows in the
     table.  The syntax is:
 <programlisting>
 CREATE TABLE products (
@@ -518,8 +518,8 @@ CREATE TABLE products (
    </para>
 
    <para>
-    If a unique constraint refers to a group of columns, the columns
-    are listed separated by commas:
+    To define a unique constraint for a group of columns, write it as a
+    table constraint with the column names separated by commas:
 <programlisting>
 CREATE TABLE example (
     a integer,
@@ -545,8 +545,11 @@ CREATE TABLE products (
    </para>
 
    <para>
-    Adding a unique constraint will automatically create a unique btree
-    index on the column or group of columns used in the constraint.
+    Adding a unique constraint will automatically create a unique B-tree
+    index on the column or group of columns listed in the constraint.
+    A uniqueness restriction covering only some rows cannot be written as
+    a unique constraint, but it is possible to enforce such a restriction by
+    creating a unique <link linkend="indexes-partial">partial index</link>.
    </para>
 
    <indexterm>
@@ -555,10 +558,10 @@ CREATE TABLE products (
    </indexterm>
 
    <para>
-    In general, a unique constraint is violated when there is more than
+    In general, a unique constraint is violated if there is more than
     one row in the table where the values of all of the
     columns included in the constraint are equal.
-    However, two null values are not considered equal in this
+    However, two null values are never considered equal in this
     comparison.  That means even in the presence of a
     unique constraint it is possible to store duplicate
     rows that contain a null value in at least one of the constrained
@@ -582,8 +585,9 @@ CREATE TABLE products (
    </indexterm>
 
    <para>
-    Technically, a primary key constraint is simply a combination of a
-    unique constraint and a not-null constraint.  So, the following
+    A primary key constraint indicates that a column, or group of columns,
+    can be used as a unique identifier for rows in the table.  This
+    requires that the values be both unique and not null.  So, the following
     two table definitions accept the same data:
 <programlisting>
 CREATE TABLE products (
@@ -603,7 +607,7 @@ CREATE TABLE products (
    </para>
 
    <para>
-    Primary keys can also constrain more than one column; the syntax
+    Primary keys can span more than one column; the syntax
     is similar to unique constraints:
 <programlisting>
 CREATE TABLE example (
@@ -616,31 +620,31 @@ CREATE TABLE example (
    </para>
 
    <para>
-    A primary key indicates that a column or group of columns can be
-    used as a unique identifier for rows in the table.  (This is a
-    direct consequence of the definition of a primary key.  Note that
-    a unique constraint does not, by itself, provide a unique identifier
-    because it does not exclude null values.)  This is useful both for
-    documentation purposes and for client applications.  For example,
-    a GUI application that allows modifying row values probably needs
-    to know the primary key of a table to be able to identify rows
-    uniquely.
-   </para>
-
-   <para>
-    Adding a primary key will automatically create a unique btree index
-    on the column or group of columns used in the primary key.
+    Adding a primary key will automatically create a unique B-tree index
+    on the column or group of columns listed in the primary key, and will
+    force the column(s) to be marked <literal>NOT NULL</>.
    </para>
 
    <para>
     A table can have at most one primary key.  (There can be any number
-    of unique and not-null constraints, which are functionally the same
-    thing, but only one can be identified as the primary key.)
+    of unique and not-null constraints, which 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
     not enforced by <productname>PostgreSQL</productname>, but it is
     usually best to follow it.
    </para>
+
+   <para>
+    Primary keys are useful both for
+    documentation purposes and for client applications.  For example,
+    a GUI application that allows modifying row values probably needs
+    to know the primary key of a table to be able to identify rows
+    uniquely.  There are also various ways in which the database system
+    makes use of a primary key if one has been declared; for example,
+    the primary key defines the default target column(s) for foreign keys
+    referencing its table.
+   </para>
   </sect2>
 
   <sect2 id="ddl-constraints-fk">
diff --git a/doc/src/sgml/ref/create_table.sgml b/doc/src/sgml/ref/create_table.sgml
@@ -493,25 +493,25 @@ CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXI
     <term><literal>PRIMARY KEY ( <replaceable class="PARAMETER">column_name</replaceable> [, ... ] )</> (table constraint)</term>
     <listitem>
      <para>
-      The primary key constraint specifies that a column or columns of a table
-      can contain only unique (non-duplicate), nonnull values.
-      Technically, <literal>PRIMARY KEY</literal> is merely a
-      combination of <literal>UNIQUE</> and <literal>NOT NULL</>, but
-      identifying a set of columns as primary key also provides
-      metadata about the design of the schema, as a primary key
-      implies that other tables
-      can rely on this set of columns as a unique identifier for rows.
+      The <literal>PRIMARY KEY</> constraint specifies that a column or
+      columns of a table can contain only unique (non-duplicate), nonnull
+      values. Only one primary key can be specified for a table, whether as a
+      column constraint or a table constraint.
      </para>
 
      <para>
-      Only one primary key can be specified for a table, whether as a
-      column constraint or a table constraint.
+      The primary key constraint should name a set of columns that is
+      different from the set of columns named by any unique
+      constraint defined for the same table.  (Otherwise, the unique
+      constraint is redundant and will be discarded.)
      </para>
 
      <para>
-      The primary key constraint should name a set of columns that is
-      different from other sets of columns named by any unique
-      constraint defined for the same table.
+      <literal>PRIMARY KEY</literal> enforces the same data constraints as
+      a combination of <literal>UNIQUE</> and <literal>NOT NULL</>, but
+      identifying a set of columns as the primary key also provides metadata
+      about the design of the schema, since a primary key implies that other
+      tables can rely on this set of columns as a unique identifier for rows.
      </para>
     </listitem>
    </varlistentry>
