Muhammad -- Thanks for the question regarding "Using CASE/DECODE in constraint", version 10.2.

Submitted on 7-Sep-2009 3:06 Central time zone

Tom's latest followup | Bookmark | Bottom

Last updated 7-Sep-2009 8:14

You Asked

I have created a unique index on a table.

Create Unique Index xyz on TableName(Case When col1='T' Then col2 else col3 End);

I want to make it defferable to be executed on commit. but i know this option is not available in Idex creation. If I make it a constraint only then I can use deferrable clause. But in costraint I am unable to use CASE or DECODE clause. Is there any option that I can use CASE or DECODE in constraint.

Regards,

and we said...

In 11g, with virtual columns - yes, you can do this.

In 10g and before, without virtual columns - no, there are no deferrable constraints using this technique.

ops$tkyte%ORA11GR1> create table t
  2  ( col1 varchar2(1),
  3    col2 int,
  4    col3 int,
  5    vc   as (Case When col1='T' Then col2 else col3 End)
  6  )
  7  /

Table created.

ops$tkyte%ORA11GR1> alter table t add constraint vc_unique unique(vc) deferrable
  2  /

Table altered.

ops$tkyte%ORA11GR1>
ops$tkyte%ORA11GR1> insert into t (col1,col2,col3) values ( 'T', 1, 2 );

1 row created.

ops$tkyte%ORA11GR1> insert into t (col1,col2,col3) values ( 'T', 1, 3 );
insert into t (col1,col2,col3) values ( 'T', 1, 3 )
*
ERROR at line 1:
ORA-00001: unique constraint (OPS$TKYTE.VC_UNIQUE) violated


ops$tkyte%ORA11GR1> insert into t (col1,col2,col3) values ( 'T', 10, 3 );

1 row created.

ops$tkyte%ORA11GR1> rollback;

Rollback complete.

ops$tkyte%ORA11GR1> set constraints all deferred;

Constraint set.

ops$tkyte%ORA11GR1> insert into t (col1,col2,col3) values ( 'T', 1, 2 );

1 row created.

ops$tkyte%ORA11GR1> insert into t (col1,col2,col3) values ( 'T', 1, 3 );

1 row created.

ops$tkyte%ORA11GR1> insert into t (col1,col2,col3) values ( 'T', 10, 3 );

1 row created.

ops$tkyte%ORA11GR1> commit;
commit
*
ERROR at line 1:
ORA-02091: transaction rolled back
ORA-00001: unique constraint (OPS$TKYTE.VC_UNIQUE) violated

Reviews

in 10g you might use a trigger instead September 7, 2009 - 9am Central time zone

Bookmark | Bottom | Top

Reviewer: Sokrates

SQL> create table t
  2  ( col1 varchar2(1),
  3   col2 int,
  4   col3 int,
  5   vc int
  6  )
  7  /

Table created.
SQL> create trigger maintain_vc
2  before insert or update on t
3  for each row
4  begin
5  :new.vc := Case When :new.col1='T' Then :new.col2 else :new.col3 End;
6  end maintain_vc;
7 /

Trigger created.

SQL> alter table t add constraint vc_unique unique(vc) deferrable
  2  /

Table altered.

SQL> set constraints all deferred;

Constraint set.

SQL> insert into t (col1,col2,col3) values ( 'T', 1, 2 );

1 row created.

SQL> insert into t (col1,col2,col3) values ( 'T', 1, 3 );


1 row created.

SQL> SQL> insert into t (col1,col2,col3) values ( 'T', 10, 3 );

1 row created.

SQL> commit;
commit
*
ERROR at line 1:
ORA-02091: transaction rolled back
ORA-00001: unique constraint (SOKRATES.VC_UNIQUE) violated

Muhammad: if you use this technique, be sure that there is only one before-row-trigger on your table.
(others may fire after maintain_vc and may change col1, col2, col3 again )

Try ON COMMIT FAST REFRESH MVIEW September 7, 2009 - 12pm Central time zone

Bookmark | Bottom | Top

Reviewer: Chris Poole from London, UK

Or you could just use a materialized view which refreshes on commit and put the constraint on that. It would take a bit more space that the theoretical index but I'm working on that:

SQL>create table t (
  2    col1 varchar2(1),
  3    col2 int,
  4    col3 int
  5  );

Table created.

SQL>
SQL>create materialized view log on t with rowid;

Materialized view log created.

SQL>
SQL>create materialized view m
  2  refresh fast on commit with rowid
  3  as
  4  select
  5   decode(
  6      col1,
  7      'T', col2,
  8      col3
  9    ) as vc
 10  from
 11    t;

Materialized view created.

SQL>
SQL>alter materialized view m add constraint vc_unique primary key (vc);

Materialized view altered.

SQL>
SQL>insert into t (col1,col2,col3) values ( 'T', 1, 2 );

1 row created.

SQL>
SQL>insert into t (col1,col2,col3) values ( 'T', 1, 3 );

1 row created.

SQL>
SQL>insert into t (col1,col2,col3) values ( 'T', 10, 3 );

1 row created.

SQL>
SQL>
SQL>commit;
commit
*
ERROR at line 1:
ORA-12008: error in materialized view refresh path
ORA-00001: unique constraint (OPS$POOLCHR.VC_UNIQUE) violated

SQL>

mview limitation September 8, 2009 - 7am Central time zone

Bookmark | Bottom | Top

Reviewer: Laurent Schneider from Switzerland

I like the mview approach, but it has quite a lot of limitation.

For instance,

SQL> alter table lsc_t shrink space ;
alter table lsc_t shrink space
*
ERROR at line 1:
ORA-10652: Object has on-commit materialized views


SQL> truncate table lsc_t;

Table truncated.

SQL> insert into lsc_t (col1,col2,col3) values ( 'T', 1, 2 );

1 row created.

SQL> commit;
commit
*
ERROR at line 1:
ORA-12034: materialized view log on "SCOTT"."LSC_T" younger than last refresh

Write a Review