SQL> create table emp
2 (emp_ID number primary key,
3 teamid number,
4 job varchar2(100),
5 status varchar2(20) check (status in ('ACTIVE', 'INACTIVE'))
6 );
Table created.
SQL>
SQL> create UNIQUE index job_unique_in_teamid on emp
2 ( case when status = 'ACTIVE' then teamid else null end,
3 case when status = 'ACTIVE' then job else null end
4 )
5 /
Index created.
SQL>
SQL> insert into emp(emp_id,teamid,job,status)values( 1, 10, 'a', 'ACTIVE' );
1 row created.
SQL>
SQL> insert into emp(emp_id,teamid,job,status)values( 2, 10, 'a', 'ACTIVE' );
insert into emp(emp_id,teamid,job,status)values( 2, 10, 'a', 'ACTIVE' )
*
ERROR at line 1:
ORA-00001: unique constraint (RNTSOFT.JOB_UNIQUE_IN_TEAMID) violated
SQL>
SQL> update emp
2 set status = 'INACTIVE'
3 where emp_id = 1
4 and teamid = 10
5 and status = 'ACTIVE';
1 row updated.
SQL>
SQL> insert into emp(emp_id,teamid,job,status)values( 2, 10, 'a', 'ACTIVE' );
1 row created.
SQL>
SQL> drop table emp;
Table dropped.
SQL>