Helen,

Helen Borrie schrieb:

> At 05:16 PM 28/10/2007, you wrote:
>
>>> Hmmm. Testing seems to indicate that creating foreign key
>>> contraints require previous DDL to be committed. "Object in use"
>>> ...
>> No, I do exactly this kind of DDL with TIB_Scripts for years now.
>> "Object is in use" is not a matter of committing, it's rather
>> caused by a second connection to the database.
>
> Umm, no, Luc. "Object is in use" means that a DDL request cannot be
> satisfied "for some reason related to the keys". So, not having the
> script running with exclusive access will throw this exception....but
> so will attempting to reference a foreign key constraint to an
> uncommitted PK or unique constraint key. The latter has never been
> possible in IB or FB and it still isn't.
>
> Somehow, I think your "luck" over the years must be related to using
> an autocommit transaction for your scripts...but using "client-side
> autocommit" (AutoCommit true) isn't recommended for DDL, anyway.

I'm not using ClientSide automommit, neither commit within the scripts.
I do have scripts like:

CREATE TABLE MASTER (
MASTER_ID INTEGER NOT NULL,
CONSTRAINT PK_MASTER PRIMARY KEY ( MASTER_ID )
);

CREATE TABLE CHILD (
CHILD_ID INTEGER NOT NULL,
MASTER_ID INTEGER NOT NULL,
CONSTRAINT PK_CHILD PRIMARY KEY ( CHILD_ID )
);

ALTER TABLE CHILD ADD CONSTRAINT FK_MASTER_CHILD
FOREIGN KEY ( MASTER_ID ) REFERENCES MASTER ( MASTER_ID )
ON DELETE CASACDE;

I execute these scripts like:

try
Script.Execute;
Script.IB_Transaction.Commit;
except
Script.IB_Transaction.Rollback;
end;

I should switch to play lottery if I got away with this for almost ten
years without any problem just by mere luck.


Luc.