1 person found this helpful
This looks like one of those AskTom questions. If we follow the advice in https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1249800833250 ,
you need to create a function-based unique index on a function taking type and person as parameters, that returns person only if type is 1, and null otherwise.
Assuming type is a number and person is a varchar2, first create the function (using this DDL in a worksheet)
create or replace function person_of_type_one( p_person in varchar2, p_type in number ) return varchar2 DETERMINISTIC as begin if ( p_type = 1 ) then return p_person; else return NULL; end if; end;
then create the index (in a worksheet)
create unique index person_of_type_one_idx on <your_person_table>(person_of_type_one(person, type) )
I did not test this, just copied the AskTom case and modified it slightly. It seems that creating a virtual column in your table (Edit Table > Columns > Data Type > Virtual > Virtual Expression), then basing a unique index on it should also work, but I am not positive.