ddl.sgml

PostgreSQL 8.1.4的源码 适用于Linux下的开源数据库系统

    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> prevents deletion of a
    referenced row. <literal>NO ACTION</literal> means that if any
    referencing rows still exist when the constraint is checked, an error
    is raised; this is the default behavior if you do not specify anything.
    (The essential difference between these two choices is that
    <literal>NO ACTION</literal> allows the check to be deferred until
    later in the transaction, whereas <literal>RESTRICT</literal> does not.)
    <literal>CASCADE</> specifies that when a referenced row is deleted,
    row(s) referencing it should be automatically deleted as well.
    There are two other options:
    <literal>SET NULL</literal> and <literal>SET DEFAULT</literal>.
    These cause the referencing columns to be set to nulls or default
    values, respectively, when the referenced row is deleted.
    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
    operation will fail.
   </para>

   <para>
    Analogous to <literal>ON DELETE</literal> there is also
    <literal>ON UPDATE</literal> which is invoked when a referenced
    column 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 either are 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-system-columns">
  <title>System Columns</title>

  <para>
   Every table has several <firstterm>system columns</> that are
   implicitly defined by the system.  Therefore, these names cannot be
   used as names of user-defined columns.  (Note that these
   restrictions are separate from whether the name is a key word or
   not; quoting a name will not allow you to escape these
   restrictions.)  You do not really need to be concerned about these
   columns, just know they exist.
  </para>

  <indexterm>
   <primary>column</primary>
   <secondary>system column</secondary>
  </indexterm>

  <variablelist>
   <varlistentry>
    <term><structfield>oid</></term>
    <listitem>
     <para>
      <indexterm>
       <primary>OID</primary>
       <secondary>column</secondary>
      </indexterm>
      The object identifier (object ID) of a row. This column is only
      present if the table was created using <literal>WITH
      OIDS</literal>, or if the <xref linkend="guc-default-with-oids">
      configuration variable was set. This column is of type
      <type>oid</type> (same name as the column); see <xref
      linkend="datatype-oid"> for more information about the type.
     </para>
    </listitem>
   </varlistentry>

   <varlistentry>
    <term><structfield>tableoid</></term>
    <listitem>
     <indexterm>
      <primary>tableoid</primary>
     </indexterm>

     <para>
      The OID of the table containing this row.  This column is
      particularly handy for queries that select from inheritance
      hierarchies (see <xref linkend="ddl-inherit">), since without it,
      it's difficult to tell which individual table a row came from.  The
      <structfield>tableoid</structfield> can be joined against the
      <structfield>oid</structfield> column of
      <structname>pg_class</structname> to obtain the table name.
     </para>
    </listitem>
   </varlistentry>

   <varlistentry>
    <term><structfield>xmin</></term>
    <listitem>
     <indexterm>
      <primary>xmin</primary>
     </indexterm>

     <para>
      The identity (transaction ID) of the inserting transaction for
      this row version.  (A row version is an individual state of a
      row; each update of a row creates a new row version for the same
      logical row.)
     </para>
    </listitem>
   </varlistentry>

   <varlistentry>
    <term><structfield>cmin</></term>
    <listitem>
     <indexterm>
      <primary>cmin</primary>
     </indexterm>

     <para>
      The command identifier (starting at zero) within the inserting
      transaction.
     </para>
    </listitem>
   </varlistentry>

   <varlistentry>
    <term><structfield>xmax</></term>
    <listitem>
     <indexterm>
      <primary>xmax</primary>
     </indexterm>

     <para>
      The identity (transaction ID) of the deleting transaction, or
      zero for an undeleted row version.  It is possible for this column to
      be nonzero in a visible row version. That usually indicates that the
      deleting transaction hasn't committed yet, or that an attempted
      deletion was rolled back.
     </para>
    </listitem>
   </varlistentry>

   <varlistentry>
    <term><structfield>cmax</></term>
    <listitem>
     <indexterm>
      <primary>cmax</primary>
     </indexterm>

     <para>
      The command identifier within the deleting transaction, or zero.
     </para>
    </listitem>
   </varlistentry>

   <varlistentry>
    <term><structfield>ctid</></term>
    <listitem>
     <indexterm>
      <primary>ctid</primary>
     </indexterm>

     <para>
      The physical location of the row version within its table.  Note that
      although the <structfield>ctid</structfield> can be used to
      locate the row version very quickly, a row's
      <structfield>ctid</structfield> will change each time it is
      updated or moved by <command>VACUUM FULL</>.  Therefore
      <structfield>ctid</structfield> is useless as a long-term row
      identifier.  The OID, or even better a user-defined serial
      number, should be used to identify logical rows.
     </para>
    </listitem>
   </varlistentry>
  </variablelist>

   <para>
    OIDs are 32-bit quantities and are assigned from a single
    cluster-wide counter.  In a large or long-lived database, it is
    possible for the counter to wrap around.  Hence, it is bad
    practice to assume that OIDs are unique, unless you take steps to
    ensure that this is the case.  If you need to identify the rows in
    a table, using a sequence generator is strongly recommended.
    However, OIDs can be used as well, provided that a few additional
    precautions are taken:

    <itemizedlist>
     <listitem>
      <para>
       A unique constraint should be created on the OID column of each
       table for which the OID will be used to identify rows.  When such
       a unique constraint (or unique index) exists, the system takes
       care not to generate an OID matching an already-existing row.
       (Of course, this is only possible if the table contains fewer
       than 2<superscript>32</> (4 billion) rows, and in practice the
       table size had better be much less than that, or performance
       may suffer.)
      </para>
     </listitem>
     <listitem>
      <para>
       OIDs should never be assumed to be unique across tables; use
       the combination of <structfield>tableoid</> and row OID if you
       need a database-wide identifier.
      </para>
     </listitem>
     <listitem>
      <para>
       The tables in question should be created using <literal>WITH
       OIDS</literal>.  As of <productname>PostgreSQL</productname> 8.1,
       <literal>WITHOUT OIDS</> is the default.
      </para>
     </listitem>
    </itemizedlist>
   </para>

   <para>
    Transaction identifiers are also 32-bit quantities.  In a
    long-lived database it is possible for transaction IDs to wrap
    around.  This is not a fatal problem given appropriate maintenance
    procedures; see <xref linkend="maintenance"> for details.  It is
    unwise, however, to depend on the uniqueness of transaction IDs
    over the long term (more than one billion transactions).
   </para>

   <para>
    Command
    identifiers are also 32-bit quantities.  This creates a hard limit
    of 2<superscript>32</> (4 billion) <acronym>SQL</acronym> commands
    within a single transaction.  In practice this limit is not a
    problem &mdash; note that the limit is on number of
    <acronym>SQL</acronym> commands, not number of rows processed.
   </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 change, 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 to existing
   tables.  Note that this is conceptually distinct from altering
   the data contained in the table: here we are interested in altering
   the definition, or structure, of the table.
  </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>Change column data types,</para>
    </listitem>
    <listitem>
     <para>Rename columns,</para>
    </listitem>
    <listitem>
     <para>Rename tables.</para>
    </listitem>
   </itemizedlist>

   All these actions are performed using the
   <xref linkend="sql-altertable" endterm="sql-altertable-title">
   command.
  </para>

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

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

   <para>
    To add a column, use a command like this:
