""sdbeames"" <s.beames@...> wrote in message
news:a5e776+103cs@......

> Hi all,
>
> FB 0.9.4.41/IB_SQL

A bit old. We had 0.95, 0.96 (lasted few time), RC1 & RC2.

> can I alter a foreign key's constraints within a single script?
> It seems I have to disconnect & reconnect before dropping the
> constraint, but 'disconnect all' isn't allowed. SYSDBA is sole user.
> The following causes an 'object INDEX is in use' error at the 'drop
> constraint' statement.
> /****************************************/
> alter TABLE ITEMSUSED add TEMP INTEGER;
> commit;
> update ITEMSUSED set TEMP = ITEMNO;

=> COMMIT HERE, you are mixing DDL with DML. It may work when you have only
one connection to the db.

> /* have to dis/reconnect here */
> alter table ITEMSUSED drop constraint ITEMS_ITEMNO_FK;
> commit;
>
> alter table ITEMSUSED add constraint ITEMS_ITEMNO_FK
> FOREIGN KEY (ITEMNO) REFERENCES ITEMS
> ON UPDATE CASCADE
> ON DELETE SET NULL;
> commit;
> update ITEMSUSED set ITEMNO = TEMP;

COMMIT again.

> alter table ITEMSUSED drop TEMP;
> commit;

I assume this is only an example. I wouldn't go to those pains just to drop
the field at the end.

C.
--
Claudio Valderrama C. - http://www.cvalde.com - http://www.firebirdSql.org
Independent developer
Owner of the Interbase® WebRing