| Subject | Re: index and foreigner key |
|---|---|
| Author | Adam |
| Post date | 2006-08-02T23:39:38Z |
--- In firebird-support@yahoogroups.com, geo.mastique@... wrote:
You have 2 indices because WinDesign specifically declared an index as
well as declaring the a foreign key constraint which implicitly
creates an index.
The reason is pretty simple, it speeds up the check to make sure the
foreign key constraint is not being violated (in most cases, I wish
there was a flag to disable it on certain low selectivity indices, but
that is for another day).
Do not create an ascending index on any set of fields that is defined
as the primary key, any set of fields that is constrained as unique,
or any field that is a foreign key.
1. It is unnecessary, it takes up space in your database, uses up
additional resources during inserts, updates and deletes, and provides
no benefit to selects, updates or deletes.
2. In the worst case, the optimiser may be confused and choose to use
neither.
Another thing, do not reply to another post. Yahoo Groups is threaded,
which means that people using Newsreaders can choose to ignore an
entire thread if it is not of interest for them. These people will
never see your question. Changing the subject is not sufficient, the
flag is inside the header of the email. Instead, compose a new email
and everyone will see it.
Adam
>sql
> I use Windesign so as to create database model and then generate the
> script "create database...".codecountry ?
>
> i run the script to create my database on firebird, it is OK.
>
> when i look to a table of the database , i have 2 index for the same
> field in case of that field is a foreigner key of another table.
> for exemple :
> - codecountry is primary key field of a table called "listcountries".
> - codecountry is used as a foreigner key in another table called
> "infocountries".
>
> why in "infocountries" indices i have 2 indices for the field
> first indice is : I_infocountries_codecountry, so the indice forthe field
> second indice is : I_FK_infocountries_codecountry, so the indice forJulien,
> the foreigner key field, witch is the same !
>
> must i keep the 2 indices ?
> thanks for your help and sorry for my low knowledge in firebird.
You have 2 indices because WinDesign specifically declared an index as
well as declaring the a foreign key constraint which implicitly
creates an index.
The reason is pretty simple, it speeds up the check to make sure the
foreign key constraint is not being violated (in most cases, I wish
there was a flag to disable it on certain low selectivity indices, but
that is for another day).
Do not create an ascending index on any set of fields that is defined
as the primary key, any set of fields that is constrained as unique,
or any field that is a foreign key.
1. It is unnecessary, it takes up space in your database, uses up
additional resources during inserts, updates and deletes, and provides
no benefit to selects, updates or deletes.
2. In the worst case, the optimiser may be confused and choose to use
neither.
Another thing, do not reply to another post. Yahoo Groups is threaded,
which means that people using Newsreaders can choose to ignore an
entire thread if it is not of interest for them. These people will
never see your question. Changing the subject is not sufficient, the
flag is inside the header of the email. Instead, compose a new email
and everyone will see it.
Adam