create_table.sgml

postgresql8.3.4源码,开源数据库

    </para>

    <tip>
     <para>
      The use of <literal>OIDS=FALSE</literal> is not recommended
      for tables with no primary key, since without either an OID or a
      unique data key, it is difficult to identify specific rows.
     </para>
    </tip>

    <para>
     <productname>PostgreSQL</productname> automatically creates an
     index for each unique constraint and primary key constraint to
     enforce uniqueness.  Thus, it is not necessary to create an
     index explicitly for primary key columns.  (See <xref
     linkend="sql-createindex" endterm="sql-createindex-title"> for more information.)
    </para>

    <para>
     Unique constraints and primary keys are not inherited in the
     current implementation.  This makes the combination of
     inheritance and unique constraints rather dysfunctional.
    </para>

    <para>
     A table cannot have more than 1600 columns.  (In practice, the
     effective limit is usually lower because of tuple-length constraints.)
    </para>

 </refsect1>


 <refsect1 id="SQL-CREATETABLE-examples">
  <title>Examples</title>

  <para>
   Create table <structname>films</> and table
   <structname>distributors</>:

<programlisting>
CREATE TABLE films (
    code        char(5) CONSTRAINT firstkey PRIMARY KEY,
    title       varchar(40) NOT NULL,
    did         integer NOT NULL,
    date_prod   date,
    kind        varchar(10),
    len         interval hour to minute
);
</programlisting>

<programlisting>
CREATE TABLE distributors (
     did    integer PRIMARY KEY DEFAULT nextval('serial'),
     name   varchar(40) NOT NULL CHECK (name &lt;&gt; '')
);
</programlisting>
  </para>

  <para>
   Create a table with a 2-dimensional array:

<programlisting>
CREATE TABLE array_int (
    vector  int[][]
);
</programlisting>
  </para>

  <para>
   Define a unique table constraint for the table
   <literal>films</literal>.  Unique table constraints can be defined
   on one or more columns of the table:

<programlisting>
CREATE TABLE films (
    code        char(5),
    title       varchar(40),
    did         integer,
    date_prod   date,
    kind        varchar(10),
    len         interval hour to minute,
    CONSTRAINT production UNIQUE(date_prod)
);
</programlisting>
  </para>

  <para>
   Define a check column constraint:

<programlisting>
CREATE TABLE distributors (
    did     integer CHECK (did &gt; 100),
    name    varchar(40)
);
</programlisting>
  </para>

  <para>
   Define a check table constraint:

<programlisting>
CREATE TABLE distributors (
    did     integer,
    name    varchar(40)
    CONSTRAINT con1 CHECK (did &gt; 100 AND name &lt;&gt; '')
);
</programlisting>
  </para>

  <para>
   Define a primary key table constraint for the table
   <structname>films</>:

<programlisting>
CREATE TABLE films (
    code        char(5),
    title       varchar(40),
    did         integer,
    date_prod   date,
    kind        varchar(10),
    len         interval hour to minute,
    CONSTRAINT code_title PRIMARY KEY(code,title)
);
</programlisting>
  </para>

  <para>
   Define a primary key constraint for table
   <structname>distributors</>.  The following two examples are
   equivalent, the first using the table constraint syntax, the second
   the column constraint syntax:

<programlisting>
CREATE TABLE distributors (
    did     integer,
    name    varchar(40),
    PRIMARY KEY(did)
);
</programlisting>

<programlisting>
CREATE TABLE distributors (
    did     integer PRIMARY KEY,
    name    varchar(40)
);
</programlisting>
  </para>

  <para>
   Assign a literal constant default value for the column
   <literal>name</literal>, arrange for the default value of column
   <literal>did</literal> to be generated by selecting the next value
   of a sequence object, and make the default value of
   <literal>modtime</literal> be the time at which the row is
   inserted:

<programlisting>
CREATE TABLE distributors (
    name      varchar(40) DEFAULT 'Luso Films',
    did       integer DEFAULT nextval('distributors_serial'),
    modtime   timestamp DEFAULT current_timestamp
);
</programlisting>
  </para>

  <para>
   Define two <literal>NOT NULL</> column constraints on the table
   <classname>distributors</classname>, one of which is explicitly
   given a name:

<programlisting>
CREATE TABLE distributors (
    did     integer CONSTRAINT no_null NOT NULL,
    name    varchar(40) NOT NULL
);
</programlisting>
    </para>

    <para>
     Define a unique constraint for the <literal>name</literal> column:

<programlisting>
CREATE TABLE distributors (
    did     integer,
    name    varchar(40) UNIQUE
);
</programlisting>

     The same, specified as a table constraint:

<programlisting>
CREATE TABLE distributors (
    did     integer,
    name    varchar(40),
    UNIQUE(name)
);
</programlisting>
  </para>

  <para>
   Create the same table, specifying 70% fill factor for both the table
   and its unique index:

<programlisting>
CREATE TABLE distributors (
    did     integer,
    name    varchar(40),
    UNIQUE(name) WITH (fillfactor=70)
)
WITH (fillfactor=70);
</programlisting>
  </para>

  <para>
   Create table <structname>cinemas</> in tablespace <structname>diskvol1</>:

