I have the following table :
CREATE TABLE action (action_id INT NOT NULL, action_type_id INT NOT NULL, action_date DATE NOT NULL); CREATE INDEX IDX_ACTION_ACT_TYPE_ACT ON action(action_id, action_type_id); ALTER TABLE action ADD CONSTRAINT PK_ACTION PRIMARY KEY (action_id) USING INDEX IDX_ACTION_ACT_TYPE_ACT; ALTER TABLE action ADD CONSTRAINT UQ_ACTION UNIQUE (action_id,action_type_id) USING INDEX IDX_ACTION_ACT_TYPE_ACT; -- I think the line below is irrelevant in the context of my question, but -- in case I'm wrong... ALTER TABLE action ADD CONSTRAINT FK_action_type_id FOREIGN KEY(action_type_id) REFERENCES action_type(action_type_id); -- Since action_type is a lookup added for normalization sake with no updates/deletes, -- I don't see any value in having an index on FK column.
It's a common parent table for many detail tables that store extra information for a particular action.
action_type_id is a part of unique constraint to let detail tables have FK to (action_id,action_type_id) and to enforce that each detail table stores only information it's supposed to store.
I wonder if there is any performance penalty for using the same index in 2 constraints compared to creating 2 indexes... In my opinion it should behave better than 2 indexes, but maybe I'm missing something. I'm using Oracle 10g if that matters.
Thank you for your answers.