> However, since there are only five possible
> >values, the number of duplicates in this index are going to be
> >astronomical. Is there a way to avoid the creation of this index?
>
> No. In this case, you could avoid the index by implementing your

own validation using a CHECK constraint on Million_Row, e.g.,

>
> alter table Million_Row
> add constraint id_exists
> CHECK (exists (select 1 from Five_row
> where five_row_id = new.five_row_id ))
>

Partly true, but you will also need to add some update and delete
triggers to five_row to emulate the cascade/set null/no action etc of
the foreign key constraint when the key field in the parent table is
updated or the (parent) record deleted.

Furthermore, both check constraints and triggers are limited to *your*
*transactions'* *context*. IOW, if another simultaneous transaction
deletes or updates one of the records in five_row, your transaction
will still see the older record versions and will therefore allow
inconsistent data. The same is true when you wish to update or delete
a record in the parent table; you have no way of knowing about
concurrent DML that is relying on your record. Of course if Five_row
is never changed (without exclusive access), this will not be an issue.

I think what the OP is thinking about is that the index structure in
ODS 11.x has been enhanced to make garbage collection significantly
cheaper than 10.x, so possibly even with the foreign key index it
won't be as bad as they are imagining.

Adam