Indexing Foreign keys

Question

How do I index a foreign key in Oracle?

Answer 1

CREATE TABLE reftable (id INT NOT NULL PRIMARY KEY)

CREATE TABLE mytable (id INT NOT NULL, ref INT NOT NULL)

ALTER TABLE mytable
ADD CONSTRAINT fk_mytable_ref_reftable
FOREIGN KEY (ref) REFERENCES reftable (id)

CREATE INDEX ix_mytable_ref ON mytable (ref)

The column in another table (the one you are referencing) must be a PRIMARY KEY or have a UNIQUE constraint defined on it, which means it already has an index.

Answer 2

You cannot index the foreign key constraint itself, but you can index the columns the foreign key is defined on.

Regards, Rob.