Oracle 11g Release 2

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table members
  ( MEMBER_ID integer not null primary key,
    TEAM_ID integer not null,
    NAME varchar(64) not null,
    IS_LEADER char(1)  default 'Y' not null check (IS_LEADER in ('Y','N'))
  ) ;
create unique index one_leader_per_team
  on members 
    (case when IS_LEADER = 'Y' then TEAM_ID end) ;
insert all
  into members values (1, 1, 'Alex', 'Y')
  into members values (2, 1, 'Bill', 'N')
  into members values (3, 1, 'Cath', 'N')
  into members values (4, 2, 'Diana', 'N')
  into members values (5, 2, 'Elsa', 'Y')
select * from dual ;
5 rows affected

insert all
  into members values (6, 3, 'Fay', 'Y')
  into members values (7, 3, 'George', 'Y')
select * from dual ;
ORA-00001: unique constraint (FIDDLE_GXZIEBCRSSJEYVCRAHNJ.ONE_LEADER_PER_TEAM) violated

insert into members values (8, 1, 'Harry', 'Y');
ORA-00001: unique constraint (FIDDLE_GXZIEBCRSSJEYVCRAHNJ.ONE_LEADER_PER_TEAM) violated

select * from members ;


MEMBER_ID
TEAM_ID
NAME
IS_LEADER


1
1
Alex
Y

2
1
Bill
N

3
1
Cath
N

4
2
Diana
N

5
2
Elsa
Y