At 10:26 AM 11/2/2000 +0100, Olivier Mascia wrote:

>The system index automatically defined on a foreign key column cannot
>be set inactive then active in order to rebuild it. It can't be
>dropped and recreated too. So after a mass insertion in a table
>with foreign keys, a set statistics is all what can be done to the indexes
>to get them back in better shape.

>Here is the design question : why are the indexes on foreign keys
>made mandatory ? Wouldn't it be acceptable to make them 'dropable' or
>at least allow them to be set inactive (then active again) ?

Not only acceptable, but very desirable. Here's what I would like to
do.

1) Allow all indexes to be [de]activated and dropped. This one
is an easy change.

2) Extend the foreign key constraint definition language with
the option [[NO ]INDEXES]. The default would be INDEXES.
NO INDEXES will create the constraint without creating
any new indexes. This one is a relatively easy change.

3) Extend the foreign key constraint definition language with
the option [[IN]ACTIVE]. The default would be ACTIVE.
INACTIVE will create the constraint definition (so higher
level languages that use it for navigation) but not enforce
it, except in terms of dependencies - an error if you try
to delete columns or tables referenced in the constraint.
This is a bit harder, but, I think, not very hard.


Regards,

Ann