create_table.sgml

关系型数据库 Postgresql 6.5.2

      <varlistentry>
       <term>CONSTRAINT <replaceable class="PARAMETER">name</replaceable></term>
       <listitem>
	<para>
	 An arbitrary name for the constraint.
	</para>
       </listitem>
      </varlistentry>
      <varlistentry>
       <term><replaceable class="PARAMETER">column</replaceable> [, ...]</term>
       <listitem>
	<para>
	 The names of one or more columns in the table.
	</para>
       </listitem>
      </varlistentry>
     </variablelist>
    </para>
   </refsect3>
   
   <refsect3>
    <title>Outputs</title>
    <variablelist>
     <varlistentry>
      <term><replaceable>status</replaceable></term>
      <listitem>
       <para>
	<variablelist>
	 <varlistentry>
	  <term>ERROR: Cannot insert a duplicate key into a unique index.</term>
	  <listitem>
	   <para>
	    This occurs at run-time if one tries to insert a duplicate value into
	    a column subject to a PRIMARY KEY constraint.
	   </para>
	  </listitem>
	 </varlistentry>
	</variablelist>
       </para>
      </listitem>
     </varlistentry>
    </variablelist>
   </refsect3>
   
   <refsect3>
    <title>Description</title>
    <para>
     The PRIMARY KEY constraint specifies a rule that a group of one
     or more distinct columns of a table may contain only unique,
     (non duplicate), non-null values. The column definitions of
     the specified columns do not have to include a NOT NULL
     constraint to be included in a PRIMARY KEY constraint.
    </para>

    <para>
     The PRIMARY KEY table constraint is similar to that for column constraints,
     with the additional capability of encompassing multiple columns.
    </para>
    <para>
     Refer to the section on the PRIMARY KEY column constraint for more
     information.
    </para>
   </refsect3>

  </refsect2>

 </refsect1>
 
 <refsect1 id="R1-SQL-CREATETABLE-2">
  <title>
   Usage
  </title>
  <para>
   Create table films and table distributors:

   <programlisting>
CREATE TABLE films (
     code      CHARACTER(5) CONSTRAINT firstkey PRIMARY KEY,
     title     CHARACTER VARYING(40) NOT NULL,
     did       DECIMAL(3) NOT NULL,
     date_prod DATE,
     kind      CHAR(10),
     len       INTERVAL HOUR TO MINUTE
);
   </programlisting>

   <programlisting>
CREATE TABLE distributors (
     did      DECIMAL(03) 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 (
          vector INT[][]
          );
   </programlisting>
  </para>

  <para>
   Define a UNIQUE table constraint for the table films.
   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       DECIMAL(03),
    date_prod DATE,
    kind      CHAR(10),
    len       INTERVAL HOUR TO MINUTE,
    CONSTRAINT production UNIQUE(date_prod)
);
   </programlisting>
  </para>
  
  <para>
   Define a CHECK column constraint:

   <programlisting>
CREATE TABLE distributors (
    did      DECIMAL(3) CHECK (did > 100),
    name     VARCHAR(40)
);
   </programlisting>
  </para>

  <para>
   Define a CHECK table constraint:

   <programlisting>
CREATE TABLE distributors (
    did      DECIMAL(3),
    name     VARCHAR(40)
    CONSTRAINT con1 CHECK (did > 100 AND name > '')
);
   </programlisting>
  </para>
 
  <para>
   Define a PRIMARY KEY table constraint for the table films.
   PRIMARY KEY table constraints can be defined on one or more
   columns of the table:

   <programlisting>
CREATE TABLE films (
    code      CHAR(05),
    title     VARCHAR(40),
    did       DECIMAL(03),
    date_prod DATE,
    kind      CHAR(10),
    len       INTERVAL HOUR TO MINUTE,
    CONSTRAINT code_title PRIMARY KEY(code,title)
);
   </programlisting>
  </para>

  <para>
   Defines a PRIMARY KEY column constraint for table distributors.
   PRIMARY KEY column constraints can only be defined on one column
   of the table (the following two examples are equivalent):

   <programlisting>
