Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

CREATE TABLE METADATA  
(  
  PK_ID        NUMBER(10)                       NOT NULL,  
  OBJ_OWNER    VARCHAR2(35 BYTE)                NOT NULL,  
  OBJ_PACKAGE  VARCHAR2(35 BYTE),  
  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.
(Oracle 9.2.0.8)

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?

share|improve this question
1  
Is it as simple as f()=obj_type||','||fk_id? –  Jack Douglas Oct 31 '11 at 19:18
    
Well I'm a bit embarrassed I suggested that now! –  Jack Douglas Oct 31 '11 at 23:10
add comment

1 Answer 1

up vote 4 down vote accepted

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) );
share|improve this answer
    
A composite index will do the job thank you! –  kevinsky Oct 31 '11 at 21:05
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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