In addition to Helens answer, some words by me...

> >http://www.ibphoenix.com/main.nfs?a=ibphoenix&l=;KNOWLEDGEBASE;ID='6'
> >
> > Can anyone please let us know why Firebird has
> >such a behavior?
>
> The knowledgebase links you found explain it. You don't see it in MySQL
> because MySQL doesn't have referential integrity support or transactions.
> Its ability to protect itself from assault on its metadata is pretty

flimsy.

>
> To put it simply, you can't change the metadata of an object if it is in

use.

MySQL 4 with "InnoDB" tables does have RI and transactions -
but metadata changes are still a bit different.

With the standard MySQL MyISAM tables, whenever you make a
metadata change, MySQL makes a copy of the table, dumps the data
and renames the table. Why? Because it are simple files, not a database.

When RI is in place, doing the above becomes much harder -- you
cannot just delete a table and replace it with a new copy and rename
it. When doing that, the RI is broken, pointing to a void area (where
your original table used to be).

That being said, I fully agree with Helen that changing metadata when
others are connected and using the database is not a smart thing to do.

What do you expect from a user using an application and dropping
a column in a table - this can result in some weird errors, don't you
think? :-)

With regards,

Martijn Tonies
Database Workbench - developer tool for InterBase, Firebird, MySQL & MS SQL
Server.
Upscene Productions
http://www.upscene.com