At 07:45 AM 22/01/2004 +0000, you wrote:

>Hello
>
>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
>records!
>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)

Show the definitions of the two tables and their keys.

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

That may fail in Firebird because it contains improperly qualified column
references (and, yes, IB let's you do it)

delete from detail d
where not exists(select m.* from master m
where m.key = d.key);

You don't say whether you are using Firebird, though. Are you? If so,
which version?

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

What method did you use to migrate your IB 5.6 database to IB 6.0? If you
then migrated it to Firebird, what method did you use?

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

Not in Firebird. Not in IB 6.0 either.

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

The proper way to migrate from 5.6 to 6.0 is to backup the database using
the 5.6 version of gbak, and then to restore it using the 6.0 version of
gbak. There is a similar route for upgrading from IB 6.0 to
Firebird. Don't expect reliability from IB 6.0, either. It is very buggy
and neglected.

If things are in a messed up state because of improper ODS upgrading then
it will probably require a datapump to put right.

/heLen