Introduction

---

Adding and Deleting indexes is accomplished through the AddIndex, DeleteIndex, and DeleteAllIndexes methods of the TDBISAMTable component. The properties used by these methods include the DatabaseName, TableName, and Exists properties.

Adding an Index

---

To add an index, you must specify the DatabaseName and TableName properties of the TDBISAMTable component and then call the AddIndex method. The table can be open or closed when this method is called, however if the table is already open it must have been opened exclusively, meaning that the Exclusive property should be set to True. If the Exclusive property is set to False, an EDBISAMEngineError exception will be raised when this method is called. The error code given when an addition of an index fails due to the table not being opened exclusively is 10253 and is defined as DBISAM_NEEDEXCLACCESS in the dbisamcn unit (Delphi) or dbisamcn header file (C++). If the table is closed when this method is called, then DBISAM will attempt to open the table exclusively before adding the index. If another session has the table open then an EDBISAMEngineError exception will be raised when this method is called. The error code given when an addition of an index fails due to the table being open by another session is 11013 and is defined as DBISAM_OSEACCES in the dbisamcn unit (Delphi) or dbisamcn header file (C++). It is usually good practice to also examine the Exists property of the TDBISAMTable component first to make sure that you don't attempt to add an index to a non-existent table. If you do attempt to add an index to a non-existent table an EDBISAMEngineError exception will be raised. The error code given when adding an index to a table fails due to the table not existing is 11010 and is defined as DBISAM_OSENOENT in the dbisamcn unit (Delphi) or dbisamcn header file (C++). If you attempt to add an index with the name of an existing index an EDBISAMEngineError exception will be raised. The error code given when adding an index to a table that already contains an index with the same name is 10027 and is defined as DBISAM_INDEXEXISTS in the dbisamcn unit (Delphi) or dbisamcn header file (C++).

The following is an example of adding a case-insensitive index on the Company field in the "customer" table:

begin
   with MyDBISAMTable do
      begin
      DatabaseName:='d:\temp';
      TableName:='customer';
      if Exists then
         AddIndex('ByCompany','Company',
                  [ixCaseInsensitive],'',icDuplicateByte);
      end;
end;

Please see the Index Compression topic for more information on the options for index compression in DBISAM.

In addition to using the TDBISAMTable AddIndex method for adding indexes to tables, DBISAM also allows the use of the CREATE INDEX SQL statement.

Backup Files

---

DBISAM will make backups of a table's physical index file before adding an index to a table. The physical index file will have the same root table name but a different extension. This extension is as follows:


To restore this files in case of a mistake, simply rename them to the proper extension or copy them to the original file name. Also, this backup file will get overwritten without warning for each index addition or structure alteration that occurs on the table. If you need the backup file for future use it's best to copy it to a separate directory where it will be safe.

The file extensions described above are the default extensions and can be changed. Please see the DBISAM Architecture and Customizing the Engine topics for more information.

Tracking the Progress of the Index Addition

---

To take care of tracking the progress of the index addition, we have provided the TDBISAMTable and TDBISAMQuery OnIndexProgress events.

Dealing with Data Loss in the Index Addition

---

To take care of dealing with data loss during the addition of an index, we have provided the TDBISAMTable and TDBISAMQuery OnDataLost events. The OnDataLost event is used to track when data is lost due to key violations resulting from the addition of a primary index or unique secondary index.

Deleting an Index

---

To delete an index, you must specify the DatabaseName and TableName properties of the TDBISAMTable component and then call the DeleteIndex method. The DeleteIndex method accepts one parameter, the name of the index to delete. If you are deleting the primary index of the table you should use a blank string ('') as the index name parameter. The same rules for exclusive table access that apply to the AddIndex method also apply to the DeleteIndex method. If you attempt to delete an index that does not exist an EDBISAMEngineError exception will be raised. The error code given when deleting an index that does not exist in the table is 10022 and is defined as DBISAM_INVALIDINDEXNAME in the dbisamcn unit (Delphi) or dbisamcn header file (C++).

The following is an example of deleting an index called ByCompany from the "customer" table:

begin
   with MyDBISAMTable do
      begin
      DatabaseName:='d:\temp';
      TableName:='customer';
      if Exists then
         DeleteIndex('ByCompany');
      end;
end;

In addition to using the TDBISAMTable DeleteIndex method for deleting indexes from tables, DBISAM also allows the use of the DROP INDEX SQL statement.

Deleting All Indexes from a Table

---

To delete all indexes from a table, you must specify the DatabaseName and TableName properties of the TDBISAMTable component and then call the DeleteAllIndexes method. The same rules for exclusive table access that apply to the DeleteIndex method also apply to the DeleteAllIndexes method.

The following is an example of deleting all indexes from the "customer" table:

begin
   with MyDBISAMTable do
      begin
      DatabaseName:='d:\temp';
      TableName:='customer';
      if Exists then
         DeleteAllIndexes;
      end;
end;