Commands and Statements

Command SET UNIQUE Foundation

Sets whether an index may contain multiple records with the same key value.

SET UNIQUE on | OFF | <lToggle>
thread-local, current workspace
<lToggle> is a logical expression which must appear in parentheses. Instead of the logical expression, the option ON can be specified for the value .T. (true) or OFF for the value .F. (false). When .T. or ON is specified, only records with a unique index key are included in indexes when the index files are generated. When SET UNIQUE is OFF, all records are included in the index.

The command SET UNIQUE determines whether index files generally receive the unique attribute (UNIQUE flag). If SET UNIQUE is set to ON, unique indexes are created. This corresponds to the option UNIQUE of the command INDEX. The index file also retains the UNIQUE flag so that the command REINDEX later creates a unique file, regardless of the setting of SET UNIQUE when REINDEX is executed.

When index files are created using the option UNIQUE, or when SET UNIQUE is set to ON before the creation of the index file, only the first data record of records with the same key value is added to the index. Other records having the same key value are not added to the index and are not visible when the index with the UNIQUE flag is set as the controlling index.

When a record is changed and the controlling index in the work area is unique, unexpected results can occur. The following situations are important:

After changing a record, it has a key value which already exists: in this case, the changed record is not added to the index file, but is removed and is no longer logically visible.

After changing a record, it has a key value which does not yet exist, but there may be other records with the same key value: this does not cause another record with the same key value to be added to the index. To accomplish this, one of the commands REINDEX or PACK must be executed.

Because of these problems, SET UNIQUE should generally remain OFF. All records are then added to the index. When a unique index is needed, it should be created with the UNIQUE option of the command INDEX.


If you see anything in the documentation that is not correct, does not match your experience with the particular feature or requires further clarification, please use this form to report a documentation issue.