Subject Unexpected "Violation of FOREIGN KEY constraint" (IB 6.0 bug?)
Author turbomenda

I have a large database (>1GB >200 tables) that I've been developing
for a few years now. It used to be in IB 5.6 format, then ported to
the open-source IB 6.0 when it was announced.

Since then I started to get this very odd problem. For some tables,
including newly created ones, when trying to add a foreign key
constraint I get the "Violation" error when there's everything OK - I
mean corresponding records in the master table exist for all detail
If this happens, I can add the constraint only if the detail table is
empty, but this prevents adding any rows to it (same error after
posting any INSERT)

I get double sure that there's no real violation of the constraints,
let's say by a "delete from detail d where not exists(select * from
master where key=d.key)". Doesn't help.
I think I get this for VARCHAR fields only.
I tried playing with "CHARACTER SET" and "COLLATE" but it doesn't
help eighter. (Most of my tables use CHARACTER SET WIN1250 COLLATE
PXW_PLK). Neighter does a backup/restore.

Perhaps there's a limit for number of foreign constraints that I hit
or else? I'm completely stuck.

This problem occurs randomly and levaves me keeping some tables not
constrained which is very, very bad...
It never occured before changing to IB 6.0 ODS

Perhaps someone has a clue?