Conditional uniqueness
June 25, 2014 2 Comments
A quick fly through the options for conditional uniqueness.
Requirement #1: I want uniqueness on a column but only under certain conditions.
For example, I have an active flag and I want to make sure there is only one active record for a particular attribute but there can be many inactive rows.
Initial setup:
create table t1 (col1 number not null ,col2 varchar2(24) not null ,is_active number(1) not null ,constraint pk_t1 primary key (col1) ,constraint ck_t1_is_active check (is_active in (1,0)));
Solution #1: A unique index on an expression which evaluates to null when the condition is not met.
create unique index i_t1 on t1 (case when is_active = 1 then col2 end); unique index I_T1 created. insert into t1 values(1,'SHAGGY',1); 1 rows inserted. insert into t1 values(2,'SHAGGY',1); SQL Error: ORA-00001: unique constraint (I_T1) violated 00001. 00000 - "unique constraint (%s.%s) violated" *Cause: An UPDATE or INSERT statement attempted to insert a duplicate key. For Trusted Oracle configured in DBMS MAC mode, you may see this message if a duplicate entry exists at a different level. *Action: Either remove the unique restriction or do not insert the key.
Only one active SHAGGY allowed.
But multiple inactives allowed:
insert into t1 values(2,'SHAGGY',0); 1 rows inserted. insert into t1 values(3,'SHAGGY',0); 1 rows inserted.
Solution #2: A virtual column with a unique constraint
drop index i_t1; index I_T1 dropped. alter table t1 add (vc_col2 varchar2(24) generated always as (case when is_active = 1 then col2 end)); table T1 altered. alter table t1 add constraint uk_t1 unique (vc_col2); table T1 altered.
Note that now we have a virtual column we have to be very aware of insert statements with no explicit column list:
insert into t1 values(4,'SCOOBY',1); SQL Error: ORA-00947: not enough values 00947. 00000 - "not enough values"
Unless we’re lucky enough to be on 12c and use the INVISIBLE syntax:
alter table t1 add (vc_col2 varchar2(24) invisible generated always as (case when is_active = 1 then col2 end));
But as this example is on 11.2.0.3:
insert into t1 (col1, col2, is_active) values(4,'SCOOBY',1); 1 rows inserted. insert into t1 (col1, col2, is_active) values(5,'SCOOBY',1); SQL Error: ORA-00001: unique constraint (UK_T1) violated 00001. 00000 - "unique constraint (%s.%s) violated" *Cause: An UPDATE or INSERT statement attempted to insert a duplicate key. For Trusted Oracle configured in DBMS MAC mode, you may see this message if a duplicate entry exists at a different level. *Action: Either remove the unique restriction or do not insert the key. insert into t1 (col1, col2, is_active) values(5,'SCOOBY',0); 1 rows inserted. insert into t1 (col1, col2, is_active) values(6,'SCOOBY',0); 1 rows inserted.
Requirement #2: Sorry we forgot to tell you that we insert the new row first and the update the old one to be inactive so we need deferred constraint (hmmm!)
In which case, you can’t have deferred uniqueness on an index so the only option is the virtual column.
alter table t1 drop constraint uk_t1; table T1 altered. alter table t1 add constraint uk_t1 unique (vc_col2) deferrable initially deferred; table T1 altered. insert into t1 (col1, col2, is_active) values(7,'FRED',1); 1 rows inserted. insert into t1 (col1, col2, is_active) values(8,'FRED',1); 1 rows inserted. commit; SQL Error: ORA-02091: transaction rolled back ORA-00001: unique constraint (.UK_T1) violated 02091. 00000 - "transaction rolled back" *Cause: Also see error 2092. If the transaction is aborted at a remote site then you will only see 2091; if aborted at host then you will see 2092 and 2091. *Action: Add rollback segment and retry the transaction. insert into t1 (col1, col2, is_active) values(7,'FRED',1); 1 rows inserted. insert into t1 (col1, col2, is_active) values(8,'FRED',1); 1 rows inserted. update t1 set is_active = 0 where col1 = 7; 1 rows updated. commit; committed.
See previous post on similar approach for conditional foreign key
Any idea if CTAS with a virtual column is possible in 12c ?
compare
would be handy if you virtual columns not “at the end of the table”, but somewhere in the middle.
Hi Matthias,
I get the same ORA-01773 error in 12.1.0.1.0.
Regards,
Dominic