<programlisting>
ALTER TABLE products ADD COLUMN description text;
</programlisting>
    The new column is initially filled with whatever default
    value is given (null if you don't specify a <literal>DEFAULT</> clause).
   </para>

   <para>
    You can also define constraints on the column at the same time,
    using the usual syntax:
<programlisting>
ALTER TABLE products ADD COLUMN description text CHECK (description &lt;&gt; '');
</programlisting>
    In fact all the options that can be applied to a column description
    in <command>CREATE TABLE</> can be used here.  Keep in mind however
    that the default value must satisfy the given constraints, or the
    <literal>ADD</> will fail.  Alternatively, you can add
    constraints later (see below) after you've filled in the new column
    correctly.
   </para>
  </sect2>

  <sect2>
   <title>Removing a Column</title>

   <indexterm>
    <primary>column</primary>
    <secondary>removing</secondary>
   </indexterm>

   <para>
    To remove a column, use a command like this:
<programlisting>
ALTER TABLE products DROP COLUMN description;
</programlisting>
    Whatever data was in the column disappears.  Table constraints involving
    the column are dropped, too.  However, if the column is referenced by a
    foreign key constraint of another table,
    <productname>PostgreSQL</productname> will not silently drop that
    constraint.  You can authorize dropping everything that depends on
    the column by adding <literal>CASCADE</>:
<programlisting>
ALTER TABLE products DROP COLUMN description CASCADE;
</programlisting>
    See <xref linkend="ddl-depend"> for a description of the general
    mechanism behind this.
   </para>
  </sect2>

  <sect2>
   <title>Adding a Constraint</title>

   <indexterm>
    <primary>constraint</primary>
    <secondary>adding</secondary>
   </indexterm>

   <para>
    To add a constraint, the table constraint syntax is used.  For example:
<programlisting>
ALTER TABLE products ADD CHECK (name &lt;&gt; '');
ALTER TABLE products ADD CONSTRAINT some_name UNIQUE (product_no);
ALTER TABLE products ADD FOREIGN KEY (product_group_id) REFERENCES product_groups;
</programlisting>