create_table.sgml

关系型数据库 Postgresql 6.5.2

    The NOT NULL constraint specifies a rule that a column may
    contain only non-null values. 
    This is a column constraint only, and not allowed
    as a table constraint.
   </para>

   <refsect3 id="R3-SQL-NOTNULL-1">
    <refsect3info>
     <date>1998-09-11</date>
    </refsect3info>
    <title>
     Outputs
    </title>
    <para>
     <variablelist>
      <varlistentry>
       <term><replaceable>status</replaceable></term>
       <listitem>
	<para>
	 <variablelist>
	  <varlistentry>
	   <term><computeroutput>
ERROR:  ExecAppend: Fail to add null value in not null attribute "<replaceable class="parameter">column</replaceable>".
	    </computeroutput></term>
	   <listitem>
	    <para>
	     This error occurs at runtime if one tries to insert a null value
	     into a column which has a NOT NULL constraint.
	    </para>
	   </listitem>
	  </varlistentry>
	 </variablelist>
	</para>
       </listitem>
      </varlistentry>
     </variablelist> 
    </para>
   </refsect3>

   <refsect3 id="R3-SQL-NOTNULL-2">
    <refsect3info>
     <date>1998-09-11</date>
    </refsect3info>
    <title>
     Description
    </title>
    <para>
    </para>
   </refsect3>

   <refsect3 id="R3-SQL-NOTNULL-3">
    <refsect3info>
     <date>1998-09-11</date>
    </refsect3info>
    <title>
     Usage
    </title>

    <para>
     Define two NOT NULL column constraints on the table
     <classname>distributors</classname>,
     one of which being a named constraint:

     <programlisting>
CREATE TABLE distributors (
    did      DECIMAL(3) CONSTRAINT no_null NOT NULL,
    name     VARCHAR(40) NOT NULL
);
     </programlisting>
    </para>
   </refsect3>
  </refsect2>

  <refsect2 id="R2-SQL-UNIQUECLAUSE-1">
   <refsect2info>
    <date>1998-09-11</date>
   </refsect2info>
   <title>
    UNIQUE Constraint
   </title>
   <synopsis>
[ CONSTRAINT <replaceable class="parameter">name</replaceable> ] UNIQUE
   </synopsis>

   <refsect3>
    <title>Inputs</title>
    <para>

     <variablelist>
      <varlistentry>
       <term>CONSTRAINT <replaceable class="parameter">name</replaceable></term>
       <listitem>
	<para>
	 An arbitrary label given to a constraint.
	</para>
       </listitem>
      </varlistentry>
     </variablelist>
    </para>
   </refsect3>
   
   <refsect3>
    <title>Outputs</title>
    <para>
     <variablelist>
      <varlistentry>
       <term><replaceable>status</replaceable></term>
       <listitem>
	<para>
	 <variablelist>
	  <varlistentry>
	   <term><computeroutput>
ERROR: Cannot insert a duplicate key into a unique index.
	    </computeroutput></term>
	   <listitem>
	    <para>
	     This error occurs at runtime if one tries to insert a
	     duplicate value into a column.
	    </para>
	   </listitem>
	  </varlistentry>
	 </variablelist>
	</para>
       </listitem>
      </varlistentry>
     </variablelist>
    </para>
   </refsect3>

   <refsect3>
    <title>
     Description
    </title>

    <para>
     The UNIQUE constraint specifies a rule that a group of one or
     more distinct columns of a table may contain only unique values.
    </para>
    <para>
     The column definitions of the specified columns do not have to
     include a NOT NULL constraint to be included in a UNIQUE
     constraint.  Having more than one null value in a column without a
     NOT NULL constraint, does not violate a UNIQUE constraint.
     (This deviates from the <acronym>SQL92</acronym> definition, but
     is a more sensible convention. See the section on compatibility
     for more details.).
    </para>
    <para>
     Each UNIQUE column constraint must name a column that is
     different from the set of columns named by any other UNIQUE or
     PRIMARY KEY constraint defined for the table.
    </para>
    <note>
     <para>
      <productname>Postgres</productname> automatically creates a unique
      index for each UNIQUE constraint, to assure
      data integrity. See CREATE INDEX for more information.
     </para>
    </note>
   </refsect3>

   <refsect3 id="R3-SQL-UNIQUECLAUSE-3">
    <title>
     Usage
    </title>

    <para>
     Defines a UNIQUE column constraint for the table distributors.
     UNIQUE column constraints can only be defined on one column
     of the table:
     <programlisting>
CREATE TABLE distributors (
    did      DECIMAL(3),
    name     VARCHAR(40) UNIQUE
);
  </programlisting>

     which is equivalent to the following specified as a table constraint:
     <programlisting>
CREATE TABLE distributors (
    did      DECIMAL(3),
    name     VARCHAR(40),
    UNIQUE(name)
);
     </programlisting>
    </para>
   </refsect3>
  </refsect2>

  <refsect2 id="R2-SQL-CHECK-1">
   <refsect2info>
    <date>1998-09-11</date>
   </refsect2info>
   <title>
    The CHECK Constraint
   </title>
   <synopsis>
