Working with indexes

You can create as many single- or multi-valued indexes for a database table as you need, and you can drop indexes that are no longer needed.

Update limitation

You can update a table in a DataWindow object only if it has a unique index or primary key.

Creating an index

In SQL Anywhere databases

In SQL Anywhere databases, you should not define an index on a column that is defined as a foreign key, because foreign keys are already optimized for quick reference.

To create an index:

  1. Do one of the following:

    • Highlight the table for which you want to create an index and click the Create Index drop-down toolbar button in PainterBar1.

    • Select Object>Insert>Index from the main menu or New>Index from the pop-up menu.

    • Expand the table's tree view, right-click on Indexes, and select New Index from the pop-up menu.

    The Index's properties display in the Object Details view.

  2. Enter a name for the index in the Index box.

  3. Select whether or not to allow duplicate values for the index.

  4. Specify any other information required for your database.

    For example, in Adaptive Server Enterprise specify whether the index is clustered, and in SQL Anywhere specify the order of the index.

  5. Click the names of the columns that make up the index.

  6. Select Save Changes from the pop-up menu.

  7. Right-click on the Object Details view and select Save Changes from the pop-up menu.

    Any changes you made in the view are immediately saved to the table definition.

Modifying an index

You can modify an index.

To modify an index:

  1. Do one of the following:

    • Highlight the index listed in the table's expanded tree view and click the Properties button.

    • Select Properties from the Object or pop-up menu.

  2. Drag the index icon and drop it in the Object Details view.

  3. In the Object Details view, select or deselect columns as needed.

  4. Right-click on the Object Details view and select Save Changes from the pop-up menu.

    Any changes you made in the view are immediately saved to the table definition.

Dropping an index

Dropping an index removes it from the database.

To drop an index from a table:

  1. In the Database painter workspace, display the pop-up menu for the index you want to drop.

  2. Select Drop Index and click Yes.