ORA-00001: unique constraint (JAVA2S.JOB_UNIQUE_IN_TEAMID) violated : Unique « Table « Oracle PL/SQL Tutorial

1. Oracle PL/SQL Tutorial
2. Table
3. Unique

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>
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 (JAVA2S.JOB_UNIQUE_IN_TEAMID) violated


SQL>
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>
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>