Subject Odp: [firebird-support] Restore errors - missing FK
Author liviuslivius@poczta.onet.pl
Hi,

Your query is not good for index error
Try
Select * from zip_code_account z where not exists(select * from account a where a.account_id + 0 = z.account_id)

This will be slow because it not use index but give you the answer

Regards,
Karol Bieniaszewski

----- Reply message -----
Od: "&apos;Bob Murdoch&apos; mailgroups@... [firebird-support]" <firebird-support@yahoogroups.com>
Do: <firebird-support@yahoogroups.com>
Temat: [firebird-support] Restore errors - missing FK
Data: niedz., cze 29, 2014 19:30


 

I received an error during a restore process today, where multiple FKs could not be restored.  The restore log messages look like this:

 

gbak:cannot commit index FK_ZIP_CODE_ACCT_TO_ACCOUNT

gbak: ERROR:violation of FOREIGN KEY constraint "FK_ZIP_CODE_ACCT_TO_ACCOUNT" on table "ZIP_CODE_ACCOUNT"

gbak: ERROR:    Foreign key reference target does not exist

 

gbak:cannot commit index FK_TRANSFER_TO_ACCOUNT

gbak: ERROR:violation of FOREIGN KEY constraint "FK_TRANSFER_TO_ACCOUNT" on table "TRANSFER"

gbak: ERROR:    Foreign key reference target does not exist

 

All told there were about 23 foreign keys which couldn’t be restored – almost the exact number of foreign keys pointing to the ACCOUNT table.

 

It looks to me like somehow an ACCOUNT row was deleted, breaking the FK.  I ran a number of queries that look like this:

 

select

   t.account_id, a.account_id

from

   zip_code_account t

   left join account a on (a.account_id = t.account_id)

 

So that I could look for any record in ZIP_CODE_ACCOUNT that did not have a matching record in ACCOUNT.  There were no records that matched this condition.  I tried it on a number of other tables as well, and could not find one instance where a value in table T.ACCOUNT_ID did not have a matching value in A.ACCOUNT_ID.

 

What would have caused this restore to fail?

 

Thank you,

 

Bob M..