CREATE TABLE SYS.SYSINDEX ( table_id UNSIGNED INT NOT NULL, index_id UNSIGNED INT NOT NULL, root INT NOT NULL, file_id SMALLINT NOT NULL, "unique" CHAR(1) NOT NULL, creator UNSIGNED INT NOT NULL, index_name CHAR(128) NOT NULL, remarks LONG VARCHAR, index_type CHAR(4) NOT NULL, index_owner CHAR(4) NOT NULL, hash_limit SMALLINT NOT NULL, PRIMARY KEY ( table_id, index_id ), UNIQUE ( index_name, creator ), FOREIGN KEY REFERENCES SYS.SYSTABLE, FOREIGN KEY REFERENCES SYS.SYSFILE, FOREIGN KEY ( creator ) REFERENCES SYS.SYSUSERPERM ( user_id ) )
Each index in the database is described by one row in SYSINDEX.
table_id The table number uniquely identifies the table to which this index applies.
index_id Each index for one particular table is assigned a unique index number.
root Indexes are stored in the database as B-trees. The root identifies the location of the root of the B-tree in the database file.
file_id The index is completely contained in the file with this file_id (see “SYSFILE system table”).
“unique” Indicates whether the index is a unique index (“Y”), a nonunique index (“N”), or a unique constraint (“U”). A unique index prevents two rows in the indexed table from having the same values in the index columns.
creator The user number of the creator of the index.
index_name The name of the index. A user ID cannot have two indexes with the same name.
index_type The type of index: FP (known as the default index), HG, HNG, LF, DATE, TIME, DTTM, CMP, WD, LD, or SA (for a non-IQ index created in the Catalog Store).
index_owner The name of the index owner: USER, IQ, SA, AUTO.
