Firebird Documentation IndexFirebird 1.5 Language Ref. UpdateDDL statements → CREATE INDEX
Firebird Home Firebird Home Prev: CREATE GENERATORFirebird Documentation IndexUp: DDL statementsNext: CREATE TABLE

CREATE INDEX

Table of Contents

UNIQUE indices now allow NULLs
Maximum number of indices per table increased

Tip

Find a more recent version at Firebird 5.0 Language Reference: INDEX

Available in: DSQL, ESQL

UNIQUE indices now allow NULLs

Changed in: 1.5

Description: In compliance with the SQL-99 standard, NULLs – even multiple – are now allowed in columns that have a UNIQUE index defined on them. For a full discussion, see CREATE TABLE :: UNIQUE constraints now allow NULLs. As far as NULLs are concerned, the rules for unique indices are exactly the same as those for unique keys.

Maximum number of indices per table increased

Changed in: 1.0.3 and 1.5

Description: The maximum number of 64 indices per table has been removed in Firebird 1.0.3, and reintroduced at the higher level of 256 in Firebird 1.5.

Note

Probably due to an off-by-one error in the code, the effective ceiling is 65 indices in Firebird 1.0 and 1.0.2, and 257 indices in Firebird 1.5.

The number of indices attainable in practice is further limited by the database page size and the number of columns per index, as shown in the table below.

Table 5.1. Maximum indices per table

Page size Firebird version(s)
1.0, 1.0.2 1.0.3 1.5.x
1 col 2 cols 3 cols 1 col 2 cols 3 cols 1 col 2 cols 3 cols
1024 62 50 41 62 50 41 62 50 41
2048 65 65 65 126 101 84 126 101 84
4096 65 65 65 254 203 169 254 203 169
8192 65 65 65 510 408 340 257 257 257
16384 65 65 65 1022 818 681 257 257 257


Please be aware that under normal circumstances, even 64 indices is way too many and will drastically reduce mutation speeds. The maximum was raised to accommodate data-warehousing applications and the like, that do lots of bulk operations during which indices are temporarily switched off.

Prev: CREATE GENERATORFirebird Documentation IndexUp: DDL statementsNext: CREATE TABLE
Firebird Documentation IndexFirebird 1.5 Language Ref. UpdateDDL statements → CREATE INDEX