> I am not a dba so my db knowledge is a bit limited. But as far as I
> know, firebird creates an index for every fk in the database.

That is correct. It also automatically creates one for primary keys
and unique constraints.

> It seems
> that other databases do not do that (oracle or sql server for
> example). Is there a way to avoir this ?

The immediate problem with an index created on a FK field is that some
foreign keys are in tables with a lot of records pointing to master
tables with very few records. The index has 'low selectivity', which
means little benefit is derived from using it and much expense is
required to maintain it. For example, a transaction table may collect
thousands of records per hour, and their may be a foreign key to a
users table of some sort. There may only be a handful of users, so you
have millions duplicates.

Firebird 2 goes a long way towards reducing the expense of maintaining
the an index with lots of duplicates, but you still have to maintain
an index which is pretty useless and it still takes up disk space.

The work-around in Firebird 1.5 is to place triggers on the master and
the detail tables to emulate the behaviour of the FK constraint. But
if one transaction deletes the master record, and another simultaneous
transaction is in the middle of inserting a detail record, (both
uncommitted), then the triggers will miss each other and inconsistent
data will be stored. ie: The master table will no longer contain the
record, but the detail table will contain a record that points to it.

This workaround is unacceptable in many cases due to this possibility.

Ideally, it would form part of the foreign key declaration, something
like:

'CONSTRAINT FK_BLAH FOREIGN KEY (BLAHID) REFERENCES BLAH (ID) ON
UPDATE CASCADE ON DELETE CASCADE NO INDEX'

or

'CONSTRAINT FK_BLAH FOREIGN KEY (BLAHID) REFERENCES BLAH (ID) ON
UPDATE CASCADE ON DELETE CASCADE WITH INDEX'

And not using the 'NO INDEX' or 'WITH INDEX' in the declaration is
would leave it to the RDBMS discretion.

(Of course I just made those keywords up, the SQL standard may have
something about it, or if not use the same syntax as the pseudo
standard by Oracle or MS.)

Did I answer your question after all than, hmmm, not really.

No there is no way to avoid it. It is not as bad performancewise in
FB2. The only workaround is to use triggers.

Adam