Command Discussion

• The keyword UNIQUE in the clause CREATE UNIQUE INDEX specifies that in the creation and maintenance of the index no two records in the index table can have the same value for the index column (or column combination). Thus, any INSERT or UPDATE command that attempts to add a duplicate row in the index would be rejected.

• Each index is assigned a name and is related to a particular table based on the ON table-name clause.

• An index is based on the specified column within the specified table, and will be arranged in ascending order.

Example: Create an index for the EMPLOYEE table that prevents records with the same employee ID from being stored in the table with this command:

CREATE UNIQUE INDEX EMPINDEX
     ON EMPLOYEE (EID) ;

This command directs the creation of a unique index on the EMPLOYEE table, where the index name is EMPINDEX. The EID column as indicated in parentheses is the Index key.

In other words, an index called EMPINDEX has been created on the EMPLOYEE table for the employee ID number.

The index is stored separately from the EMPLOYEE table. The example below shows the relationship between EMPLOYEE and EMPINDEX after ten employees have been added to the EMPLOYEE table. Each row of the index, EMPINDEX, consists of a column value for the index column and a pointer, or physical address, to the location of a row in the EMPLOYEE table. As employees are added or deleted from the EMPLOYEE table, Texis automatically updates the index in the most efficient and timely manner.

To conceptualize how the index works, assume you didn't realize Chapman's record was already stored in the EMPLOYEE table and you attempt to add her record again. You enter the command:

INSERT INTO EMPLOYEE
     VALUES ('103','Chapman, Margaret','LIB','STAFF','PART',22000) ;

and Texis responds with an error message, such as:

ERROR: Duplicate Value in Index

This message occurs because the value 103, the employee ID (EID), is already stored in EMPINDEX and attempting to add another 103 value results in a duplicate value, which is not permitted in a unique index.

When we add a new employee named Krinski with an EID equal to 110 by entering this command:

INSERT INTO EMPLOYEE
     VALUES ('110','Krinski','LIB','DHEAD','FULL',32500) ;

the record is successfully created. The value 110 did not previously exist in the unique index EMPINDEX, and so it was allowed to be entered as a new row in the EMPLOYEE table. As the EMPINDEX is in sorted order, it is much faster to ascertain that information than it would be by searching the entire EMPLOYEE table.

The relationship between EMPINDEX the index, and EMPLOYEE the table, appear below as they would containing 10 employee records. The dashed lines indicate pointers from the index to rows in the table. However, this is conceptual rather than actual, and not all pointers are shown.

EMPINDEX      EMPLOYEE
Index         Table
              EID  ENAME               DEPT   RANK   BENEFITS   SALARY
101 --------> 101  Aster, John A.      MKT    STAFF  FULL       32000
102 --+       109  Brown, Penelope     MKT    DHEAD  FULL       37500
103   |       104  Jackson, Herbert    RND    STAFF  FULL       30000
104   |  +--> 108  Jones, David        RND    DHEAD  FULL       37500
105   +--|--> 102  Barrington, Kyle    MGT    DHEAD  FULL       45000
106      |    106  Sanchez, Carla      MKT    STAFF  FULL       35000
108 -----+    105  Price, Stella       FIN    DHEAD  FULL       42000
107           103  Chapman, Margaret   LIB    STAFF  PART       22000
109           107  Smith, Roberta      RND    STAFF  PART       25000
110 --------> 110  Krinski, Wanda      LIB    DHEAD  FULL       32500