|
Each row in the SYSIDX system view defines a logical index in the database. The underlying system table for this view is ISYSIDX.
| Column name | Column type | Column constraint |
|---|---|---|
| table_id | UNSIGNED INT | NOT NULL |
| index_id | UNSIGNED INT | NOT NULL |
| object_id | UNSIGNED BIGINT | NOT NULL |
| phys_index_id | UNSIGNED INT | |
| file_id | SMALLINT | NOT NULL |
| index_category | TINYINT | NOT NULL |
| "unique" | TINYINT | NOT NULL |
| index_name | CHAR(128) | NOT NULL |
| not_enforced | CHAR(1) | NOT NULL |
table_id Uniquely identifies the table to which this index applies.
index_id A unique number identifying the index within its table.
object_id The internal ID for the index, uniquely identifying it in the database.
phys_index_id Identifies the underlying physical index used to implement the logical index. This value is NULL for indexes on temporary tables or remote tables. Otherwise, the value corresponds to the object_id of a physical index in the SYSPHYSIDX system view. See SYSPHYSIDX system view.
file_id The ID of the file in which the index is contained. This value corresponds to an entry in the SYSFILE system view. See SYSFILE system view.
index_category The type of index. Values include:
| Value | Index type |
|---|---|
| 1 | Primary key |
| 2 | Foreign key |
| 3 | Secondary index (includes unique constraints) |
"unique" Indicates whether the index is a unique index (1), a non-unique index (4), or a unique constraint (2). A unique index prevents two rows in the indexed table from having the same values in the index columns.
index_name The name of the index.
not_enforced For system use only.
PRIMARY KEY (table_id, index_id)
FOREIGN KEY (table_id) references SYS.ISYSTAB (table_id)
FOREIGN KEY (object_id) references SYS.ISYSOBJECT (object_id) MATCH UNIQUE FULL
FOREIGN KEY (table_id, phys_index_id) references SYS.ISYSPHYSIDX (table_id, phys_index_id)