>>when I restore FK I foun some records that break referential integrity. I delete this records and

create FK.

 

I have had 2 or 3 databases now, where the primary key index gets corrupted. Because the system used the corrupt PK to check, it doesn’t find a certain value, and allows the duplicate to be inserted.

 

Noticing the problem is very tricky – it normally only gets discovered when someone tries to restore the database.

Using the database that the backup was made from, you'd think you'd find the duplicates quite easily by running a SQL statement something like this:

 

select PK_FIELD, Count(ANOTHER_FIELD) from MY_TABLE group by PK_FIELD having Count(ANOTHER_FIELD > 1)

 

BUT, that once again uses the corrupt index, and shows everything as only having one record.

 

If you get clever and change the sort, so an non-corrupted index is used, you finally get to see the problem.

 

Then deleting it is also quite interesting, as you can't say:

 

DELETE from MY_TABLE where PK_FIELD = 5, as that will then delete the good record, and leave the hidden one behind. So, once again, you need to put some where clauses in that make Firebird use an index that isn't the PK index.

 

I've only seen 3 databases like this so far, and I think the index corruption issue has been fixed, we're just seeing more recently, as we are making people do a backup/restore when they upgrade, as we are using some of the new FB 2.5 features now.