Subject RE: [firebird-support] Foreign keys
Author Alan McDonald
> Ok, sorry, but that means nothing to me.
> On 17 Jul 2004, at 10:33, Alan McDonald wrote:
> >>
> >> Btw, there's one use case you seem to be forgetting. What about a real
> >> production database where other web applications are already running
> >> from it? Some ISP only allow one database instance, or a limited
> >> amount
> >> of them. So you suggest that to install a new application, one should
> >> shut down all the others, create the new structure manually and them
> >> put everything up again. Surely you can't be serious about this in a
> >> production environment?
> >>
> >> On 17 Jul 2004, at 09:50, Alan McDonald wrote:
> >
> > we're talking about database granularity NOT server granularity
> > Alan

here's a similar thing from June - maybe version 2 will be more to your

>Iam trying to add a foreign key with no success, and this is the error
>This operation is not defined for system tables.
>unsuccessful metadata update.
>attempt to store duplicate value (visible to active transactions) in
>unique index "RDB$INDEX_5".
>What does this mean?

Here was your statement:

add constraint FK_PAYMENT_DETAILS
foreign key (MASTER_KEY)
references PAYMENT(ID)

Now, what this needs to succeed is:

1. The CREATE TABLE operations for PAYMENT_DETAILS and PAYMENT must be
2. The primary key (or UNIQUE) constraint on PAYMENT.ID must be committed.

Now, if you were doing this stuff in a script, and the 'alter table'
statement failed because of one or the other of these two reasons, then the
unfinished transaction will have already stored the new index for the
foreign key, using by default the same name for the index as you gave for
the constraint. You would have the metadata sitting in an unstable
state: the supporting index created and still visible in the transaction,
but the constraint creation failing because of the uncommitted depended_on
constraint or table.

It's something you have to watch out for currently, unfortunately. A fix
is coming in Fb 2 (perhaps sooner); but, for now, your only choice is to
commit the transaction (if you can) and try to drop the index.

To prevent this sort of thing happening, make sure that depended_on objects
are committed before you try to create dependencies. In scripts or isql,
use SET AUTODDL ON to have Firebird run a separate transaction for each DDL
statement and commit it automatically; or use explicit COMMIT or COMMIT
WORK statements.

If you are doing this stuff in "bites" using an interactive utility, still
"think like a script" - commit depended_on objects manually before
submitting further requests that form dependencies.