I have a search table which has a generated primary key PK_ID,and also has primary keys from three or four other tables in the FK_ID column

  PK_ID        NUMBER(10)                       NOT NULL,  
  OBJ_OWNER    VARCHAR2(35 BYTE)                NOT NULL,  
  OBJ_NAME     VARCHAR2(35 BYTE)                NOT NULL,  
  OBJ_TYPE     NUMBER(10)                       NOT NULL,  
  FK_ID        NUMBER(10)                       NOT NULL  

For example, when OBJ_TYPE = 1 then every FK_ID should be unique for that value of OBJ_TYPE.

I think I can do this with a function based index but the link to Tom Kyte's discussion on this is broken across a few web sites and I'm not sure how.

A composite index will do the job. A bonus would be if a function based index could be used as a foreign key for the other tables. Is this possible?


I'm not completely sure that I understand what you mean by "every FK_ID should be unique for that value of OBJ_TYPE".

If you are trying to say that each FK_ID has to be unique within each OBJ_TYPE, then it sounds like you just want a composite index on (OBJ_TYPE, FK_ID).

CREATE UNIQUE INDEX idx_uniq_metadata
   ON metadata( obj_type, fk_id );

If, on the other hand, you are trying to say that if OBJ_TYPE = 1 then FK_ID must be unique but you can have duplicate FK_ID values if the OBJ_TYPE is something else, then you'd want a function-based unique index.

CREATE UNIQUE INDEX idx_uniq_metadata2
   ON metadata( (case when obj_type = 1 
                      then fk_id 
                      else null 
                   end) );
| improve this answer | |
  • A composite index will do the job thank you! – kevinsky Oct 31 '11 at 21:05

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.