Re: [Q] Foreign keys and indexes

Hi Martin,

As far as I know oracle won't create any index for fk's if you don't specify explicit. So:

ALTER TABLE EMP
ADD CONSTRAINT EMP_FK
FOREIGN KEY (DEPTNO) REFERENCES DEPT (DEPTNO) ;

CREATE INDEX TEST ON EMP (DEPTNO, EXTRA_FIELD); Would do your job though I'm in doubt wether this index would be used for accessing parent table.

replace this with @ Martin Jesterhoudt wrote:
>
> Hi,
>
> I want to create a foreign key constraint and use an index. But if I
> want the foreign key field to be used together with another field in
> another index, I don't want to create another index for the foreign
> key. If I don't specify 'USING INDEX', does Oracle create an index by
> itself?
>
> Assume the usage of EMPLOYEE (EMP) and DEPARTMENT (DEPT). Is the
> following order correct? Can I leave the 'USING INDEX' out?
>
> ALTER TABLE EMP
> ADD CONSTRAINT EMP_FK
> FOREIGN KEY (DEPTNO) REFERENCES DEPT (DEPTNO)
> USING INDEX
> DISABLE;
>
> CREATE INDEX TEST ON EMP (DEPTNO, EXTRA_FIELD);
>
> ALTER TABLE EMP
> MODIFY CONSTRAINT EMP_FK
> ENABLE;
>
> Many thanks in advance.
> -------------------------------------------------------------------------------
> --- Martin Jesterhoudt
> --- martinj(replace this by @)xs4all.nl
> -------------------------------------------------------------------------------
 

-- 
Regards

Matthias Gresz    :-)