From 48322916559607e5b6a85f30a8e5307baf9cac3d Mon Sep 17 00:00:00 2001
From: Tom Lane <tgl@sss.pgh.pa.us>
Date: Thu, 26 Aug 2010 21:08:35 +0000
Subject: [PATCH] Explain automatic creation (or lack of it) of indexes for the
 various types of constraints.

Kevin Grittner
---
 doc/src/sgml/ddl.sgml | 27 ++++++++++++++++++++++++++-
 1 file changed, 26 insertions(+), 1 deletion(-)

diff --git a/doc/src/sgml/ddl.sgml b/doc/src/sgml/ddl.sgml
index 8f42a8d630..261f675368 100644
--- a/doc/src/sgml/ddl.sgml
+++ b/doc/src/sgml/ddl.sgml
@@ -1,4 +1,4 @@
-<!-- $PostgreSQL: pgsql/doc/src/sgml/ddl.sgml,v 1.96 2010/08/23 02:43:25 tgl Exp $ -->
+<!-- $PostgreSQL: pgsql/doc/src/sgml/ddl.sgml,v 1.97 2010/08/26 21:08:35 tgl Exp $ -->
 
 <chapter id="ddl">
  <title>Data Definition</title>
@@ -544,6 +544,11 @@ CREATE TABLE products (
 </programlisting>
    </para>
 
+   <para>
+    Adding a unique constraint will automatically create a unique btree
+    index on the column or group of columns used in the constraint.
+   </para>
+
    <indexterm>
     <primary>null value</primary>
     <secondary sortas="unique constraints">with unique constraints</secondary>
@@ -622,6 +627,11 @@ CREATE TABLE example (
     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.
+   </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
@@ -831,6 +841,16 @@ CREATE TABLE order_items (
     column is changed (updated).  The possible actions are the same.
    </para>
 
+   <para>
+    Since a <command>DELETE</command> of a row from the referenced table
+    or an <command>UPDATE</command> of a referenced column will require
+    a scan of the referencing table for rows matching the old value, it
+    is often a good idea to index the referencing columns.  Because this
+    is not always needed, and there are many choices available on how
+    to index, declaration of a foreign key constraint does not
+    automatically create an index on the referencing columns.
+   </para>
+
    <para>
     More information about updating and deleting data is in <xref
     linkend="dml">.
@@ -875,6 +895,11 @@ CREATE TABLE circles (
     See also <link linkend="SQL-CREATETABLE-EXCLUDE"><command>CREATE
     TABLE ... CONSTRAINT ... EXCLUDE</></link> for details.
    </para>
+
+   <para>
+    Adding an exclusion constraint will automatically create an index
+    of the type specified in the constraint declaration.
+   </para>
   </sect2>
  </sect1>
 
-- 
2.39.5