hi

Helen Borrie wrote:

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

Thanks a lot for those guidelines. what can I say but Thank You! =)


cheers,
james