CREATE TABLE distributors (
    did      DECIMAL(03),
    name     CHAR VARYING(40),
    PRIMARY KEY(did)
); 
   </programlisting>

   <programlisting>
CREATE TABLE distributors (
    did      DECIMAL(03) PRIMARY KEY,
    name     VARCHAR(40)
);
   </programlisting>
  </para>
  
  <refsect2 id="R2-SQL-CREATETABLE-3">
   <refsect2info>
    <date>1998-09-11</date>
   </refsect2info>
   <title>
    Notes
   </title>
   <para>
    CREATE TABLE/INHERITS is a <productname>Postgres</productname>
    language extension.
   </para>
  </refsect2>
  
 </refsect1>
 
 <refsect1 id="R1-SQL-CREATETABLE-3">
  <title>
   Compatibility
  </title>
  
  <refsect2 id="R2-SQL-CREATETABLE-4">
   <refsect2info>
    <date>1998-09-11</date>
   </refsect2info>
   <title>
    SQL92
   </title>
   <para>
    In addition to the locally-visible temporary table, SQL92 also defines a
    CREATE GLOBAL TEMPORARY TABLE statement, and optionally an 
    ON COMMIT clause:
   <synopsis>
CREATE GLOBAL TEMPORARY TABLE <replaceable class="parameter">table</replaceable> ( <replaceable class="parameter">column</replaceable> <replaceable class="parameter">type</replaceable> [
    DEFAULT <replaceable class="parameter">value</replaceable> ] [ CONSTRAINT <replaceable class="parameter">column_constraint</replaceable> ] [, ...] )
    [ CONSTRAINT <replaceable class="parameter">table_constraint</replaceable> ] [ ON COMMIT { DELETE | PRESERVE } ROWS ] 
   </synopsis>
   </para>

   <para>
    For temporary tables, the CREATE GLOBAL TEMPORARY TABLE statement
    names a new table visible to other clients and defines the table's columns and
    constraints. 
   </para>
   <para>
    The optional ON COMMIT clause of CREATE TEMPORARY TABLE
    specifies whether or not the temporary table should be emptied of
    rows whenever COMMIT is executed. If the ON COMMIT clause is
    omitted, the default option, ON COMMIT DELETE ROWS, is assumed. 
   </para>
   <para>
    To create a temporary table:

    <programlisting>
CREATE TEMPORARY TABLE actors (
    id         DECIMAL(03),
    name       VARCHAR(40),
    CONSTRAINT actor_id CHECK (id &lt; 150)
) ON COMMIT DELETE ROWS;
    </programlisting>
   </para>

   <refsect3 id="R3-SQL-UNIQUECLAUSE-1">
    <refsect3info>
     <date>1998-09-11</date>
    </refsect3info>
    <title>
     UNIQUE clause
    </title>
    <para>
     SQL92 specifies some additional capabilities for UNIQUE:
    </para>
    <para>
     Table Constraint definition:

     <synopsis>
[ CONSTRAINT name ] UNIQUE ( column [, ...] )
    [ { INITIALLY DEFERRED | INITIALLY IMMEDIATE } ]
    [ [ NOT ] DEFERRABLE ]
     </synopsis>
    </para>

    <para>
     Column Constraint definition:

     <synopsis>
[ CONSTRAINT name ] UNIQUE
      [ {INITIALLY DEFERRED | INITIALLY IMMEDIATE} ]
      [ [ NOT ] DEFERRABLE ]
     </synopsis>
    </para>
   </refsect3>
   
   <refsect3 id="R3-SQL-NULL-1">
    <refsect3info>
     <date>1998-12-24</date>
    </refsect3info>
    <title>
     NULL clause
    </title>
    <para>
	The NULL "constraint" (actually a non-constraint)
	is a <productname>Postgres</productname> extension to SQL92
	is included for symmetry with the NOT NULL clause. Since it is the default
	for any column, its presence is simply noise.
     <synopsis>
[ CONSTRAINT name ] NULL 
     </synopsis>
    </para>
   </refsect3>
   
   <refsect3 id="R3-SQL-NOTNULL-4">
    <refsect3info>
     <date>1998-09-11</date>
    </refsect3info>
    <title>
     NOT NULL clause
    </title>
    <para>
     
     SQL92 specifies some additional capabilities for NOT NULL:
     <synopsis>
