Home • Explore • Blog • CI
☰



postgresql
18th chunk of `doc/src/sgml/ref/create_table.sgml`
5ac37a9006d9f93c0e36b5eb4a8670c38f9c5d69ce41ccd90000000100000fa1
 </para>

     <para>
      For the purpose of a unique constraint, null values are not
      considered equal, unless <literal>NULLS NOT DISTINCT</literal> is
      specified.
     </para>

     <para>
      Each unique constraint should name a set of columns that is
      different from the set of columns named by any other unique or
      primary key constraint defined for the table.  (Otherwise, redundant
      unique constraints will be discarded.)
     </para>

     <para>
      When establishing a unique constraint for a multi-level partition
      hierarchy, all the columns in the partition key of the target
      partitioned table, as well as those of all its descendant partitioned
      tables, must be included in the constraint definition.
     </para>

     <para>
      Adding a unique constraint will automatically create a unique btree
      index on the column or group of columns used in the constraint.  But if
      the constraint includes a <literal>WITHOUT OVERLAPS</literal> clause, it
      will use a GiST index.  The created index has the same name as the
      unique constraint.
     </para>

     <para>
      The optional <literal>INCLUDE</literal> clause adds to that index
      one or more columns that are simply <quote>payload</quote>: uniqueness
      is not enforced on them, and the index cannot be searched on the basis
      of those columns.  However they can be retrieved by an index-only scan.
      Note that although the constraint is not enforced on included columns,
      it still depends on them.  Consequently, some operations on such columns
      (e.g., <literal>DROP COLUMN</literal>) can cause cascaded constraint and
      index deletion.
     </para>
    </listitem>
   </varlistentry>

   <varlistentry id="sql-createtable-parms-primary-key">
    <term><literal>PRIMARY KEY</literal> (column constraint)</term>
    <term><literal>PRIMARY KEY ( <replaceable class="parameter">column_name</replaceable> [, ... ] [, <replaceable class="parameter">column_name</replaceable> WITHOUT OVERLAPS ] )</literal>
    <optional> <literal>INCLUDE ( <replaceable class="parameter">column_name</replaceable> [, ...])</literal> </optional> (table constraint)</term>
    <listitem>
     <para>
      The <literal>PRIMARY KEY</literal> constraint specifies that a column or
      columns of a table can contain only unique (non-duplicate), nonnull
      values. Only one primary key can be specified for a table, whether as a
      column constraint or a table constraint.
     </para>

     <para>
      The primary key constraint should name a set of columns that is
      different from the set of columns named by any unique
      constraint defined for the same table.  (Otherwise, the unique
      constraint is redundant and will be discarded.)
     </para>

     <para>
      <literal>PRIMARY KEY</literal> enforces the same data constraints as
      a combination of <literal>UNIQUE</literal> and <literal>NOT
      NULL</literal>.  However,
      identifying a set of columns as the primary key also provides metadata
      about the design of the schema, since a primary key implies that other
      tables can rely on this set of columns as a unique identifier for rows.
     </para>

     <para>
      When placed on a partitioned table, <literal>PRIMARY KEY</literal>
      constraints share the restrictions previously described
      for <literal>UNIQUE</literal> constraints.
     </para>

     <para>
      Adding a <literal>PRIMARY KEY</literal> constraint will automatically
      create a unique btree index on the column or group of columns used in
      the constraint, or GiST if <literal>WITHOUT OVERLAPS</literal> was
      specified.
     </para>

     <para>
      The optional <literal>INCLUDE</literal> clause adds to that index
      one or more columns that are simply <quote>payload</quote>: uniqueness
      is not enforced on them, and the index cannot be searched on the basis
      of those columns.  However they can
Title: CREATE TABLE Parameters: UNIQUE and PRIMARY KEY Constraints Details
Summary
This section continues the explanation of the `UNIQUE` constraint and introduces the `PRIMARY KEY` constraint in `CREATE TABLE`. The `UNIQUE` constraint section details how adding a `UNIQUE` constraint automatically creates a unique btree index, or a GiST index if `WITHOUT OVERLAPS` is specified. The optional `INCLUDE` clause allows adding non-unique 'payload' columns to the index. The `PRIMARY KEY` constraint is then described as a combination of `UNIQUE` and `NOT NULL` constraints, providing metadata about the schema design. It also creates a unique btree or GiST index, and supports the `INCLUDE` clause.