--- In firebird-support@yahoogroups.com, "Christian Kaufmann"
<ch.kaufmann@s...> wrote:

>
> Hi,
>
> I have a question about foreign keys. If I create a foreign key
> constraint, does FB not reuse existing indexes? It looks like there is
> always a new index created:
>
> 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);
>
> 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;
>
> Is there a way to avoid such (unnecessary) indexes?

No, most of the time when you create a foreign key, you will want to
join on that field etc. There are cases where a foreign key index will
give very bad selectivity, for example if you had some electoral
system and you had country of origin, most of the records would have
the same country of origin, so that foreign key index wouldn't help
too much.

If you create another compount index, then at least you give the
optimiser the oportunity to use that index, so not all is lost.

Adam