Are foreign keys that are part of a composite key automatically indexed in Oracle?

Question

For instance, if I have a students table:

CREATE TABLE student (
    student_id NUMBER(8),
    student_name VARCHAR2(30),
    CONSTRAINT pk_student PRIMARY KEY (student_id)
);

And a subject table:

CREATE TABLE subject (
    subject_id NUMBER(8),
    subject_name VARCHAR2(30),
    CONSTRAINT pk_subject PRIMARY KEY (subject_id)
);

And then I create a third table of student's favorites subjects:

CREATE TABLE fav_sub (
    student_id NUMBER(8),
    subject_it NUMBER(8),
    CONSTRAINT pk_fav_sub PRIMARY KEY (student_id, subject_id),
    CONSTRAINT fk_1_fav_sub FOREIGN KEY (student_id) REFERENCES student(student_id),
    CONSTRAINT fk_2_fav_sub FOREIGN KEY (subject_id) REFERENCES subject(subject_id)
);

Do I then need to manually create indexes for the foreign keys in the fav_sub table such as:

CREATE INDEX in_1_fav_sub ON fav_sub(student_id);
CREATE INDEX in_2_fav_sub ON fav_sub(subject_id);

Or are the indexes for the foreign keys automatically created by the database, since they're part of the composite key?

Edit: Just to clarify, I'm not asking if an index for a foreign key is automatically created, I'm asking if an index for a foreign key is created WHEN it's part of a composite key, since Oracle automatically indexes primary keys.

possible duplicate of [Do I need to create indexes on foreign keys?](http://stackoverflow.com/questions/4127206/do-i-need-to-create-indexes-on-foreign-keys) — OldProgrammer, Aug 16 '15 at 19:03
Oracle only creates indexes automatically when absolutely required (i.e. to support a unique constraint). Otherwise, it never creates an index for you unless you explicitly tell it to. — Jeffrey Kemp, Aug 17 '15 at 05:45
Also, it has nothing to do with whether it's a composite or simple key. — Jeffrey Kemp, Aug 17 '15 at 05:46

score 1 · Accepted Answer · answered Aug 16 '15 at 19:04

The creation of the primary key indexes the combination of [student_id, subject_id]. Adding foreign key constraints to each individual column does not create an index on them, and if you want it (which is probably a good idea), you'd have to manually create it.

Are foreign keys that are part of a composite key automatically indexed in Oracle?

1 Answers1