What does "SQLCODE -607 -- Unsuccessful Metadata Update" Mean?

By: Borland Staff

Abstract: Error indicates an attempt to create an index with a name that already exists.

Problem:
Is there anyway to debug the following error, or at least to point me in the 
right direction: 

statement failed, SQLCODE = -607
unsuccessful metadata update
-STORE RDB$INDICES failed
-attempt to store duplicate value (visible to active transactions) in unique 
index "RDB$INDEX_5"

Solution:
Here is an explanation of the text associated with SQLCODE -607.
It's intended to help people, who, unlike programs, have trouble associating
useful meaning with numbers.

>-"STORE RDB$INDICES failed"

This is the actual operation that failed.  STORE is InterBase old-speak for INSERT.
The next line of the error contains the name of the table on which the operation failed.
So far, we know that a metadata operation failed because an insert into 
the system table RDB$INDICES failed.

>-attempt to store duplicate value (visible to active transactions) in 
>unique index "RDB$INDEX_5"

This is the crux of the matter.  The failure was caused by an attempt to
store a duplicate value into a unique index, and the unique index is
RDB$INDEX_5.  Not a very informative name, but we can deduce from the 
fact that the name starts with "RDB$" that that the index was created by 
the system rather than a user.  'RDB$' is the prefix InterBase gives to all of its system
generated metadata objects.  SHOW metadata may also help.


SHOW INDEX rdb$index_5
RDB$INDEX_5 UNIQUE INDEX ON RDB$INDICES(RDB$INDEX_NAME) 

RDB$INDEX_5 is a single key unique index on the field RDB$INDEX_NAME
in the table RDB$INDICES.    'RDB$INDICES' is the name of the system table that 
stores all of the metadata for indices.  Each index name can exist in this index only once.  
You can query this table to find out  information about the index that already exists in 
the database with this name.

Further checking shows that RDB$INDEX_NAME contains the name of each defined index.

One possible cause could be that your script is  attempting to define two indexes with the same name.  
Index names must be unique in the database - not just within a table.  


The following example using the database 'employee.gdb' from the
InterBase 'examples' directory demonstrates a possible way to 
generate this type of error.

SQL> create unique index myindex1 on employee(emp_no);
SQL> commit;
SQL> create unique index myindex1 on customer(cust_no);
Statement failed, SQLCODE = -607
unsuccessful metadata update
-STORE RDB$INDICES failed
-attempt to store duplicate value (visible to active
transactions) in unique index
 "RDB$INDEX_5"



Source : Ann Harrison on the listserve

Another possible cause of this error is trying insert a duplicate value into the primary key field.
When a primary key is created, there is an unique index created by the system as well.





Server Response from: ETNASC03