Foreign Keys

create table contact (id number
                     ,referred_by number
                     ,user_refer_type varchar2(1)
                     ,prospect_refer_type varchar2(1));

create table users(id number, refer_type varchar2(1) default 'U');

create table prospect(id number,refer_type varchar2(1) default 'P');

alter table users    add constraint u_pk primary key(id,refer_type);
alter table prospect add constraint p_pk primary key(id,refer_type);

alter table contact add constraint c1_fk foreign key
(referred_by, user_refer_type) references users (id, refer_type);

alter table contact
add constraint c2_fk foreign key (referred_by, prospect_refer_type)
references prospect (id, refer_type);

insert into users (id) values (1);

insert into prospect (id) values (2);

insert into contact (id, referred_by, user_refer_type)
values (1, 1, 'U');

insert into contact (id, referred_by, prospect_refer_type)
values  (2, 2, 'P');

insert into contact (id) values  (3);

Mandatory/optional property of FK is related to mandatory/optional property of FK columns:

Mandatory FK - FK columns will be "not null"

Optional  FK - FK columns will allow nulls