How to associate Foreign Key with an Index?

Having recently started working with Oracle, after many years with InterBase and SQL Server -- I am having a hard time figuring out how to make sure that the foreign keys I create have indexes. In InterBase, the very act of creating a foreign key creates the associated index, and you cannot really do much with that index. You cannot create a FK in InterBase without having InterBase create an index for you in the background.

So, in Oracle you can obviously create a FK without an index. I have been using the following syntax to create my foreign keys:

create table MY_TABLE (

   MY_KEY Number(*,0) NOT NULL,
   FK_FIELD Number(*,0) NOT NULL,
   constraint PK_MY_TABLE primary key (MY_KEY),    constraint FK_MY_TABLE foreign key (FK_FIELD) references OTHER_TABLE );

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.

All the examples that I have found (dozens of examples) show the basic syntax used above. 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? Received on Thu Feb 08 2007 - 03:48:25 CST