diff options
| author | Tom Lane <tgl@sss.pgh.pa.us> | 2010-08-26 21:08:43 +0000 |
|---|---|---|
| committer | Tom Lane <tgl@sss.pgh.pa.us> | 2010-08-26 21:08:43 +0000 |
| commit | cf95e921ff4957bed08ac94e3a9d0b91e00cc95f (patch) | |
| tree | 603c985cd30cfa5d9a739e0ea99440459d6253d3 | |
| parent | 4ad4b95be26f6677d80cd744036b5e73c89406c7 (diff) | |
Explain automatic creation (or lack of it) of indexes for the various types
of constraints.
Kevin Grittner
| -rw-r--r-- | doc/src/sgml/ddl.sgml | 27 |
1 files changed, 26 insertions, 1 deletions
diff --git a/doc/src/sgml/ddl.sgml b/doc/src/sgml/ddl.sgml index edbfb31f471..f56d939749f 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.93.2.2 2010/08/23 02:43:35 tgl Exp $ --> +<!-- $PostgreSQL: pgsql/doc/src/sgml/ddl.sgml,v 1.93.2.3 2010/08/26 21:08:43 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> @@ -623,6 +628,11 @@ CREATE TABLE example ( </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 thing, but only one can be identified as the primary key.) @@ -832,6 +842,16 @@ CREATE TABLE order_items ( </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">. </para> @@ -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> |