Create unique index oracle with where clause

Question

I m trying to create a unique index with where clause like this:

CREATE UNIQUE INDEX cmt_unique_sid_yid_ct
          ON COMMENTARY (source_id, year_id)
       WHERE comment_type_id = (
         select comment_type_name 
         from comment_type 
         where comment_type_name = 'Final'
       );

Getting this error :

02158. 00000 -  "invalid CREATE INDEX option"
*Cause:    An option other than COMPRESS, NOCOMPRESS, PCTFREE, INITRANS,
           MAXTRANS, STORAGE, TABLESPACE, PARALLEL, NOPARALLEL, RECOVERABLE,
           UNRECOVERABLE, LOGGING, NOLOGGING, LOCAL, or GLOBAL was specified.
*Action:   Choose one of the valid CREATE INDEX options.

I m basically trying restrict comments with type Final from comment_type table, to only one row per source/year. So scope to :

comment_type_name | source_id | year_id

How can I do this with SQL, specifically oracle?

This may be the model you want asktom.oracle.com/pls/apex/…. However, you may consider doing this with a view to do the logic as well. — BriteSponge
– BriteSponge, Commented Jan 13, 2017 at 15:55
@Raj can you point me to the example, I found this oracle-base.com/articles/8i/function-based-indexes doesn't seem related, maybe I m not understanding this correctly — Gandalf StormCrow
– Gandalf StormCrow, Commented Jan 13, 2017 at 20:07

Balazs Papp · Accepted Answer · 2017-01-13 21:07:39Z

1

No such thing in Oracle, just some workaround with function based indexes:

CREATE UNIQUE INDEX cmt_unique_sid_yid_ct
ON COMMENTARY (
  case when comment_type_name = 'Final' then source_id end,
  case when comment_type_name = 'Final' then year_id end
);

Sample query that can use this index:

select * from commentary
where 
case when comment_type_name = 'Final' then source_id end = :B1
and case when comment_type_name = 'Final' then year_id end = :B2
;

answered Jan 13, 2017 at 21:07

Balazs Papp

41.5k2 gold badges29 silver badges47 bronze badges

Add a comment |

Stack Exchange Network

Create unique index oracle with where clause

1 Answer 1

Your Answer

Hot Network Questions

Create unique index oracle with where clause

1 Answer 1

Your Answer

Sign up or log in

Post as a guest

Related

Hot Network Questions