<programlisting>
CREATE TABLE cinemas (
        id serial,
        name text,
        location text
) TABLESPACE diskvol1;
</programlisting>
  </para>

 </refsect1>

 <refsect1 id="SQL-CREATETABLE-compatibility">
  <title id="SQL-CREATETABLE-compatibility-title">Compatibility</title>

  <para>
   The <command>CREATE TABLE</command> command conforms to the
   <acronym>SQL</acronym> standard, with exceptions listed below.
  </para>

  <refsect2>
   <title>Temporary Tables</title>

   <para>
    Although the syntax of <literal>CREATE TEMPORARY TABLE</literal>
    resembles that of the SQL standard, the effect is not the same.  In the
    standard,
    temporary tables are defined just once and automatically exist (starting
    with empty contents) in every session that needs them.
    <productname>PostgreSQL</productname> instead
    requires each session to issue its own <literal>CREATE TEMPORARY
    TABLE</literal> command for each temporary table to be used.  This allows
    different sessions to use the same temporary table name for different
    purposes, whereas the standard's approach constrains all instances of a
    given temporary table name to have the same table structure.
   </para>

   <para>
    The standard's definition of the behavior of temporary tables is
    widely ignored.  <productname>PostgreSQL</productname>'s behavior
    on this point is similar to that of several other SQL databases.
   </para>

   <para>
    The standard's distinction between global and local temporary tables
    is not in <productname>PostgreSQL</productname>, since that distinction
    depends on the concept of modules, which
    <productname>PostgreSQL</productname> does not have.
    For compatibility's sake, <productname>PostgreSQL</productname> will
    accept the <literal>GLOBAL</literal> and <literal>LOCAL</literal> keywords
    in a temporary table declaration, but they have no effect.
   </para>

   <para>
    The <literal>ON COMMIT</literal> clause for temporary tables
    also resembles the SQL standard, but has some differences.
    If the <literal>ON COMMIT</> clause is omitted, SQL specifies that the
    default behavior is <literal>ON COMMIT DELETE ROWS</>.  However, the
    default behavior in <productname>PostgreSQL</productname> is
    <literal>ON COMMIT PRESERVE ROWS</literal>.  The <literal>ON COMMIT
    DROP</literal> option does not exist in SQL.
   </para>
  </refsect2>

  <refsect2>
   <title>Column Check Constraints</title>

   <para>
    The SQL standard says that <literal>CHECK</> column constraints
    can only refer to the column they apply to; only <literal>CHECK</>
    table constraints can refer to multiple columns.
    <productname>PostgreSQL</productname> does not enforce this
    restriction; it treats column and table check constraints alike.
   </para>
  </refsect2>

  <refsect2>
   <title><literal>NULL</literal> <quote>Constraint</quote></title>

   <para>
    The <literal>NULL</> <quote>constraint</quote> (actually a
    non-constraint) is a <productname>PostgreSQL</productname>
    extension to the SQL standard that is included for compatibility with some
    other database systems (and for symmetry with the <literal>NOT
    NULL</literal> constraint).  Since it is the default for any
    column, its presence is simply noise.
   </para>
  </refsect2>

  <refsect2>
   <title>Inheritance</title>

   <para>
    Multiple inheritance via the <literal>INHERITS</literal> clause is
    a <productname>PostgreSQL</productname> language extension.
    SQL:1999 and later define single inheritance using a
    different syntax and different semantics.  SQL:1999-style
    inheritance is not yet supported by
    <productname>PostgreSQL</productname>.
   </para>
  </refsect2>

  <refsect2>
   <title>Zero-column tables</title>

   <para>
    <productname>PostgreSQL</productname> allows a table of no columns
    to be created (for example, <literal>CREATE TABLE foo();</>).  This
    is an extension from the SQL standard, which does not allow zero-column
    tables.  Zero-column tables are not in themselves very useful, but
    disallowing them creates odd special cases for <command>ALTER TABLE
    DROP COLUMN</>, so it seems cleaner to ignore this spec restriction.
   </para>
  </refsect2>

  <refsect2>
   <title><literal>WITH</> clause</title>

   <para>
    The <literal>WITH</> clause is a <productname>PostgreSQL</productname>
    extension; neither storage parameters nor OIDs are in the standard.
   </para>
  </refsect2>

  <refsect2>
   <title>Tablespaces</title>

   <para>
    The <productname>PostgreSQL</productname> concept of tablespaces is not
    part of the standard.  Hence, the clauses <literal>TABLESPACE</literal>
    and <literal>USING INDEX TABLESPACE</literal> are extensions.
   </para>
  </refsect2>
 </refsect1>


 <refsect1>
  <title>See Also</title>

  <simplelist type="inline">
   <member><xref linkend="sql-altertable" endterm="sql-altertable-title"></member>
   <member><xref linkend="sql-droptable" endterm="sql-droptable-title"></member>
   <member><xref linkend="sql-createtablespace" endterm="sql-createtablespace-title"></member>
  </simplelist>
 </refsect1>
</refentry>