Firebird foreign key question
lazarus / 16-Databases

----------------------------------------------------------------------
patyi · 2014-08-12 09:15

Hi !

"When creating a foreign key index is automatically created. This
index is applied for speeding up the inquiries using join of tables,
as well as for checking the conditions of the foreign key." (from
http://ibsurgeon.com/en/articles/indices-interbase-and-firebird/)

Does this mean that the foreign key created on particular column can
be used as an ordinary index ?
I don't have to create additional index on the same field ?

Thanks, Patyi.

----------------------------------------------------------------------
mangakissa · 2014-08-12 09:29

As far as I know Firebird doesn't create indexes itself.
A foreign key can only works with an ordinary index.
You don't use a foreign key for joined tables but for a relation of
two tables so that records can not deleted if a relation still exists.
As long as you have indexes, you can use them.

----------------------------------------------------------------------
rvk · 2014-08-12 09:58

Yes. From your own source:
QuoteWhen creating a foreign key index is automatically created.
In my Firebird databases there are indexes created automatically for
foreign keys (with the same name as the Foreign Key CONSTRAINT-name).

When doing a simple select with "order by foreign-key-field"  I can
see from the PLAN it uses that index too.
(So you don't have to create an additional index on that same field)

----------------------------------------------------------------------
patyi · 2014-08-12 10:16

Thanks, rvk !

This not mentioned anywhere in documentation, and this question is
bothering me a log time of until now !
(Yes I'm blind ! The query plan is the answer ...)