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.
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?