ORA-00001: unique constraint (JAVA2S.JOB_UNIQUE_IN_TEAMID) violated

   

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>

Related examples in the same category

1.	if a column is not explicitely defined as not null, nulls can be inserted multiple times
2.	Vialate the unique contraint: try to insert the same value
3.	A unique constraint can be extended over multiple columns
4.	Add unique containt to a varchar2 type column
5.	Create a table with 'unique deferrable initially immediate'
6.	Add unique constraints
7.	Setting a Unique Constraint
8.	Unique value column

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

Related examples in the same category