| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: How to associate Foreign Key with an Index?
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:
Cheers,
JH
Received on Thu Feb 08 2007 - 05:13:08 CST
 |
 |