Conditional Unique Index (on the PersonName in HR-XML)

Using a HR-XML schema as a database schema in Oracle, an “only 1 current name (or birthname) allowed per person” restriction on the PersonName table can be implemented by using the following code snippet:

create or replace function SingleCurrentName(
       NameTypeCode in varchar2,
       PersonID in varchar2
       )
return varchar2
DETERMINISTIC
  as
  begin
      if ( NameTypeCode = 'currentname' or NameTypeCode = 'birthname')
      then
          return PersonID || '/' || NameTypeCode;
      else
          return null;
      end if;
  end SingleCurrentName;

-- Create personname unique index
create unique index personnames_idx_1 on personnames( singlecurrentname(nametypecode,personid) );

-- Test unique index, should PASS
declare
   v_personid varchar2(36);
begin
   v_personid := get_formatted_guid;
   insert into persons (personid) values (v_personid);

   insert into personnames (personnameid, personid, nametypecode, formattedname)
   values (get_formatted_guid, v_personid, 'currentname', 'John Doe');

   insert into personnames (personnameid, personid, nametypecode, formattedname)
   values (get_formatted_guid, v_personid, 'birthname', 'John Doe');

   insert into personnames (personnameid, personid, nametypecode, formattedname)
   values (get_formatted_guid, v_personid, 'alias', 'John Doe');

   insert into personnames (personnameid, personid, nametypecode, formattedname)
   values (get_formatted_guid, v_personid, 'alias', 'John Doe');
end;
-- Test unique index, should FAIL
declare
   v_personid varchar2(36);
begin
   v_personid := get_formatted_guid;
   insert into persons (personid) values (v_personid);

   insert into personnames (personnameid, personid, nametypecode, formattedname)
   values (get_formatted_guid, v_personid, 'currentname', 'John Doe');

   insert into personnames (personnameid, personid, nametypecode, formattedname)
   values (get_formatted_guid, v_personid, 'currentname', 'John Doe');

   insert into personnames (personnameid, personid, nametypecode, formattedname)
   values (get_formatted_guid, v_personid, 'alias', 'John Doe');

   insert into personnames (personnameid, personid, nametypecode, formattedname)
   values (get_formatted_guid, v_personid, 'alias', 'John Doe');
end;

Implementation based on the following link:
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1249800833250
which also has nice comments on other solutions, e.g. without a function or by using a trigger.