Modifying Indexes, CALC Keys, and Referential Constraints › Maintaining Referential Constraints › Modifying Referential Constraint Tuning Characteristics

Modifying Referential Constraint Tuning Characteristics

To modify referential constraint tuning characteristics (for example, changing from unlinked to linked or adding an ORDER BY option) use the SQL DDL DROP CONSTRAINT statement, then re-add the constraint using the SQL DDL CREATE CONSTRAINT statement. Certain referential constraint characteristics can be changed with the SQL DDL ALTER CONSTRAINT statement.

For more information about SQL DDL statements see the SQL Reference Guide.

Using ALTER CONSTRAINT

Types of Changes You Can Make:

The following attributes of a referential constraint can be changed:

• Index block specification
• Index uniqueness

Statements That Modify Constraints

To modify referential constraint attributes, use the ALTER CONSTRAINT statement.

Considerations

All considerations for modifying a referential constraint apply.

Example: Indexed constraint characteristics are changed

alter constraint dept_empl on demo.empl
      displacement is 50 pages
      index block contains 10 keys;

Using DROP/CREATE CONSTRAINT

Considerations

All considerations for dropping and creating a referential constraint apply.

Example: Linked referential constraint has been changed to unlinked

drop constraint emp_benefits from benefits;
create constraint emp_benefits
       benefits (emp_id)
       references employee (emp_id);