ALTER CONSTRAINT

The ALTER CONSTRAINT statement changes the characteristics of an existing referential constraint. This statement is a CA IDMS extension to the SQL standard.
idmscu19
The ALTER CONSTRAINT statement changes the characteristics of an existing referential constraint. This statement is a CA IDMS extension to the SQL standard.
Authorization
To issue an ALTER CONSTRAINT statement, you must:do the following:
  • Either hold the ALTER privilege on, or own the referencing table in the constraint being altered
  • Hold the REFERENCES privilege on the referenced table in the constraint being altered
Syntax
►►─ ALTER CONSTRAINT constraint-name ON ───────────────────────────► ►─────────┬──────────────────┬─ referencing-table──────────────────►           └──schema-name.────┘    ┌───────────────────────────────────────────┐  ►─▼───┬INDEX BLOCK CONTAINS
key-count
KEYS──┬─┴──────────────────►◄        ├─DISPLACEMENT IS
page-count
PAGES ───┤        └─┬───────┬─UNIQUE ───────────────────┘          └──NOT──┘
Parameters
  • constraint-name
    Identifies the referential constraint to be changed. Constraint-name must be the name of a constraint on the table identified in the ON clause.
  • referencing-table
    Specifies the name of the referencing table in the constraint to be changed.
  • schema-name
    Identifies the schema associated with the referencing table.
    Default:
    The default varies depending on where the statement is encountered.
    If you do not specify schema-name, it defaults to:
    • The current schema associated with your SQL session when the statement is entered through the Command Facility or executed dynamically.
    • The schema associated with the access module used at runtime when the statement is embedded in an application program.
  • key-count
    KEYs
    Establishes a new value for the maximum number of entries in each internal index record (SR8 system record).
    Limits:
    Key-count must be an unsigned integer in the range 3 through 8180.
  • page-count
    PAGES
    Specifies how far away from the referenced row the bottom-level index records are stored.
    If the value of page-count is zero (0), the bottom-level internal index records are not displaced from the referenced row.
    Limits:
    Page-count must be an unsigned integer in the range 0 through 32,767.
  • UNIQUE
    Specifies that the sort-key value in any given row of the referencing table must be different from the sort-key value in all other rows that have the same non-null referencing key value.
  • NOT UNIQUE
    Removes the restriction that all values of the sort-key with the same non-null foreign key value must be unique.
Example
In this example, the physical characteristics of the DEPT_EMPL constraint are changed. Each internal index record will have a maximum of 10 keys and the bottom level index records will be displaced 50 pages from the associated referenced row:
alter constraint dept_empl on emp.empl       displacement is 50 pages       index block contains 10 keys;