[ CONSTRAINT <replaceable class="parameter">name</replaceable> ] CHECK
    ( <replaceable>condition</replaceable> [, ...] ) 
   </synopsis>
   <refsect3 id="R3-SQL-CHECK-1">
    <title>Inputs</title>
    <para>

     <variablelist>
      <varlistentry>
       <term><replaceable class="parameter">name</replaceable></term>
       <listitem>
	<para>
	 An arbitrary name given to a constraint.
	</para>
       </listitem>
      </varlistentry>
      <varlistentry>
       <term><replaceable>condition</replaceable></term>
       <listitem>
	<para>
	 Any valid conditional expression evaluating to a boolean result.
	</para>
       </listitem>
      </varlistentry>
     </variablelist>
    </para>
   </refsect3>

   <refsect3 id="R3-SQL-CHECK-2">
    <refsect3info>
     <date>1998-09-11</date>
    </refsect3info>
    <title>
     Outputs
    </title>
    <para>

     <variablelist>
      <varlistentry>
       <term><replaceable>status</replaceable></term>
       <listitem>
	<para>

	 <variablelist>
	  <varlistentry>
	   <term><computeroutput>
ERROR:  ExecAppend: rejected due to CHECK constraint "<replaceable class="parameter">table_column</replaceable>".
	    </computeroutput></term>
	   <listitem>
	    <para>
	     This error occurs at runtime if one tries to insert an illegal
	     value into a column subject to a CHECK constraint.
	    </para>
	   </listitem>
	  </varlistentry>
	 </variablelist>
	</para>
       </listitem>
      </varlistentry>
     </variablelist>
    </para>
   </refsect3>

   <refsect3>
    <title>Description</title>
    <para>
     The CHECK constraint specifies a restriction on allowed values
     within a column.
     The CHECK constraint is also allowed as a table constraint.
    </para>
    <para>
     The SQL92 CHECK column constraints can only be defined on, and
     refer to, one column of the table. <productname>Postgres</productname>
     does not have
     this restriction.
    </para>
   </refsect3>
  </refsect2>
  
  <refsect2 id="R2-SQL-PRIMARYKEY-1">
   <refsect2info>
    <date>1998-09-11</date>
   </refsect2info>
   <title>
    PRIMARY KEY Constraint
   </title>
   <synopsis>
[ CONSTRAINT <replaceable class="PARAMETER">name</replaceable> ] PRIMARY KEY 
   </synopsis>

   <refsect3>
    <title>Inputs</title>
    <para>
     <variablelist>
      <varlistentry>
       <term>CONSTRAINT <replaceable class="PARAMETER">name</replaceable></term>
       <listitem>
	<para>
	 An arbitrary name for the constraint.
	</para>
       </listitem>
      </varlistentry>
     </variablelist>
    </para>
   </refsect3>

   <refsect3>
    <title>Outputs</title>
    <variablelist>
     <varlistentry>
      <term><computeroutput>
ERROR: Cannot insert a duplicate key into a unique index.
       </computeroutput></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>
   </refsect3>

   <refsect3>
    <title>Description</title>
    <para>
     The PRIMARY KEY column constraint specifies that a column of a table
     may contain only unique
     (non-duplicate), non-NULL values. The definition of
     the specified column does not have to include an explicit NOT NULL
     constraint to be included in a PRIMARY KEY constraint. 
    </para>
    <para>
     Only one PRIMARY KEY can be specified for a table.
    </para>
   </refsect3>
   
   <refsect3 id="R3-SQL-PRIMARYKEY-3">
    <refsect3info>
     <date>1998-09-11</date>
    </refsect3info>
    <title>
     Notes
    </title>
    <para>
     <productname>Postgres</productname> automatically creates
     a unique index to assure
     data integrity. (See CREATE INDEX statement)
    </para>
    <para>
     The PRIMARY KEY constraint should name a set of columns that is
     different from other sets of columns named by any UNIQUE constraint
     defined for the same table, since it will result in duplication
     of equivalent indexes and unproductive additional runtime overhead.
     However, <productname>Postgres</productname> does not specifically
     disallow this.
    </para>
   </refsect3>
  </refsect2>
 </refsect1>
 
 <refsect1 id="R1-SQL-TABLECONSTRAINT-1">
  <refsect1info>
   <date>1998-09-11</date>
  </refsect1info>
  <title>
   Table CONSTRAINT Clause
  </title>
  <para>
   <synopsis>
