SQL › Alter Constraint Support › ALTER CONSTRAINT Statement

ALTER CONSTRAINT Statement

The ALTER CONSTRAINT statement changes the characteristics of an existing referential constraint. This statement is a CA IDMS extension to the SQL standard.

Note: To issue an ALTER CONSTRAINT statement you must own or hold the ALTER privilege on the table on which the constraint is defined.

ALTER CONSTRAINT Syntax

The following diagram shows the new ALTER CONSTRAINT statement:

►►─ ALTER CONSTRAINT constraint-name ON ───────────────────────────►

 ►───────────┬────────────────┬─ referencing-table ────────────────►
             └─ schema-name. ─┘

   ┌───────────────────────────────────────────┐
 ►─▼─┬─ INDEX BLOCK CONTAINS key-count KEYS ─┬─┴───────────────────►◄
     ├─ DISPLACEMENT IS page-count PAGES ────┤
     └─┬───────┬─ UNIQUE ────────────────────┘
       └─ NOT ─┘

ALTER CONSTRAINT Parameters

This section describes the ALTER CONSTRAINT 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: Alter the DEPT_EMPL Constraint

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;