0

I have a table with the following columns:

TEAM_ID integer,
NAME varchar(64),
IS_LEADER char(1) not null default 'Y'

I want to apply the constraint that, in the same team, only a leader can be possible. This constraint applies only on a subset of rows, not to the whole table as, of course, multiple teams can have their leader.

How to write this kind of constraint in oracle?

4

You can use a unique index for that. Example:

create unique index one_leader_per_team
  on members 
    (case when IS_LEADER = 'Y' then TEAM_ID else null end) ;

What it does is store team_id - in the index - only for those rows that have IS_LEADER = 'Y' and nulls in the rest of the rows The UNIQUE ensures that have only one row per team with 'Y' and the nulls are ignored.

(I think that up to 11 and older version, Oracle doesn't store the nulls at all in the index, so the index is more space efficient - not sure if this has changed in version 12). That means that the Oracle index is equivalent to a filtered/partial index in other DBMS (SQL Server, PostgreSQL), i.e.:

-- this would work in SQL Server, Postgres 
create unique index one_leader_per_team
  on members (TEAM_ID)
  where (IS_LEADER = 'Y') ;

Test in dbfiddle.uk:

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_TITLKRLCAFRLTVCBXZVI.ONE_LEADER_PER_TEAM) violated
insert into members values (8, 1, 'Harry', 'Y');
ORA-00001: unique constraint (FIDDLE_TITLKRLCAFRLTVCBXZVI.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        

db<>fiddle here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.