Basic concepts > Database entities

An index determines the order of the records in a table. Paradox, dBASE, FoxPro, Access, and SQL database systems all let you create indexes to order records. However, there are differences in the way indexes work and the information required to define indexes in each of the database systems.

The Borland Database Engine (BDE) supports all the native modes of indexing for Paradox, dBASE, FoxPro, Access, and SQL database systems. To enable your application to create an index, BDE provides a generic index descriptor structure, IDXDesc. IDXDesc is a union of all of the fields required to define an index for all of the supported database systems. To add an index, the application supplies the required data in IDXDesc and calls the function DbiAddIndex.

To create an index for a table, your application need only supply data in the index descriptor fields that are applicable to that particular table's database system. For example, when defining an index on an InterBase table, your application ignores fields such as szTagName and bExpIdx, which are used only in defining dBASE indexes. When required fields are not supplied, an error message is returned by the DbiAddIndex call.

Different types of indexes allowed within the database system may have different requirements. For example, when adding a dBASE maintained index, the field szTagName is required. Indexes can also be created using the SQL Data Definition Language.

Types of indexes

There are three basic types of indexes:

•Traditional indexes on columns. These indexes can be single column indexes or composite indexes on more than one column.

•Expression indexes. These indexes have key values determined by an expression (not necessarily column values). Of the drivers mentioned, only dBASE currently supports expression indexes.

•Pseudo-indexes. For SQL data sources, BDE can create a pseudo-index by using one or more user-specified SQL fields to define the requested order

Characteristics of indexes

Indexes have three other characteristics:

•Subset indexes do not index every record in a table; instead, they index only those rows that satisfy a given Boolean expression. Of the drivers mentioned, only dBASE uses subset indexes.

•Unique indexes cannot have duplicate key values.

•Indexes can be ascending or descending for drivers that support them.

Driver-defined index requirements

It is important to understand that different drivers support different types and characteristics of indexes. The following sections provide a partial list of rules for the different index types and characteristics supported by each driver:

dBASE

The following rules describe how dBASE supports indexes:

•dBASE supports only expression indexes. (Single-column indexes are treated as a special case of expression indexes.)

•dBASE supports two different physical index formats: .NDX-style and .MDX-style.

•dBASE supports subset indexes in .MDX-style indexes.

•In dBASE, all maintained indexes are .MDX-style indexes.

•dBASE supports FoxPro compressed index .CDX-style indexes.

•dBASE supports primary indexes only as index tags, not the .NDX file index type.

Expression indexes

When defining an index, dBASE uses expression indexes. The expression index determines how the key is computed when a record is added. Expression indexes can be simply the name of a field or they can be created from field names, operators, and dBASE Data Manipulation Language (DML) functions.

Maintained indexes

Multiple indexes are stored in a single file with a .MDX or .CDX extension. dBASE stores different indexes in the same physical file. Each index in the multiple index file is called a tag. Tags are identified by the szTagName you assigned when you created the index. Up to 47 index tags may be included in a single index file.

One of the multiple index files is used to store all the maintained indexes. The name of this file is of the form <Tbl_Name>.MDX or <Tbl_Name>.CDX. This file is called the production index file; indexes in this file are always maintained by the BDE. The front-end application need only open the table for this to happen.

Non-maintained indexes

The dBASE driver also supports the older, obsolescent style indexes (circa dBASE III PLUS) called .NDX indexes. This index is stored in a file with a .NDX extension. Each such file contains only one index; this index is maintained only if the index is explicitly opened.

The front-end application must explicitly request the BDE update these index types as data in the table is changed, added, and deleted. These indexes are not automatically maintained.

Paradox

The following rules describe how Paradox supports indexes:

•Paradox supports both single- and multi-column indexes.

•Paradox supports a primary key.

•Paradox supports maintained and non-maintained secondary indexes. Maintained secondary indexes are supported only if the table also has a primary index. If an index is non-maintained, it becomes out of date if any data in the table changes.

•Paradox does not support expression indexes.

•Paradox does not support subset indexes.

•Paradox supports case-sensitive/insensitive secondary indexes.

•Paradox supports descending indexes with level 6 tables.

Primary indexes

A Paradox primary key is defined as a field or group of fields whose values uniquely identify each record of a table. The fields in a key must be contiguous starting with the first field. A primary key requires a unique value for each record (row) of a table. A table’s primary key establishes the default sort order for the table. A Paradox table is sorted based on the values in the fields you define as the table's primary key. Only one record's primary key can be blank. All subsequent blanks are considered as duplicates, and records containing them are not accepted.

Secondary indexes

Paradox supports secondary indexes. A table can have more than one secondary index, and a secondary index can be a composite index. Each secondary index can be maintained or non-maintained. If it is maintained, the index is updated automatically every time the table is changed. Secondary indexes can be case-sensitive or insensitive. If it is case-sensitive, BDE differentiates between uppercase and lowercase letters as it sorts fields. Maintained secondary indexes are supported only if the table also has a primary key. If an index is non-maintained, it becomes out of date if any data in the table changes.

SQL

The following rules describe how SQL drivers support indexes:

•All SQL indexes are maintained.

•The rules for index creation are based on SQL server support. SQL drivers support the following indexes if they are supported by your server:

- Single and multi-column indexes
- Unique and non-unique indexes
- Ascending and descending indexes

•If an index is added to any SQL table, then any cursors open on that table must be closed and reopened, to allow for possible changes in the buffer size.