[Q] Foreign keys and indexes

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

  ---