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 UQ_ACTION UNIQUE (action_id,action_type_id) 
-- 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.

  • Funnily enough, I've worked on an app with an identical table and foreign key definition. – Philᵀᴹ Nov 11 '12 at 17:18
  • @Mat: It's not redundant; if I didn't have it, I would not be able to have FK in other tables to (action_id,action_type_id) -- "ORA-02270: no matching unique or primary key for this column-list" – a1ex07 Nov 11 '12 at 17:19
  • @Phil : I guess it's quite common practice ; I had similar structure in SQLServer and Mysql myself; the only difference is that Oracle has a better separation between constraints and indexes and lets multiple constraints use the same index. I just wonder about possible pitfalls. – a1ex07 Nov 11 '12 at 17:22
  • Ah, didn't know that didn't work in this case. The index you have is really poor for backing a lookup on action_type_id though, performance-wise. – Mat Nov 11 '12 at 17:24
  • @a1ex07 I meant identical table and column names – Philᵀᴹ Nov 11 '12 at 17:25

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.

  • It's not redundant. Detail tables refer to (action_id, action_type_id). Each detail table has a check constraint on its action_type_id thus enforcing that for instance logging_action cannot contain anything but details for action_type_id = 1 (assuming 1 represents logging action). To add a foreign key it must be either unique constraint or primary key constraint in master table. – a1ex07 Nov 11 '12 at 17:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.