Clever Geek Handbook

Indexing foreign keys

How to index a foreign key in Oracle?

+2


source to share


2 answers 


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)


A column in another table (the one you are referring to) must be PRIMARY KEY

or have a constraint UNIQUE

defined on it, which means it already has an index.

+2


source


You cannot index a foreign key constraint, but you can index the columns where the foreign key is defined.



Regards, Rob.

0


source






More articles:


All Articles