ddl.sgml

PostgreSQL7.4.6 for Linux

    <literal>product_no</literal> entries that do not appear in the
    products table.
   </para>

   <para>
    We say that in this situation the orders table is the
    <firstterm>referencing</firstterm> table and the products table is
    the <firstterm>referenced</firstterm> table.  Similarly, there are
    referencing and referenced columns.
   </para>

   <para>
    You can also shorten the above command to
<programlisting>
CREATE TABLE orders (
    order_id integer PRIMARY KEY,
    product_no integer REFERENCES products,
    quantity integer
);
</programlisting>
    because in absence of a column list the primary key of the
    referenced table is used as the referenced column.
   </para>

   <para>
    A foreign key can also constrain and reference a group of columns.
    As usual, it then needs to be written in table constraint form.
    Here is a contrived syntax example:
<programlisting>
CREATE TABLE t1 (
  a integer PRIMARY KEY,
  b integer,
  c integer,
  <emphasis>FOREIGN KEY (b, c) REFERENCES other_table (c1, c2)</emphasis>
);
</programlisting>
    Of course, the number and type of the constrained columns needs to
    match the number and type of the referenced columns.
   </para>

   <para>
    A table can contain more than one foreign key constraint.  This is
    used to implement many-to-many relationships between tables.  Say
    you have tables about products and orders, but now you want to
    allow one order to contain possibly many products (which the
    structure above did not allow).  You could use this table structure:
<programlisting>
CREATE TABLE products (
    product_no integer PRIMARY KEY,
    name text,
    price numeric
);

CREATE TABLE orders (
    order_id integer PRIMARY KEY,
    shipping_address text,
    ...
);

CREATE TABLE order_items (
    product_no integer REFERENCES products,
    order_id integer REFERENCES orders,
    quantity integer,
    PRIMARY KEY (product_no, order_id)
);
</programlisting>
    Note also that the primary key overlaps with the foreign keys in
    the last table.
   </para>

   <indexterm>
    <primary>CASCADE</primary>
    <secondary>foreign key action</secondary>
   </indexterm>

   <indexterm>
    <primary>RESTRICT</primary>
    <secondary>foreign key action</secondary>
   </indexterm>

   <para>
    We know that the foreign keys disallow creation of orders that
    do not relate to any products.  But what if a product is removed
    after an order is created that references it?  SQL allows you to
    specify that as well.  Intuitively, we have a few options:
    <itemizedlist spacing="compact">
     <listitem><para>Disallow deleting a referenced product</para></listitem>
     <listitem><para>Delete the orders as well</para></listitem>
     <listitem><para>Something else?</para></listitem>
    </itemizedlist>
   </para>

   <para>
    To illustrate this, let's implement the following policy on the
    many-to-many relationship example above: When someone wants to
    remove a product that is still referenced by an order (via
    <literal>order_items</literal>), we disallow it.  If someone
    removes an order, the order items are removed as well.
<programlisting>
CREATE TABLE products (
    product_no integer PRIMARY KEY,
    name text,
    price numeric
);

CREATE TABLE orders (
    order_id integer PRIMARY KEY,
    shipping_address text,
    ...
);

CREATE TABLE order_items (
    product_no integer REFERENCES products <emphasis>ON DELETE RESTRICT</emphasis>,
    order_id integer REFERENCES orders <emphasis>ON DELETE CASCADE</emphasis>,
    quantity integer,
    PRIMARY KEY (product_no, order_id)
);
</programlisting>
   </para>

   <para>
    Restricting and cascading deletes are the two most common options.
    <literal>RESTRICT</literal> can also be written as <literal>NO
    ACTION</literal> and it's also the default if you do not specify
    anything.  There are two other options for what should happen with
    the foreign key columns when a primary key is deleted:
    <literal>SET NULL</literal> and <literal>SET DEFAULT</literal>.
    Note that these do not excuse you from observing any constraints.
    For example, if an action specifies <literal>SET DEFAULT</literal>
    but the default value would not satisfy the foreign key, the
    deletion of the primary key will fail.
   </para>

   <para>
    Analogous to <literal>ON DELETE</literal> there is also
    <literal>ON UPDATE</literal> which is invoked when a primary key
    is changed (updated).  The possible actions are the same.
   </para>

   <para>
    More information about updating and deleting data is in <xref
    linkend="dml">.
   </para>

   <para>
    Finally, we should mention that a foreign key must reference
    columns that are either a primary key or form a unique constraint.
    If the foreign key references a unique constraint, there are some
    additional possibilities regarding how null values are matched.
    These are explained in the reference documentation for
    <xref linkend="sql-createtable" endterm="sql-createtable-title">.
   </para>
  </sect2>
 </sect1>

 <sect1 id="ddl-inherit">
  <title>Inheritance</title>

  <comment>This section needs to be rethought.  Some of the
  information should go into the following chapters.</comment>

  <para>
   Let's create two tables. The capitals  table  contains
   state  capitals  which  are also cities. Naturally, the
   capitals table should inherit from cities.

<programlisting>
CREATE TABLE cities (
    name            text,
    population      float,
    altitude        int     -- (in ft)
);

CREATE TABLE capitals (
    state           char(2)
) INHERITS (cities);
</programlisting>

   In this case, a row  of  capitals  <firstterm>inherits</firstterm>  all
   attributes  (name,  population,  and altitude) from its
   parent, cities.  The type  of  the  attribute  name  is
   <type>text</type>,  a  native  <productname>PostgreSQL</productname>  type
   for variable length character strings.  The type of the attribute
   population is
   <type>float</type>,  a  native <productname>PostgreSQL</productname> type for double precision
   floating-point numbers.  State capitals have  an  extra
   attribute, state, that shows their state.  In <productname>PostgreSQL</productname>,
   a  table  can inherit from zero or more other tables,
   and a query can reference either  all  rows  of  a
   table  or  all  rows of  a  table plus all of its
   descendants. 

   <note>
    <para>
     The inheritance hierarchy is actually a directed acyclic graph.
    </para>
   </note>
  </para>

  <para>
    For example, the  following  query finds the  names  of  all  cities,
    including  state capitals, that are located at an altitude 
    over 500ft:

