ALTER INDEX Statement

Renames indexes in base or global temporary tables, foreign key role names of indexes and foreign keys explicitly created by a user, or changes the clustered nature of an index on a catalog store table. You cannot rename indexes created to enforce key constraints.

Quick Links:

Go to Parameters

Go to Examples

Go to Usage

Go to Standards

Go to Permissions

Syntax

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

rename-clause - (back to Syntax)
   RENAME TO | AS new-name

move-clause - (back to Syntax)
   MOVE TO dbspace-name

cluster-clause - (back to Syntax)
   CLUSTERED | NONCLUSTERED

Parameters

(back to top)

Examples

(back to top)

Usage

(back to top)

You must have CREATE privilege on the new dbspace and be the table owner or have the MANAGE ANY DBSPACE system privilege.
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

(back to top)

  • SQL—ISO/ANSI SQL compliant.
  • SAP Sybase Database product—Not supported by Adaptive Server.

Permissions

(back to top)

move-clause for materialized view requires one of:
  • MANAGE ANY DBSPACE system privilege.
  • ALTER ANY INDEX system privilege.
  • ALTER ANY OBJECT system privilege.
  • You own the materialized view along with one of:
    • CREATE ANY OBJECT system privilege.
    • CREATE privilege on the target dbspace.
move-clause for all other indexes requires one of:
  • MANAGE ANY DBSPACE system privilege.
  • ALTER ANY INDEX system privilege.
  • ALTER ANY OBJECT system privilege.
  • You own the underlying table or have REFERENCES privilege on the table along with one of:
    • CREATE ANY OBJECT system privilege.
    • CREATE privilege on the target dbspace.
cluster-clause for materialized view requires one of:
  • ALTER ANY INDEX system privilege.
  • ALTER ANY OBJECT system privilege.
  • You own the materialized view.
cluster-clause for all other indexes, requires one of:
  • ALTER ANY INDEX system privilege.
  • ALTER ANY OBJECT system privilege.
  • REFERENCES privilege on the table.
  • You own the table.
All other clauses require one of:
  • ALTER ANY INDEX system privilege.
  • ALTER ANY OBJECT system privilege.
  • REFERENCES privilege on the table.
  • You own the underlying table.
Related reference
ALTER TABLE Statement
CREATE INDEX Statement
CREATE TABLE Statement