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?
Answer :
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
;