Subject RE: [firebird-support] Restore errors - missing FK
Author Bob Murdoch
Post date 2014-07-01T00:53:37Z

Thank you Karol for your post.

 

However, your query did not yield any results either.  I’m not sure what the difference would have been between using the left join and the subselect, but I’ll take any advice right now.

 

Thank you,

 

Bob M.. 

 

From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com]
Sent: Monday, June 30, 2014 1:23 AM
To: firebird-support@yahoogroups.com
Subject: Odp: [firebird-support] Restore errors - missing FK

 



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..