Re: How to associate Foreign Key with an Index?

On Feb 8, 12:13 pm, "John K. Hinsdale" <h..._at_alma.com> wrote:
> On Feb 8, 4:48 am, MRCarver <mrcnewGr..._at_charter.net> wrote:
>
> > I am having a hard time figuring out how to
> > make sure that the foreign keys I create have indexes.
>
> Oracle does it for you. When you create a FK, the child column(s),
> in the table being referenced, must be constrainted to be unqiue
> by virtue of a UNIQUE or PRIMARY KEY constraint, either of which
> requires an index. These dependent constraints must be created
> prior to, or concurrent with (as in your example) the foreign
> key.
>
> > In InterBase, the
> > very act of creating a foreign key creates the associated index, and you
> > cannot really do much with that index.
>
> Not sure what you mean by "do much with", but the primary
> key and unique constraints' indexes in Oracle will be
> used for efficiency purposes if they can be. But their
> main purpose is to provide a scalable way to enforce uniqueness
> of the indexed column(s) so that the FK will keep integrity.
>
> > You cannot create a FK in
> > InterBase without having InterBase create an index for you in the
> > background.
>
> In Oracle, you cannot create a FK without having also (explicitly)
> creating a PK or UNIQUE constraint, which (implicitly) creates
> an underlying index. Actually you can be somewhat explicit
> about the underlying index, too, giving storage guidelines, etc.
> Oracle's pretty good about giving you control over things.
>
> > So, in Oracle you can obviously create a FK without an index.
>
> No, there has to be one, somewhere, to guarantee a child row when
> referred to, is un-ambiguous.
>
> > When I examine the constraints for this table, it shows that the FK
> > constraint has no index that it uses. So, I manually created an index on
> > the FK_FIELD column. The constraint still shows that it has no
> > associated index.
>
> Look at the indexes on OTHER_TABLE, and you should see one on
> the column FK_FIELD. The index is on the table being referred
> to, not the table with the FK.
>
> > Is there some syntax to specifically link a foreign
> > key to a particular index, or does the index have to exist when I create
> > the fk constraint?
>
> The PK or UNIQUE constraint must be created prior to, or concurrent
> with, the FK which requires it. You need not worry about "forgetting"
> to do it; Oracle won't allow otherwise. This is part of Oracle's
> "saving
> you from yourself." ;)
>
> Example:
>
> SQL> create table t1 ( n number );
>
> Table created.
>
> SQL> create table t2 ( n number,
> 2 constraint fk_t2 foreign key (n) references t1(n))
> 3 ;
> constraint fk_t2 foreign key (n) references t1(n))
> *
> ERROR at line 2:
> ORA-02270: no matching unique or primary key for this column-list
>
> Cheers,
> JH

Sorry but you are fully mistaken.
The error message refers to t1, not to the constraint. And FKs do NOT have to be indexed. They'd better be, but a FK is NOT automatically indexed when you create the FK. This applies to PKs and UQs ONLY.

--
Sybrand Bakker
Senior Oracle DBA