Thom Brown <t...@linux.com> wrote:
> Kevin Grittner <kevin.gritt...@wicourts.gov> wrote:
>> It looks like discussion died here. Do you want to propose a new
>> patch? (I'd be happy to give it a shot if you'd rather.)
>
> Sure, go for it. :)
Initial stab at it attached.
I'm torn on whether the paragraph I added to the foreign key
constraint section should be in a warning block -- we do see
complaints from time-to-time from people who are surprised by slow
deletes from referenced tables; in some cases they have come from
database products which automatically create an index on the
referencing columns and are surprised that they need to choose
whether and how to do so in PostgreSQL.
I think we may want to link to these sections from the appropriate
sections of CREATE TABLE (and possibly ALTER TABLE), but that seems
like it could be a separate patch.
Exclusion constraint documentation relies rather more heavily on the
CREATE TABLE page, and is skimpy on the constraints page. I think
that the CREATE TABLE page should focus on syntax and an overview,
and link to the constraints page for any in-depth information.
Again, that seems like it could be addressed separately, but it
seemed worth mentioning, since I stumbled across it.
-Kevin
*** a/doc/src/sgml/ddl.sgml
--- b/doc/src/sgml/ddl.sgml
***************
*** 550,555 **** CREATE TABLE products (
--- 550,563 ----
</indexterm>
<para>
+ Adding a unique constraint will automatically create a unique btree
+ index on the column or group of columns declared for the constraint.
+ The index name will match the name given to the constraint; if no
+ constraint name is specified, it will be the table name suffixed
+ with <literal>_<replaceable>columnname_key</replaceable></literal>.
+ </para>
+
+ <para>
In general, a unique constraint is violated when there is more than
one row in the table where the values of all of the
columns included in the constraint are equal.
***************
*** 623,628 **** CREATE TABLE example (
--- 631,644 ----
</para>
<para>
+ Adding a primary key will automatically create a unique btree index
+ on the column or group of columns declared for the primary key.
+ The index name will match the name given to the constraint; if no
+ constraint name is specified, it will be the table name suffixed
+ with <literal>_pkey</literal>.
+ </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.)
***************
*** 832,837 **** CREATE TABLE order_items (
--- 848,863 ----
</para>
<para>
+ Since a <command>DELETE</command> of a row from a 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 it
+ is not always needed, and there are many choices available on how
+ index, declaration of a foreign key constraint does not
+ automatically create an index.
+ </para>
+
+ <para>
More information about updating and deleting data is in <xref
linkend="dml">.
</para>
***************
*** 875,880 **** CREATE TABLE circles (
--- 901,915 ----
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. The index
+ name will match the name given to the constraint; if no constraint
+ name is specified, it will be the table name and column names used
+ in the constraint, separated by underscores, suffixed with
+ <literal>_excl</literal>.
+ </para>
</sect2>
</sect1>
--
Sent via pgsql-docs mailing list (pgsql-docs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-docs
