Firebird/Interbase Database Indexes

I have a firebird database which having a table well indexed, eg unique index transactionnumber.  However after some times, I find that I am able to save the record with the same transactionnumber twice.  Finally when I look into this database, I find this unique index is somehow inactive.

Do anyone know what may cause this happen whereas I do not do anything to inactivate this index ?
ivylnmAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
TheRealLokiConnect With a Mentor Senior DeveloperCommented:
Is this an index, or is it the primary key constraint?
sometimes a backup/restore can fail to reindex, but usually only if there has been a data corruption.
gfix should be able to tell you this

1 way I can think of to find all the duplicates is to use a stored procedure that does something like this (excuse typos)
create procedure finddupes
returns
( mykeyfield varcahr(32) )
as
declare variable lastkeyfield varchar(32);
begin
  :lastkeyfield = null;
  for select T.field1 from table1 t into :mykeyfield do
  begin
    if (:mykeyfield = :lastkeyfield) then suspend;
    :lastkeyfield = :mykeyfield;
  end
end;

something like that anyway...
0
 
ivylnmAuthor Commented:
By the way, is there a quick way for me to list out all inactive indexes ?
0
 
sun4sundayCommented:
>> unique index transactionnumber
In the table if you define the field as unique/primary key, then when you try to post it should through the error.
I think it is not the problem with the index. Check out your table structure and set the transactionnumber as unique.
Check in your code some where the index is deactivating. Any how it looks strange.

Also When the table is open don't do the backup for the interbase. it may get corrept.

http://www.ibphoenix.com/main.nfs?a=ibphoenix&page=ibp_db_corr

sun4sunday
0
 
RatjeConnect With a Mentor Commented:
Listing all inactive indexes...hmmm...maybe this'll work (from memory...works in interbase me thinks :) )

select * from rdb$Indices where rdb$index_inactive ='1'
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.