Subject Re: [IB-Architect] Rebuilding foreign keys system indexes
Author Olivier Mascia

Thursday, November 02, 2000 4:23 PM, Ann Harrison wrote :

> 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.

I agree and second the idea, though some tool or function in the
engine should be able to report on primary/foreign keys without
indexes, with inactive indexes and/or inactive foreign key
constraint. Maybe the database statistics could highlight such
special uses. People may find performance incredibly low after having
inactivated / dropped some indexes if they forget to re-activate /
re-create them. That's a powerfull thing (I have uses for that) but
can be a malicious one.

> 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.

Good point. I indeed have some tables whose foreign keys do not
benefit from being indexed. I like this idea. If the foreign key is
there just to help ensure the value do exists in the reference table
(primary key) the foreign key index is of no use at that point. And
depending on the (good) design, the primary key might well never be
deleted nor updated. So again no point in having an index on the
foreign key. I definitely like it.

> 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.

I'm less convinced at first by this one. I suppose your goal is to
allow more easy data re-organization by dropping the constraint
enforcement. This way you could, temporarily (because it fits some
data re-organisation scheme) have data which would violate the
foreign key constraint. When the constraint will be re-activated,
would we only accept the change if no rows violate the constraint, or
would invalid rows be accepted ? I vote for checking all rows and
only accept the re-activation if there are no constraint violations.
I suppose it is related with the label 'a bit harder' you put on this

Olivier Mascia, om@...
T.I.P. Group S.A.,

Version: PGP Personal Privacy 6.5.8