Caché SQL Reference
[Back] [Next]
Go to:

Removes an index.
DROP INDEX index-name [ON [TABLE] [schema-name.]table-name]

DROP INDEX [schema-name.]table-name.index-name
index-name The name of the index to be deleted. index-name may be a standard index or a bitmap index.
ON table-name
ON TABLE table-name
Optional — The name of the table associated with the index. You can specify the table-name using either syntax: The first syntax uses the ON clause; the TABLE keyword is optional. The second syntax uses the qualified name syntax schema-name.table-name.index-name; the table’s schema name is optional. If you omit the table-name, Caché deletes the first index found that matches index-name, as described below.
A DROP INDEX statement deletes an index. DROP INDEX does not apply to indices created by defining PRIMARY KEY or UNIQUE constraints (created by using the PRIMARY KEY or UNIQUE options of either the CREATE TABLE or ALTER TABLE statements, respectively).
You may wish to delete an index for any of the following reasons:
You cannot drop an IDKEY index when there is data in the table. Attempting to do so generates an SQLCODE -325 error.
Privileges and Locking
The DROP INDEX command is a privileged operation. Prior to using DROP INDEX it is necessary for your process to have either %ALTER_TABLE administrative privileges or the %ALTER privilege for the specified table. Failing to do so results in an SQLCODE –99 error (Privilege Violation). You can determine if the current user has %ALTER privilege by invoking the %CHECKPRIV command. You can determine if a specified user has %ALTER privilege by invoking the $SYSTEM.SQL.CheckPriv() method.You can use the GRANT command to assign these privileges, if you hold appropriate granting privileges.
DROP INDEX cannot be used on a table created by defining a persistent class, unless the table class definition includes [DdlAllowed]. Otherwise, the operation fails with an SQLCODE -300 error with the %msg DDL not enabled for class 'Schema.tablename'>
The DROP INDEX statement acquires a table-level lock on table-name. This prevents other processes from modifying the table’s data. This lock is automatically released at the conclusion of the DROP INDEX operation.
Index Name and Table Name
When specify an index-name to create an index, Caché generates a corresponding class index name by stripping out any punctuation characters; it retains the index-name you specified in the class as the SqlName value for the index. When you drop an index, Caché searches the class (or classes) for either the matching SqlName or the matching class index name.
You can specify the table associated with the index using either DROP INDEX syntax form:
In either syntax, the table name can be unqualified (table), or qualified (schema.table). If the schema name is omitted, the system-wide default schema is used.
Note that DROP INDEX assumes that all index names in a namespace are unique. It is strongly suggested that you create unique index names, but Caché does not enforce uniqueness of index names. If multiple indices share a name, and you do not explicitly specify the associated table, DROP INDEX removes the index associated with the first table it finds, using an alphabetically ordered search of the corresponding class names.
Nonexistent Index
If you try to delete a nonexistent index, DROP INDEX issues an SQLCODE -333 error, by default. However, this default can be overridden system-wide by setting a configuration option as follows:
The default is “No” (0). By default, Caché issues an SQLCODE -333 error. This is the recommended setting for this option. Set this option to “Yes” (1) if you want a DROP INDEX for a nonexistent index to perform no operation and issue no error message. For further details, refer to SQL configuration settings described in Caché Advanced Configuration Settings Reference.
Table Name
If you specify the optional table-name, it must correspond to an existing table.
The first example creates a table named Employee, which is used in all of the examples in this section.
The following embedded SQL example creates an index named "EmpSalaryIndex" and later removes it. Note that here DROP INDEX does not specify the table associated with the index; it assumes that "EmpSalaryIndex" is a unique index name in this namespace.
  &sql(CREATE TABLE Employee (
  WRITE !,"SQLCODE=",SQLCODE," Created a table"
  &sql(CREATE INDEX EmpSalaryIndex
       ON TABLE Employee
  WRITE !,"SQLCODE=",SQLCODE," Created an index"
  /* use the index */
  &sql(DROP INDEX EmpSalaryIndex)
  WRITE !,"SQLCODE=",SQLCODE," Deleted an index"
  WRITE !,"message",%msg
The following embedded SQL example specifies the table associated with the index to be dropped using an ON TABLE clause:
  &sql(CREATE INDEX EmpVacaIndex
       ON TABLE Employee
  WRITE !,"SQLCODE=",SQLCODE," Created an index"
  /* use the index */
  &sql(DROP INDEX EmpVacaIndex ON TABLE Employee)
  WRITE !,"SQLCODE=",SQLCODE," Deleted an index"
The following embedded SQL example specifies the table associated with the index to be dropped using qualified name syntax:
  &sql(CREATE INDEX EmpSickIndex
       ON TABLE Employee
  WRITE !,"SQLCODE=",SQLCODE," Created an index"
  /* use the index */
  &sql(DROP INDEX Employee.EmpSickIndex)
  WRITE !,"SQLCODE=",SQLCODE," Deleted an index"
The following command attempts to drop a nonexistent index. It generates an SQLCODE -333 error:
DROP INDEX PeopleIndex ON TABLE Employee
See Also