Creating an Index on a Table

After defining the columns for your table, you are ready to define its indexes. You can define an index when you create the table, or you can use the Alter Table window to define an index for an existing table.

Options are provided for compatibility with DB2 and other XDB Server-supported databases.

To create an index:

1. Click the Create command from the Table menu to display the Create Table or Alter Table dialog box, or click the Alter command to display the Open Table dialog box and choose a table. If you are creating a new table, or you need to change the column specifications for an existing table, make the column entries before proceeding.
   Note:

   
   
   From the Catalog Browser dialog box, click the Table tab; then click the Create button, or choose a table and click the Alter button

2. Click the Create button in the Indexes and Keys group box.

   SQLWizard displays the Create Index dialog box.

3. Type a name for your index in the Index Name field.
4. Choose the type of index by clicking one of the three types in the Type group box: Index, Unique Index, or Primary Key.
5. Choose the column(s) you wish to use as the key for the index. Any column names included in the Index Columns list when you exit the Create Index dialog box are used as your index key(s). Use the following descriptions as a guide for choosing columns.

   To move a column name from one list to the other, click its name to highlight it; then click the right or left arrow button to move it to the other list. Alternatively, double-click a column name to move it to the other list.

   To select multiple contiguous items, hold down the Shift key when clicking on the first and last entry of the desired list of names. To select multiple non-contiguous items, hold down the Ctrl key while clicking on each item you want to select. When the desired items are highlighted, click the appropriate arrow button to move them to the other list.

   To move all column names from one list to the other, click a double-arrow button.

6. To change the order of columns in the Index Columns list, click a column name and click the up- or down-arrow button.
7. Click the Options button to specify additional index items.

   Most of these additional options are provided for compatibility with DB2 or other XDB Server-supported databases. For DB2, these include the equivalents of USING BLOCKS, FREE BLOCKS, CLUSTERS, BUFFER POOLS and other options.

8. Click OK to accept the index definition.

   SQLWizard adds the name of the new index to the Indexes And Keys group box, preceded by a + sign to indicate a new index definition.

9. Click OK from the Create/Alter Table dialog box to create the new index.

   SQLWizard creates the new index definition for the selected table.