Is it any potential problem with having 2 constraints suppored by one index in Oracle?

Question

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.

Answer 1

As Nat has noted your UQ_ACTION constraint duplicates the primary key constraint. Based on your FK usage into this table, your primary key is (action_id, action_type_id). As such it is redundant and should be removed. If you wish your primary key to be action_id alone, then your foreign keys must reference only action_id.

I believe you were correct in modeling action_id as the primary key. In this case change your child references. (Having the action type id in the foreign key violates normal form and forces redundant information into the referencing tables.). Child tables of your action table would have the action type id in their key if they apply to multiple types.

You may want a UK on action_id, but it is not necessary. If you have such a UK with a corresponding index, then I would reverse the order on the index containing (action_type_id, action_id). This will allow fast retrieval by action_id alone and by action_type_id where statistics indicate an index is useful.

However, to answer your question in cases where an index can be used for multiple constraints, no there is no penalty. In fact, the opposite is the case.

Maintaining indexes can be a relatively expensive operation. Adding redundant indexes does have a penalty. Each insert, update, or delete that changes the column in the redundant index will require additional work to update the index.

It appears you have recognized the value of not creating unused indexes. While common, foreign key indexes are not necessary in many cases. It is a good idea to have an index if you do deletes from the referenced table. A surrogate index beginning with the foreign key may result in more efficient queries. If you do frequent lookups by action_type_id, the index would be useful. It might be more useful to have a multi-column index with other values used to filter the result set.