[Firebird-devel] RFE: Allow control over constraint indices

[Eyal <ey...@re...>]

Hi,

Firebird generates indices automatically for PK, FK and Unique 
constraints. Those indices are sometimes redundant and can't be 
controlled (dropped, de-activated) by standard index manipulation commands.

I suggest to improve the handling of constraint indices as follows:


1. For FKs, there is no need to generate an index on the referring 
columns. Either make index generation optional by explicit user request, 
or leave current behavior but add an option to prevent index generation.

In theory an index isn't required to enforce PK and Unique constraints 
as well. Without proper indices enforcing those constraints may be very 
slow, except if the table is small and the cost of using an index may be 
higher than accessing the data.


2. For all types of constraints - allow using an existing index (after 
verifying it's appropriate for the job). There is already an existing 
syntax to support it.

Starting with v1.5 constraint definitions can contain a USING clause to 
specify a name for the soon to be created index (see v1.5 Release Notes 
page 8). This exact same syntax can be used to also accept the name of 
an existing index.

Note that an existing index can serve also if it contains more columns 
than required by the constraint, provided the constrained columns are 
the first segments of the index. For example a PK on an ID field can use 
an exisitng unique index on ID + START_DATE + NAME.


3. Allow de-activation and re-activation of constraint indices.


4. Allow DROP INDEX for constraint indices. As in point #1 above, this 
is usually safe for FK indices, but maybe also to PK and Unique too.


Comments?

Eyal.