[ CONSTRAINT name ] { PRIMARY KEY |  UNIQUE } ( <replaceable class="parameter">column</replaceable> [, ...] )
[ CONSTRAINT name ] CHECK ( <replaceable>constraint</replaceable> )
   </synopsis>
  </para>
  <refsect2 id="R2-SQL-TABLECONSTRAINT-1">
   <refsect2info>
    <date>1998-09-11</date>
   </refsect2info>
   <title>
    Inputs
   </title>

   <para>

    <variablelist>
     <varlistentry>
      <term>CONSTRAINT <replaceable class="parameter">name</replaceable></term>
      <listitem>
       <para>
	An arbitrary name given to an integrity constraint.
       </para>
      </listitem>
     </varlistentry>
     <varlistentry>
      <term><replaceable class="parameter">column</replaceable> [, ...]</term>
      <listitem>
       <para>
	The column name(s) for which to define a unique index
	and, for PRIMARY KEY, a NOT NULL constraint.
       </para>
      </listitem>
     </varlistentry>
     <varlistentry>
      <term>CHECK ( <replaceable class="parameter">constraint</replaceable> )</term>
      <listitem>
       <para>
	A boolean expression to be evaluated as the constraint.
       </para>
      </listitem>
     </varlistentry>
    </variablelist>
   </para>
  </refsect2>
    
  <refsect2 id="R2-SQL-TABLECONSTRAINT-2">
   <refsect2info>
    <date>1998-09-11</date>
   </refsect2info>
   <title>
    Outputs
   </title>
   
   <para>
    The possible outputs for the table constraint clause are the same
    as for the corresponding portions of the column constraint clause.
   </para>
  </refsect2>
  
  <refsect2 id="R2-SQL-TABLECONSTRAINT-3">
   <refsect2info>
    <date>1998-09-11</date>
   </refsect2info>
   <title>
    Description
   </title>
   
   <para>
    A table constraint is an integrity constraint defined on one or
    more columns of a base table. The four variations of "Table
    Constraint" are:
    <simplelist columns="1">
     <member>UNIQUE</member>
     <member>CHECK</member>
     <member>PRIMARY KEY</member>
     <member>FOREIGN KEY</member>
    </simplelist>
   </para>
   <note>
    <para>
     <productname>Postgres</productname> does not yet 
     (as of version 6.5) support FOREIGN KEY
     integrity constraints. The parser understands the FOREIGN KEY syntax,
     but only prints a notice and otherwise ignores the clause.
     Foreign keys may be partially emulated by triggers (See the CREATE TRIGGER
     statement).
    </para>
   </note>
  </refsect2>
 
  <refsect2 id="R2-SQL-UNIQUECLAUSE-4">
   <refsect2info>
    <date>1998-09-11</date>
   </refsect2info>
   <title>
    UNIQUE Constraint
   </title>
   <para>
    <synopsis>
[ CONSTRAINT <replaceable class="parameter">name</replaceable> ] UNIQUE ( <replaceable class="parameter">column</replaceable> [, ...] )
    </synopsis>
   </para>
   <refsect3>
    <title>Inputs</title>
    <variablelist>
     <varlistentry>
      <term>CONSTRAINT <replaceable class="parameter">name</replaceable></term>
      <listitem>
       <para>
	An arbitrary name given to a constraint.
       </para>
      </listitem>
     </varlistentry>
     <varlistentry>
      <term><replaceable class="parameter">column</replaceable></term>
      <listitem>
       <para>
	A name of a column in a table.
       </para>
      </listitem>
     </varlistentry>
    </variablelist>
   </refsect3>

   <refsect3>
    <title>Outputs</title>
    <para>
     <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 error occurs at runtime if one tries to insert a
	     duplicate value into a column.
	    </para>
	   </listitem>
	  </varlistentry>
	 </variablelist>
	</para>
       </listitem>
      </varlistentry>
     </variablelist>
    </para>
   </refsect3>
   
   <refsect3>
    <title>
     Description
    </title>
    
    <para>
     The UNIQUE constraint specifies a rule that a group of one or
     more distinct columns of a table may contain only unique values.
     The behavior of the UNIQUE table constraint is the same as that for column
     constraints, with the additional capability to span multiple columns.
    </para>
    <para>
     See the section on the UNIQUE column constraint for more details.
    </para>
   </refsect3>

   <refsect3 id="R3-SQL-UNIQUECLAUSE-4">
    <title>
     Usage
    </title>
    
    <para>
     Define a UNIQUE table constraint for the table distributors:
     <programlisting>
CREATE TABLE distributors (
    did      DECIMAL(03),
    name     VARCHAR(40),
    UNIQUE(name)
);
     </programlisting>
    </para>
   </refsect3>
  </refsect2>

  <refsect2 id="R2-SQL-PRIMARYKEY-4">
   <refsect2info>
    <date>1998-09-11</date>
   </refsect2info>
   <title>
    PRIMARY KEY Constraint
   </title>
   <para>
    <synopsis>
[ CONSTRAINT <replaceable class="PARAMETER">name</replaceable> ] PRIMARY KEY ( <replaceable class="PARAMETER">column</replaceable> [, ...] ) 
    </synopsis>
   </para>
   <refsect3>
    <title>Inputs</title>
    <para>

     <variablelist>