Alters a constraint.

1  –  Environment

    You can use the ALTER CONSTRAINT statement:

    o  In interactive SQL

    o  Embedded in host language programs

    o  As part of a procedure in an SQL module or other compound
       statement

    o  In dynamic SQL as a statement to be dynamically executed

2  –  Format

  ALTER CONSTRAINT <constraint-name> ---------+
      +---------------------------------------+
      +--+--+--> COMMENT IS +-> '<text-literal>' -+--+--+-->
         |  |               +------- / <----------+  |  |
         |  +--> constraint-attributes --------------+  |
         |  +--> RENAME TO <new-constraint-name> ----+  |
         +-------------------- <------------------------+

  constraint-attributes =

  -+-> DEFERRABLE -------------+------------------------------+-+->
   |                           +-> INITIALLY +-> IMMEDIATE --++ |
   |                                         +-> DEFERRED ---+  |
   +-> NOT DEFERRABLE ---------+-------------------------+------+
   |                           +-> INITIALLY IMMEDIATE --+      |
   +-> INITIALLY IMMEDIATE ----+-------------------+------------+
   |                           +-> DEFERRABLE -----+            |
   |                           +-> NOT DEFERRABLE -+            |
   +-> INITIALLY DEFERRED -----+-------------------+------------+
                               +-> DEFERRABLE -----+

3  –  Arguments

3.1  –  COMMENT_IS

    Adds a comment about the constraint. SQL displays the text of the
    comment when it executes a SHOW CONSTRAINTS statement. Enclose
    the comment in single quotation marks ( ') and separate multiple
    lines in a comment with a slash mark (/).

3.2  –  constraint-attributes

    See the ALTER TABLE statement.

3.3  –  constraint-name

    The name of the table whose definition you want to change.

3.4  –  RENAME_TO

    Changes the name of the constraint being altered. See the the
    RENAME statement for further discussion. If the new name is the
    name of a synonym then an error will be raised.

    The RENAME TO clause requires synonyms be enabled for this
    database. Refer to the ALTER DATABASE statement SYNONYMS ARE
    ENABLED clause. Note that these synonyms may be deleted if they
    are no longer used by database definitions or applications.

4  –  Example

    This example shows how ALTER CONSTRAINT can be used to change the
    constraint attributes and add a comment to a constraint.

    SQL> set dialect 'sql99';
    SQL> attach 'file db$:mf_personnel';
    SQL>
    SQL> create table PERSON
    cont>     (last_name  char(20)
    cont>         constraint MUST_HAVE_LAST_NAME
    cont>             not null
    cont>             deferrable,
    cont>      first_name char(20),
    cont>      birthday   date
    cont>         constraint MUST_BE_IN_PAST
    cont>             check (birthday < current_date)
    cont>             not deferrable,
    cont>      constraint ALL_UNIQUE
    cont>         unique (last_name, first_name, birthday)
    cont>         deferrable initially immediate
    cont>     );
    SQL>
    SQL> show table (constraint) PERSON
    Information for table PERSON

    Table constraints for PERSON:
    ALL_UNIQUE
     Unique constraint
         Null values are considered distinct
     Table constraint for PERSON
     Evaluated on each VERB
     Source:
     UNIQUE (last_name, first_name, birthday)

    MUST_BE_IN_PAST
     Check constraint
     Column constraint for PERSON.BIRTHDAY
     Evaluated on UPDATE, NOT DEFERRABLE
     Source:
     CHECK (birthday < current_date)

    MUST_HAVE_LAST_NAME
     Not Null constraint
     Column constraint for PERSON.LAST_NAME
     Evaluated on COMMIT
     Source:
     PERSON.LAST_NAME NOT null

    Constraints referencing table PERSON:
    No constraints found

    SQL>
    SQL> alter constraint ALL_UNIQUE
    cont>     deferrable initially deferred;
    SQL>
    SQL> alter constraint MUST_HAVE_LAST_NAME
    cont>     comment is 'We must assume all persons have a name'
    cont>     not deferrable;
    SQL>
    SQL> alter constraint MUST_BE_IN_PAST
    cont>     deferrable initially immediate;
    SQL>
    SQL> show table (constraint) PERSON
    Information for table PERSON

    Table constraints for PERSON:
    ALL_UNIQUE
     Unique constraint
         Null values are considered distinct
     Table constraint for PERSON
     Evaluated on COMMIT
     Source:
     UNIQUE (last_name, first_name, birthday)

    MUST_BE_IN_PAST
     Check constraint
     Column constraint for PERSON.BIRTHDAY
     Evaluated on each VERB
     Source:
     CHECK (birthday < current_date)

    MUST_HAVE_LAST_NAME
     Not Null constraint
     Column constraint for PERSON.LAST_NAME
     Evaluated on UPDATE, NOT DEFERRABLE
     Comment: We must assume all persons have a name
     Source:
     PERSON.LAST_NAME NOT null

    Constraints referencing table PERSON:
    No constraints found

    SQL>
    SQL> commit;