Home Explore Blog CI



postgresql

28th chunk of `doc/src/sgml/ref/alter_table.sgml`
8b28cf2b413a0b9669d962e324bacf7eac269eb7acfe4c1b00000001000008e0
 INHERIT;
</programlisting>
   (The check constraint will not be inherited by future children, either.)
  </para>

  <para>
   To remove a check constraint from a table and all its children:
<programlisting>
ALTER TABLE distributors DROP CONSTRAINT zipchk;
</programlisting>
  </para>

  <para>
   To remove a check constraint from one table only:
<programlisting>
ALTER TABLE ONLY distributors DROP CONSTRAINT zipchk;
</programlisting>
   (The check constraint remains in place for any child tables.)
  </para>

  <para>
   To add a foreign key constraint to a table:
<programlisting>
ALTER TABLE distributors ADD CONSTRAINT distfk FOREIGN KEY (address) REFERENCES addresses (address);
</programlisting>
  </para>

  <para>
   To add a foreign key constraint to a table with the least impact on other work:
<programlisting>
ALTER TABLE distributors ADD CONSTRAINT distfk FOREIGN KEY (address) REFERENCES addresses (address) NOT VALID;
ALTER TABLE distributors VALIDATE CONSTRAINT distfk;
</programlisting>
  </para>

  <para>
   To add a (multicolumn) unique constraint to a table:
<programlisting>
ALTER TABLE distributors ADD CONSTRAINT dist_id_zipcode_key UNIQUE (dist_id, zipcode);
</programlisting>
  </para>

  <para>
   To add an automatically named primary key constraint to a table, noting
   that a table can only ever have one primary key:
<programlisting>
ALTER TABLE distributors ADD PRIMARY KEY (dist_id);
</programlisting>
  </para>

  <para>
   To move a table to a different tablespace:
<programlisting>
ALTER TABLE distributors SET TABLESPACE fasttablespace;
</programlisting>
  </para>

  <para>
   To move a table to a different schema:
<programlisting>
ALTER TABLE myschema.distributors SET SCHEMA yourschema;
</programlisting>
  </para>

  <para>
   To recreate a primary key constraint, without blocking updates while the
   index is rebuilt:
<programlisting>
CREATE UNIQUE INDEX CONCURRENTLY dist_id_temp_idx ON distributors (dist_id);
ALTER TABLE distributors DROP CONSTRAINT distributors_pkey,
    ADD CONSTRAINT distributors_pkey PRIMARY KEY USING INDEX dist_id_temp_idx;
</programlisting></para>

  <para>
   To attach a partition to a range-partitioned table:
<programlisting>
ALTER TABLE measurement
    ATTACH PARTITION measurement_y2016m07

Title: ALTER TABLE Examples: Constraints, Keys, Tablespaces, Schemas, and Partitions
Summary
The provided examples demonstrate ALTER TABLE usage for adding and removing check constraints (with and without inheritance), adding foreign key constraints (including NOT VALID option), adding unique and primary key constraints, moving tables to different tablespaces and schemas, recreating primary keys without blocking updates, and attaching a partition to a range-partitioned table.