ALTER INDEX Statement

Renames indexes in base or global temporary tables and foreign key role names of indexes and foreign keys explicitly created by a user.

Syntax

ALTER { INDEX index-name[ INDEX ] FOREIGN KEY role-name[ INDEX ] PRIMARY KEYON [owner.]table-name   { rename-clause | move-clause } 

Parameters

Examples

Usage

The ALTER INDEX statement renames indexes and foreign key role names of indexes and foreign keys that were explicitly created by a user. Only indexes on base tables or global temporary tables can be renamed. You cannot rename indexes created to enforce key constraints.

ON clause—The ON clause specifies the name of the table that contains the index or foreign key to rename.

RENAME [ AS | TO ] clause—The RENAME clause specifies the new name of the index or foreign key role.

MOVE clause—The MOVE clause moves the specified index, unique constraint, foreign key, or primary key to the specified dbspace. For unique constraint or foreign key, you must specify its unique index name.

You must have CREATE privilege on the new dbspace and be the table owner or have DBA or SPACE ADMIN authority.

Note: Attempts to alter an index in a local temporary table return the error index not found. Attempts to alter a nonuser-created index, such as a default index (FP), return the error Cannot alter index. Only indexes in base tables or global temporary tables with an owner type of USER can be altered.
Side effects:
  • Automatic commit. Clears the Results tab in the Results pane in Interactive SQL. Closes all cursors for the current connection.

Standards

  • SQL—ISO/ANSI SQL compliant.

  • Sybase—Not supported by Adaptive Server Enterprise.

Permissions

Must own the table, or have REFERENCES permissions on the table, or have DBA or RESOURCE authority. For ALTER INDEX MOVE TO statements, you must have CREATE privilege on the new dbspace and be the table owner or have DBA or SPACE ADMIN authority.

Related reference
ALTER TABLE Statement
CREATE INDEX Statement
CREATE TABLE Statement