[ CONSTRAINT name ] NOT NULL 
    [ {INITIALLY DEFERRED | INITIALLY IMMEDIATE} ]
    [ [ NOT ] DEFERRABLE ]
     </synopsis>
    </para>
   </refsect3>
   
<!--
I can't figure out why DEFAULT clause is different from what we already have.
Perhaps because CURRENT_USER and CURRENT_DATE have specific types (currently
the "name" type), if you aren't careful then the types won't match up with
the column. Not our problem...
- Thomas 1998-08-16

   <REFSECT3 ID="R3-SQL-DEFAULTCLAUSE-1">
    <REFSECT3INFO>
     <DATE>1998-09-11</DATE>
    </REFSECT3INFO>
    <TITLE>
     DEFAULT clause
    </TITLE>
    <PARA>
     SQL92 specifies some additional capabilities for the DEFAULT clause.
     A DEFAULT clause is used to set the default value for a column
     or a domain.
    </para>
    <synopsis>
     DEFAULT niladic USER function |
             niladic datetime function |
             NULL
    </synopsis>
   </refsect3>
-->

   <refsect3 id="R3-SQL-CONSTRAINT-3">
    <refsect3info>
     <date>1998-09-11</date>
    </refsect3info>
    <title>
     CONSTRAINT clause
    </title>
    <para>
     SQL92 specifies some additional capabilities for constraints,
     and also defines assertions and domain constraints.
     <note>
      <para>
       <productname>Postgres</productname> does not yet support 
       either domains or assertions.
      </para>
     </note>
    </para>
    <para>
     An assertion is a special type of integrity constraint and share
     the same namespace as other constraints.
     However, an assertion is not necessarily dependent on one
     particular base table as constraints are, so SQL-92 provides the
     CREATE ASSERTION statement as an alternate method for defining a
     constraint:
    </para>
    <synopsis>
CREATE ASSERTION name CHECK ( condition )
    </synopsis>
    
    <para>
     Domain constraints are defined by CREATE DOMAIN or ALTER DOMAIN
     statements:
    </para>
    <para>
     Domain constraint: 

     <synopsis>
[ CONSTRAINT name ] CHECK constraint 
    [ {INITIALLY DEFERRED | INITIALLY IMMEDIATE} ]
    [ [ NOT ] DEFERRABLE ]
     </synopsis>
    </para>

    <para>
     Table constraint definition:

     <synopsis>
[ CONSTRAINT name ] { PRIMARY KEY ( <replaceable class="parameter">column</replaceable>, ... ) | FOREIGN KEY constraint | UNIQUE constraint | CHECK constraint }
    [ {INITIALLY DEFERRED | INITIALLY IMMEDIATE} ]
    [ [ NOT ] DEFERRABLE ]
     </synopsis>
    </para>

    <para>
     Column constraint definition:

     <synopsis>
[ CONSTRAINT name ] { NOT NULL | PRIMARY KEY | FOREIGN KEY constraint | UNIQUE | CHECK constraint }  
    [ {INITIALLY DEFERRED | INITIALLY IMMEDIATE} ]
    [ [ NOT ] DEFERRABLE ]
     </synopsis>
    </para>

    <para>
     A CONSTRAINT definition may contain one deferment attribute
     clause and/or one initial constraint mode clause, in any order.
     <variablelist>
      <varlistentry>
       <term>NOT DEFERRABLE</term>
       <listitem>
	<para>
	 means that the Constraint must be checked for
	 violation of its rule after the execution of every SQL statement.
	</para>
       </listitem>
      </varlistentry>
      <varlistentry>
       <term>DEFERRABLE</term>
       <listitem>
	<para>
	 means that checking of the Constraint may be deferred
	 until some later time, but no later than the end of the current
	 transaction.
	</para>
       </listitem>
      </varlistentry>
     </variablelist>
    </para>
    <para>
     The constraint mode for every Constraint always has an initial
     default value which is set for that Constraint at the beginning
     of a transaction.
     <variablelist>
      <varlistentry>
       <term>INITIALLY IMMEDIATE</term>
       <listitem>
	<para>
	 means that, as of the start of the transaction,
	 the Constraint must be checked for violation of its rule after the
	 execution of every SQL statement.
	</para>
       </listitem>
      </varlistentry>
      <varlistentry>
       <term>INITIALLY DEFERRED</term>
       <listitem>
	<para>
	 means that, as of the start of the transaction,
	 checking of the Constraint may be deferred until some later time,
	 but no later than the end of the current transaction.</para>
       </listitem>
      </varlistentry>
     </variablelist>
    </para>
   </refsect3>
   
   
   <refsect3 id="R3-SQL-CHECK-4">
    <refsect3info>
     <date>1998-09-11</date>
    </refsect3info>
    <title>
     CHECK clause
    </title>
    <para>
     SQL92 specifies some additional capabilities for CHECK in either
