Subject Bug: Unexpected "Violation of FOREIGN KEY constraint" with COLLATE
Author turbomenda
The bug is as follows:

You can link two tables with a foreign key on string fields with
different collation orders. There's no error message when creating
the FK, but this prevents any inserts or updates to the detail table.
The only solution to this is to drop one of the columns and recreate
it with a matching COLLATE. Modifying the collation directly in RDB$
tables doesn't help.

This was detected in IB 6.0 open-source version (

Could anyone check if this fixed in Firebird?

--- In, Helen Borrie <helebor@t...>
> You don't say whether you are using Firebird, though. Are you? If
> which version?

I am using IB 6.0. I switched from 5.6 to save on additional client
licenses (I got beetween 100-200 connected users)

> What method did you use to migrate your IB 5.6 database to IB 6.0?

Usual backup/restore. I am connecting using DIALECT 1, tho.

> >"delete from detail d where not exists(select * from
> >master where key=d.key)".
> That may fail in Firebird because it contains improperly qualified
> references (and, yes, IB let's you do it)

Well, it worked fine for me so far. Does it mean problems if I switch
to Firebird? I have tons of subqueries like that in my progs...

> Don't expect reliability from IB 6.0, either. It is very buggy
> and neglected.

Hmm... It works fine for 2 years now... I think I'll wait with
converting to Firebird until there's some real gain, say when a
multiprocessor version gets released. An ODS change like that is too
much risk with hundreds of users and 4 replicas. I also use every IB
trick available - UDFs, recursive stored procedures, external tables,
replication etc, so virtually any incopatibility anywhere means
problems for me. ;-)