At 11:20 AM 12/06/2004 +0800, you wrote:

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

Here was your statement:

alter table PAYMENT_DETAILS
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
committed;
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.

/heLen