At 12:01 PM 7/26/2001 +0200, Bernhard Döbler wrote:

>Unsuccessful metadata update
>object BODY is in use
>Statement: Alter table FELDINHALT add foreign key (BODY) references BODY
>(DATENWORTID) on update no action on delete cascade
>
>I don't see why BODY should be in use. It's even empty.

Creating a foreign key requires exclusive access to the database -
I think that's all that message is saying. Chase everybody off,
connect, and execute that statement before doing anything else.

Why does adding a foreign key constraint require exclusive access
to the database while adding an index (for example) does not?

When an index is created, the connection that creates it must get an
exclusive lock on the index root page for the table. Since everyone
who uses a table has some level of lock on its index root page,
every transaction using the table is immediately notified that a
new index exists. All inserts and deletes on that table, and all
updates that affect key fields (maybe all updates) stall until the
new index is created. When they restart, they are fully aware of
the index and update it appropriately.

I believe that the problem with foreign keys is in the actions. There's
no way to notify concurrent transactions that they should change their
behavior. At one point there was discussion about adding a page type
for foreign keys that would work much like the index root page, but that's
an ODS change and it was felt that the ability to add foreign keys on
a live database was not important enough to force and ODS change.



Regards,

Ann
www.ibphoenix.com
We have answers.