| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: [Q] Foreign keys and indexes
On Sun, 25 Jan 1998 18:56:49 GMT, martinj(replace this with @)xs4all.nl (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;
Foreign keys do not create indexes (primary keys use a unique index, but foreign
keys do not create any).
"Using index" is not valid on a foreign key constraint. You must create the index yourself if you want it.
The way you are doing it (minus the using index clause and the enable) is OK. Just create the foreign key and then index it. You never need to disable or reenable it as you are.
See the chapter in the Application developers guide, chapter 6 called "Concurrency Control, Indexes, and Foreign Keys" as to the reason you want to index your foreign keys (and yes, as long as your foreign key is on the leading edge of the index -- as your example is -- it is considered indexed and will prevent the locking discussed in that chapter).
>
>Many thanks in advance.
>-------------------------------------------------------------------------------
>--- Martin Jesterhoudt
>--- martinj(replace this by @)xs4all.nl
>-------------------------------------------------------------------------------
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Bethesda MD
http://govt.us.oracle.com/ -- downloadable utilities
Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Mon Jan 26 1998 - 00:00:00 CST
 |
 |