<programlisting>
SELECT name, altitude
    FROM cities
    WHERE altitude &gt; 500;
</programlisting>

   which returns:

<programlisting>
   name    | altitude
-----------+----------
 Las Vegas |     2174
 Mariposa  |     1953
 Madison   |      845
</programlisting>
  </para>

  <para>
    On the other hand, the  following  query  finds
    all  the cities that are not state capitals and
    are situated at an altitude over 500ft:

<programlisting>
SELECT name, altitude
    FROM ONLY cities
    WHERE altitude &gt; 500;

   name    | altitude
-----------+----------
 Las Vegas |     2174
 Mariposa  |     1953
</programlisting>         
  </para>

  <para>
   Here the <quote>ONLY</quote> before cities indicates that the query should
   be  run over only cities and not tables below cities in the
   inheritance hierarchy.  Many of the  commands  that  we
   have  already discussed -- <command>SELECT</command>,
   <command>UPDATE</command> and <command>DELETE</command> --
   support this <quote>ONLY</quote> notation.
  </para>

  <para>
  In some cases you may wish to know which table a particular row
  originated from. There is a system column called
  <structfield>TABLEOID</structfield> in each table which can tell you the
  originating table:

<programlisting>
SELECT c.tableoid, c.name, c.altitude
FROM cities c
WHERE c.altitude &gt; 500;
</programlisting>

   which returns:

<programlisting>
 tableoid |   name    | altitude
----------+-----------+----------
   139793 | Las Vegas |     2174
   139793 | Mariposa  |     1953
   139798 | Madison   |      845
</programlisting>

   (If you try to reproduce this example, you will probably get
   different numeric OIDs.)  By doing a join with
   <structname>pg_class</> you can see the actual table names:

<programlisting>
SELECT p.relname, c.name, c.altitude
FROM cities c, pg_class p
WHERE c.altitude &gt; 500 and c.tableoid = p.oid;
</programlisting>

   which returns:

<programlisting>
 relname  |   name    | altitude
----------+-----------+----------
 cities   | Las Vegas |     2174
 cities   | Mariposa  |     1953
 capitals | Madison   |      845
</programlisting>
   
  </para>

  <note>
   <title>Deprecated</title> 
   <para>
     In previous versions of <productname>PostgreSQL</productname>, the
     default behavior was not to include child tables in queries. This was
     found to be error prone and is also in violation of the SQL99
     standard. Under the old syntax, to get the sub-tables you append
     <literal>*</literal> to the table name.
     For example
<programlisting>
SELECT * from cities*;
</programlisting>
     You can still explicitly specify scanning child tables by appending
     <literal>*</literal>, as well as explicitly specify not scanning child tables by
     writing <quote>ONLY</quote>.  But beginning in version 7.1, the default
     behavior for an undecorated table name is to scan its child tables
     too, whereas before the default was not to do so.  To get the old
     default behavior, set the configuration option
     <literal>SQL_Inheritance</literal> to off, e.g.,
<programlisting>
SET SQL_Inheritance TO OFF;
</programlisting>
     or add a line in your <filename>postgresql.conf</filename> file.
   </para>
  </note>

  <para>
   A limitation of the inheritance feature is that indexes (including
   unique constraints) and foreign key constraints only apply to single
   tables, not to their inheritance children.  Thus, in the above example,
   specifying that another table's column <literal>REFERENCES cities(name)</>
   would allow the other table to contain city names but not capital names.
   This deficiency will probably be fixed in some future release.
  </para>
 </sect1>

 <sect1 id="ddl-alter">
  <title>Modifying Tables</title>

  <indexterm zone="ddl-alter">
   <primary>table</primary>
   <secondary>modifying</secondary>
  </indexterm>

  <para>
   When you create a table and you realize that you made a mistake, or
   the requirements of the application changed, then you can drop the
   table and create it again.  But this is not a convenient option if
   the table is already filled with data, or if the table is
   referenced by other database objects (for instance a foreign key
   constraint).  Therefore <productname>PostgreSQL</productname>
   provides a family of commands to make modifications on existing
   tables.
  </para>

  <para>
   You can
   <itemizedlist spacing="compact">
    <listitem>
     <para>Add columns,</para>
    </listitem>
    <listitem>
     <para>Remove columns,</para>
    </listitem>
    <listitem>
     <para>Add constraints,</para>
    </listitem>
    <listitem>
     <para>Remove constraints,</para>
    </listitem>
    <listitem>
     <para>Change default values,</para>
    </listitem>
    <listitem>
     <para>Rename columns,</para>
    </listitem>
    <listitem>
     <para>Rename tables.</para>
    </listitem>
   </itemizedlist>

   All these actions are performed using the <literal>ALTER
   TABLE</literal> command.
  </para>

  <sect2>
   <title>Adding a Column</title>

   <indexterm>
    <primary>column</primary>
    <secondary>adding</secondary>
   </indexterm>

   <para>
    To add a column, use this command:
<programlisting>
ALTER TABLE products ADD COLUMN description text;
</programlisting>
    The new column will initially be filled with null values in the
    existing rows of the table.
   </para>

   <para>
    You can also define a constraint on the column at the same time,
    using the usual syntax:
<programlisting>
ALTER TABLE products ADD COLUMN description text CHECK (description &lt;&gt; '');
</programlisting>
    A new column cannot have a not-null constraint since the column