At 02:59 PM 24/04/2005 +0000, you wrote:

>Hi,
>
>I have a question about foreign keys. If I create a foreign key
>constraint, does FB not reuse existing indexes?

No.

>It looks like there is always a new index created:

Yes, always.

>CREATE TABLE SWIMRESULT
>(
> ATHLETEID INTEGER DEFAULT 0 NOT NULL,
> CLUBID INTEGER DEFAULT 0 NOT NULL,
> MEETID INTEGER DEFAULT 0 NOT NULL,
> STYLEID SMALLINT DEFAULT 0 NOT NULL,
> SWIMRESULTID INTEGER NOT NULL,
> SWIMTIME INTEGER,
>);
>
>
>CREATE TABLE SPLIT
>(
> DISTANCE SMALLINT NOT NULL,
> SWIMRESULTID INTEGER NOT NULL,
> SWIMTIME INTEGER
>);
>
>Then I tried two different indexes on table SPLIT:
>
>ALTER TABLE SPLIT ADD CONSTRAINT PK_SPLIT PRIMARY KEY (SWIMRESULTID,
>DISTANCE);
>
>or
>
>CREATE ASC INDEX IX_PK_SPLIT ON SPLIT (SWIMRESULTID, DISTANCE);

You don't want the user-defined index here. The primary key constraint
causes a unique index to be created automatically. The optimiser can get
confused if you try to duplicate the constraint's index.

>But in both cases, when I run the following statement, one more index
>is created on table SPLIT:
>
>ALTER TABLE SPLIT ADD CONSTRAINT FK_SPLIT_SWIMRESULT FOREIGN KEY
>(SWIMRESULTID) REFERENCES SWIMRESULT
> (SWIMRESULTID) ON DELETE CASCADE ON UPDATE CASCADE;

This index is not the same as either the PK constraint's index OR your
(redundant) compound index. It is a mandatory index that is automatically
created on only the column that references the PK of the parent table.

>Is there a way to avoid such (unnecessary) indexes?

In your example, it isn't an unnecessary index. The only problem I see
here is the redundant user-defined index.

There is no way (currently) to prevent the engine from creating the
mandatory indexes. From a design strategy perspective, this isn't a
problem. You should know what your primary and foreign keys are going to
be, before you ever start to think about indexes. (I suspect that you are
converting from a legacy DB such as Paradox, where referential integrity
depends on indexes because the engine does not support the foreign key
constraint...)

To be clear about indexes in Firebird: the only indexes that are actually
*required* are those that the engine creates to enforce constraints. (NB,
the UNIQUE constraint also creates a mandatory index!!) User-defined
indexing is a design activity that should be deferred until much later in
the development process. Until you come to test your structures with the
actual volumes and geometries of intended usage, you won't know whether a
user-defined index will be beneficial or not.

Unlike Paradox, Firebird can be smart about the indexes it uses for
particular purposes, if you give it the chance. It might eventuate that a
custom index will help to speed up a certain ordered query - but there may
be an existing index that the engine can use.

For a simple example, in a compound index of two key segments, like the one
you have as the PK of the SPLIT table, Fb can use the leftmost key segment
of that index (SWIMRESULTID) to order by that column. For ORDER BY
DISTANCE, the DISTANCE segment of that index is not available. If indexing
on DISTANCE would help speed up ordering, the PK that you defined is no
use. You would have to create another index on DISTANCE.

if the application requirements are more likely to need to order by
DISTANCE, rather than SWIMRESULTID, the better primary key constraint
would have been defined the other way around:

ALTER TABLE SPLIT
ADD CONSTRAINT PK_SPLIT PRIMARY KEY (DISTANCE, SWIMRESULTID);

Unlike Paradox, Firebird doesn't care where a foreign key is located in in
the table's column order (its "degree", to use the correct term); and the
presence of SWIMRESULTID in the PK of SPLIT has no relevance to the
relationship.

Unwelcome (?) advice:

If I've guessed right that you are working with a database design that was
inherited from a Paradox/Delphi legacy system, then it's important to
realise that all the Delphi stuff involving TTable and IndexDefs, etc., was
designed for Paradox and a few of its desktop-style allies (dBase, Access,
etc.). It is not at all useful for DBMSs that implement the relational
model according to the SQL standard, since it infers a physical ordering of
records on disk that is meaningless for Firebird. However, it is worse
than "meaningless". It is a serious obstruction to the design of good,
performance-oriented structures.

./heLen