Skip to content

DF_INDEX_UNIQUE

Indicates whether the index is unique.

Level

Index

Supported by

The DataFlex SQL Drivers (SQL Server, DB2, and ODBC), revision 3.0 and higher.

Type

Boolean, permanent

Access

Read / Write

Values

  • True
  • False

Syntax

Use cli.pkg
Get_Attribute DF_INDEX_UNIQUE of {tableNumber} {indexNumber} to {BooleanVariable}
Set_Attribute DF_INDEX_UNIQUE of {tableNumber} {indexNumber} to {True|False}

Remarks

In the Embedded Database, all indices must be unique. There can be no two records with identical values for all segments in an index. SQL databases treat indices completely differently. Indices are not required as they are in the Embedded Database; they are used to tune performance. In SQL databases, indices are also not required to be unique. It is possible to define a non-unique index, allowing two records to exist that have identical values for all segments in an index.

The DataFlex framework is designed with unique indices in mind. It does not gracefully handle non-unique indices. The main concept of the DataFlex framework and the Embedded Database is a record-oriented approach to data in tables. SQL, on the other hand, uses a set-oriented approach. Non-unique indices fit very well in a set-oriented concept. However, they do not fit well in a record-oriented concept. Generally, you will see non-unique indices working adequately when used to traverse a complete set. When the index is used to jump around a table, some side effects may occur.

If you control the indices, we recommend not using non-unique indices.

This attribute can only be set inside a Structure_Start ... Structure_End operation. This attribute may be stored in the intermediate file under the keyword Index_Unique.

Example

Procedure ShowUniqueIndices Handle hTable
    Integer iLastIndex
    Integer iIndex
    String sIndex
    String sName
    Integer iNumSegments
    Integer iSegment
    Integer iColumn
    String sColumn
    Integer iCase
    Integer iDirection
    Boolean bUnique

    Get_Attribute DF_FILE_LAST_INDEX_NUMBER Of hTable To iLastIndex
    For iIndex From 1 To iLastIndex
        Get_Attribute DF_INDEX_NUMBER_SEGMENTS Of hTable iIndex To iNumSegments
        If (iNumSegments > 0) Begin
            Get_Attribute DF_INDEX_NAME Of hTable iIndex To sName
            Get_Attribute DF_INDEX_UNIQUE Of hTable iIndex To bUnique
            Move "" To sIndex
            For iSegment From 1 To iNumSegments
                Get_Attribute DF_INDEX_SEGMENT_FIELD Of hTable iIndex iSegment To iColumn
                Get_Attribute DF_FIELD_NAME Of hTable iColumn To sColumn
                Get_Attribute DF_INDEX_SEGMENT_CASE Of hTable iIndex iSegment To iCase
                If (iCase = DF_CASE_IGNORED) ;
                    Move (sColumn * "uppercased") To sColumn
                Get_Attribute DF_INDEX_SEGMENT_DIRECTION Of hTable iIndex iSegment To iDirection
                If (iDirection = DF_DESCENDING) ;
                    Move (sColumn * "descending") To sColumn
                If (iSegment > 1) ;
                    Move (sIndex + ",") To sIndex
                Move (sIndex * sColumn) To sIndex
            Loop
            Showln "   Index  " sName " (" sIndex ")" (If(bUnique, "", " NOT UNIQUE"))
        End
    Loop
    If (iLastIndex = 0) ;
        Showln "   NO index defined."
End_Procedure // ShowUniqueIndices

Procedure ShowAllIndices
    Handle hTable
    String sTable
    String sDriver
    Move 0 To hTable
    Repeat
        Get_Attribute DF_FILE_NEXT_USED Of hTable To hTable
        If (hTable > 0) Begin
            Open hTable
            Get_Attribute DF_FILE_DRIVER Of hTable To sDriver
            If (sDriver = "MSSQLDRV" Or sDriver = "DB2_DRV" Or sDriver = "ODBC_DRV") Begin
                Get_Attribute DF_FILE_LOGICAL_NAME Of hTable To sTable
                Showln "Indexes for " sTable
                Send ShowUniqueIndices hTable
            End
            Close hTable
        End
    Until (hTable = 0)
End_Procedure // ShowAllIndices

The sample procedure above shows all indices of all tables in the file list that are accessed through an SQL Database Driver. If the index is not unique, it will indicate this.