Home • Explore • Blog • CI
☰



postgresql
12th chunk of `doc/src/sgml/ref/alter_table.sgml`
605504c811f399d62e3d2f9c8c37f058e19fecd4cb1e00ec0000000100000fa1
 not-null constraint that was
      previously created as <literal>NOT VALID</literal>, by scanning the
      table to ensure there are no rows for which the constraint is not
      satisfied.  If the constraint is not enforced, an error is thrown.
      Nothing happens if the constraint is already marked valid.
      (See <xref linkend="sql-altertable-notes"/> below for an explanation
      of the usefulness of this command.)
     </para>
     <para>
      This command acquires a <literal>SHARE UPDATE EXCLUSIVE</literal> lock.
     </para>
    </listitem>
   </varlistentry>

   <varlistentry id="sql-altertable-desc-drop-constraint">
    <term><literal>DROP CONSTRAINT [ IF EXISTS ]</literal></term>
    <listitem>
     <para>
      This form drops the specified constraint on a table, along with
      any index underlying the constraint.
      If <literal>IF EXISTS</literal> is specified and the constraint
      does not exist, no error is thrown. In this case a notice is issued instead.
     </para>
    </listitem>
   </varlistentry>

   <varlistentry id="sql-altertable-desc-disable-enable-trigger">
    <term><literal>DISABLE</literal>/<literal>ENABLE [ REPLICA | ALWAYS ] TRIGGER</literal></term>
    <listitem>
     <para>
      These forms configure the firing of trigger(s) belonging to the table.
      A disabled trigger is still known to the system, but is not executed
      when its triggering event occurs.  (For a deferred trigger, the enable
      status is checked when the event occurs, not when the trigger function
      is actually executed.)  One can disable or enable a single
      trigger specified by name, or all triggers on the table, or only
      user triggers (this option excludes internally generated constraint
      triggers, such as those that are used to implement foreign key
      constraints or deferrable uniqueness and exclusion constraints).
      Disabling or enabling internally generated constraint triggers
      requires superuser privileges; it should be done with caution since
      of course the integrity of the constraint cannot be guaranteed if the
      triggers are not executed.
     </para>

     <para>
      The trigger firing mechanism is also affected by the configuration
      variable <xref linkend="guc-session-replication-role"/>. Simply enabled
      triggers (the default) will fire when the replication role is <quote>origin</quote>
      (the default) or <quote>local</quote>. Triggers configured as <literal>ENABLE
      REPLICA</literal> will only fire if the session is in <quote>replica</quote>
      mode, and triggers configured as <literal>ENABLE ALWAYS</literal> will
      fire regardless of the current replication role.
     </para>

     <para>
      The effect of this mechanism is that in the default configuration,
      triggers do not fire on replicas.  This is useful because if a trigger
      is used on the origin to propagate data between tables, then the
      replication system will also replicate the propagated data; so the
      trigger should not fire a second time on the replica, because that would
      lead to duplication.  However, if a trigger is used for another purpose
      such as creating external alerts, then it might be appropriate to set it
      to <literal>ENABLE ALWAYS</literal> so that it is also fired on
      replicas.
     </para>

     <para>
      When this command is applied to a partitioned table, the states of
      corresponding clone triggers in the partitions are updated too,
      unless <literal>ONLY</literal> is specified.
     </para>

     <para>
      This command acquires a <literal>SHARE ROW EXCLUSIVE</literal> lock.
     </para>
    </listitem>
   </varlistentry>

   <varlistentry id="sql-altertable-desc-disable-enable-rule">
    <term><literal>DISABLE</literal>/<literal>ENABLE [ REPLICA | ALWAYS ] RULE</literal></term>
    <listitem>
     <para>
      These forms configure the firing of rewrite rules belonging to the table.
      A
Title: ALTER TABLE: Constraint Management (Validation & Dropping) and Trigger Control (Enable/Disable)
Summary
This section details ALTER TABLE functionalities for constraint validation, allowing the validation of foreign key, check, or not-null constraints that were previously created as NOT VALID by scanning the table. It also explains how to drop constraints, including conditional dropping with IF EXISTS. Furthermore, it describes how to enable or disable triggers on a table, differentiating between user triggers and internally generated constraint triggers, and how replication roles (origin, local, replica) affect trigger firing, including ENABLE REPLICA and ENABLE ALWAYS options, and the implications for partitioned tables.