Caché SQL Reference
[Home] [Back] [Next]
InterSystems: The power behind what matters   

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. A table-name can be qualified (schema.table), or unqualified (table). An unqualified table name takes the system-wide default schema name. If you omit the table-name entirely, 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, the system 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.
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 name is used.
If DROP INDEX does not specify a table name, Caché searches through all indices for an index SqlName matching index-name, or an index name matching index-name for indices where an SqlName is not specified for the index. If Caché finds no matching indices in any class, an SQLCODE -333 error is generated, indicating no such index exists. If Caché finds more than one matching index, DROP INDEX cannot determine which index to drop; it issues an SQLCODE -334 error: “Index name is ambiguous. Index found in multiple tables.” Index names in Caché are not unique per namespace.
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

Send us comments on this page
Copyright © 1997-2019 InterSystems Corporation, Cambridge, MA