table or column constraints.
    </para>
<!--
Constraints associated with domains do not need to be mentioned here,
even though it is the case that a domain constraint may possibly
affect a column or a table.
- Thomas 1998-08-16
    <para>
     A CHECK constraint is either a table constraint, a column
     constraint or a domain constraint.
    </para> 
-->
    <para>
     table constraint definition:
     <synopsis>
[ CONSTRAINT name ] CHECK ( VALUE condition ) 
      [ {INITIALLY DEFERRED | INITIALLY IMMEDIATE} ]
      [ [ NOT ] DEFERRABLE ]
     </synopsis>
    </para>

    <para>
     column constraint definition:
    <synopsis>
[ CONSTRAINT name ] CHECK ( VALUE condition ) 
      [ {INITIALLY DEFERRED | INITIALLY IMMEDIATE} ]
      [ [ NOT ] DEFERRABLE ]
    </synopsis>
    </para>
<!--
   <para>
     domain constraint definition: 
    </para>
    <synopsis>
     [ CONSTRAINT name ] 
      CHECK ( VALUE condition ) 
      [ {INITIALLY DEFERRED | INITIALLY IMMEDIATE} ]
      [ [ NOT ] DEFERRABLE ]
    </synopsis>
    <para>
     CHECK domain constraints can be defined in either
     a CREATE DOMAIN statement or an ALTER DOMAIN statement:
    </para>
    <programlisting>
CREATE DOMAIN duration AS SMALLINT 
    CONSTRAINT minutes CHECK (VALUE IN (90,120,180,240)); 

ALTER DOMAIN cities 
    ADD CONSTRAINT new_city CHECK (VALUE LIKE 'L%');
   </programlisting>
-->
   </refsect3>

   <refsect3 id="R3-SQL-PRIMARYKEY-1">
    <refsect3info>
     <date>1998-09-11</date>
    </refsect3info>
    <title>
     PRIMARY KEY clause
    </title>
    <para>
     SQL92 specifies some additional capabilities for PRIMARY KEY:
    </para>
    <para>
     Table Constraint definition:
     <synopsis>
[ CONSTRAINT name ] PRIMARY KEY ( column [, ...] ) 
    [ {INITIALLY DEFERRED | INITIALLY IMMEDIATE} ]
    [ [ NOT ] DEFERRABLE ]
     </synopsis>
    </para>
    <para>
     Column Constraint definition: 
     <synopsis>
[ CONSTRAINT name ] PRIMARY KEY 
    [ {INITIALLY DEFERRED | INITIALLY IMMEDIATE} ]
    [ [ NOT ] DEFERRABLE ]
     </synopsis>
    </para>
   </refsect3>
  </refsect2>
 </refsect1>
</refentry>

<!-- Keep this comment at the end of the file
Local variables:
mode: sgml
sgml-omittag:nil
sgml-shorttag:t
sgml-minimize-attributes:nil
sgml-always-quote-attributes:t
sgml-indent-step:1
sgml-indent-data:t
sgml-parent-document:nil
sgml-default-dtd-file:"../reference.ced"
sgml-exposed-tags:nil
sgml-local-catalogs:"/usr/lib/sgml/catalog"
sgml-local-ecat-files:nil
End:
-->