Home • Explore • Blog • CI
☰



postgresql
10th chunk of `doc/src/sgml/ref/alter_table.sgml`
9612e4939f4627857c9bc86a99857fd790444f77a31f42690000000100000fa1
 <varlistentry id="sql-altertable-desc-add-table-constraint">
    <term><literal>ADD <replaceable class="parameter">table_constraint</replaceable> [ NOT VALID ]</literal></term>
    <listitem>
     <para>
      This form adds a new constraint to a table using the same constraint
      syntax as <link linkend="sql-createtable"><command>CREATE TABLE</command></link>, plus the option <literal>NOT
      VALID</literal>, which is currently only allowed for foreign key,
      <literal>CHECK</literal> constraints and not-null constraints.
     </para>

     <para>
      Normally, this form will cause a scan of the table to verify that all
      existing rows in the table satisfy the new constraint.  But if
      the <literal>NOT VALID</literal> option is used, this
      potentially-lengthy scan is skipped.  The constraint will still be
      enforced against subsequent inserts or updates (that is, they'll fail
      unless there is a matching row in the referenced table, in the case
      of foreign keys, or they'll fail unless the new row matches the
      specified check condition).  But the
      database will not assume that the constraint holds for all rows in
      the table, until it is validated by using the <literal>VALIDATE
      CONSTRAINT</literal> option.
      See <xref linkend="sql-altertable-notes"/> below for more information
      about using the <literal>NOT VALID</literal> option.
     </para>

     <para>
      Although most forms of <literal>ADD
      <replaceable class="parameter">table_constraint</replaceable></literal>
      require an <literal>ACCESS EXCLUSIVE</literal> lock, <literal>ADD
      FOREIGN KEY</literal> requires only a <literal>SHARE ROW
      EXCLUSIVE</literal> lock.  Note that <literal>ADD FOREIGN KEY</literal>
      also acquires a <literal>SHARE ROW EXCLUSIVE</literal> lock on the
      referenced table, in addition to the lock on the table on which the
      constraint is declared.
     </para>

     <para>
      Additional restrictions apply when unique or primary key constraints
      are added to partitioned tables; see <link linkend="sql-createtable"><command>CREATE TABLE</command></link>.
     </para>

    </listitem>
   </varlistentry>

   <varlistentry id="sql-altertable-desc-add-table-constraint-using-index">
    <term><literal>ADD <replaceable class="parameter">table_constraint_using_index</replaceable></literal></term>
    <listitem>
     <para>
      This form adds a new <literal>PRIMARY KEY</literal> or <literal>UNIQUE</literal>
      constraint to a table based on an existing unique index.  All the
      columns of the index will be included in the constraint.
     </para>

     <para>
      The index cannot have expression columns nor be a partial index.
      Also, it must be a b-tree index with default sort ordering.  These
      restrictions ensure that the index is equivalent to one that would be
      built by a regular <literal>ADD PRIMARY KEY</literal> or <literal>ADD UNIQUE</literal>
      command.
     </para>

     <para>
      If <literal>PRIMARY KEY</literal> is specified, and the index's columns are not
      already marked <literal>NOT NULL</literal>, then this command will attempt to
      do <literal>ALTER COLUMN SET NOT NULL</literal> against each such column.
      That requires a full table scan to verify the column(s) contain no
      nulls.  In all other cases, this is a fast operation.
     </para>

     <para>
      If a constraint name is provided then the index will be renamed to match
      the constraint name.  Otherwise the constraint will be named the same as
      the index.
     </para>

     <para>
      After this command is executed, the index is <quote>owned</quote> by the
      constraint, in the same way as if the index had been built by
      a regular <literal>ADD PRIMARY KEY</literal> or <literal>ADD UNIQUE</literal>
      command.  In particular, dropping the constraint will make the index
      disappear too.
     </para>

     <para>
Title: ALTER TABLE: Adding Constraints with and without Validation
Summary
This section describes the ALTER TABLE command's ADD CONSTRAINT functionality, including the use of the NOT VALID option to skip initial constraint validation on existing rows. Constraints added with NOT VALID are still enforced on new inserts/updates, but the database doesn't assume they hold for existing rows until validated. Also described is the ADD CONSTRAINT USING INDEX variant, allowing the creation of PRIMARY KEY or UNIQUE constraints based on existing indexes, with specific restrictions on index types and columns. Adding a PRIMARY KEY may involve ALTER COLUMN SET NOT NULL, which requires a table scan.