Firebird Index – Creation Fails

firebirdindex

I'm creating an index in a table with the following command:

CREATE INDEX IDX_CVE_OBS ON FACTF01 (CVE_OBS);

But it returns me the following error:

this operation is not defined for system tables. unsuccessful metadata update index IDX_CVE_OBS already exists

I have checked and the index name doesn't exists on that table. does the index name have to be unique across all the database? I'm not sure if the index name already exists on another table.

Best Answer

Index names need to be unique in the entire database; indexes are not part of table so they need to be uniquely identified. The Interbase 6.0 Language Reference says:

CREATE [UNIQUE] [ASC[ENDING] | DESC[ENDING]] INDEX index ON table (col [, col …]);

...
index Unique name for the index

The Firebird Book, 2nd Edition by Helen Borrie says:

CREATE INDEX <index-name>
—names the index. The identifier must be distinct from all other object identifiers in the database, apart from constraint and column identifiers.

Related Question