> I understand that there is no way of creating and populating a table
> within a single transaction? Dito for creating foreign keys in other
> tables that refer to the newly created table?

Conceptually I agree, although there are loads of implementation
questions if it were done that way.

To answer directly your question. You need to commit after making any
DDL changes before executing DML. It is a known source of data weirdness.

> If I'm right, does this have a reason?

Some things are bigger than transactions, indexes are global for
instance. What about constraints? If you were to add a unique
constraint, and another user is active, should they be allowed to add
a duplicate to the table before you commit? What about foreign keys?
All of these items are new restrictions other transactions need to
rely on.

> I must admit it's quite annoying
> sometimes. I mean, it's supposed to be the meaning of transactions to
> bundle tasks that should be performed altogether or not at all - in my
> book this applies to creating a table and creating other database
> objects related to that table.

The Atomicity part anyway, what about Isolation? You can hardly call a
drop table in isolation and get anything remotely workable.

The approach you need to use is to make a backup of the database
before any DDL changes are made, and if you encounter any issues,
restore the backup. DDL can only be safely made if you are the only
user. Firebird provides several mechanisms for this, the easiest way
is to put the database into shutdown mode.

General running of your system should not require DDL changes,
certainly they may be required if a fix or enhancement is made, so
this approach should work fine.

Adam

>
> I use FB 1.5 on a windows XP machine.
>
> Thanks,
>
> Stefan
>