Oracle Conditional Index with two identifiers

indexing, oracle

Solution

Yes, but you have to perform a CASE per column:

CREATE UNIQUE INDEX idx_dup_wfc
    ON WF_WORKFLOW_CLASS 
     (CASE WHEN is_active = 1 THEN NAME END
     ,CASE WHEN is_active = 1 THEN DEPT_OWNER END
     )

(The ELSE in your code is superfluous).

Problem

Is it possible to create a conditional index on two columns? ``` CREATE UNIQUE INDEX idx_dup_wfc ON WF_WORKFLOW_CLASS (CASE WHEN is_active = 1 THEN (NAME, DEPT_OWNER) ELSE NULL END) ``` returns `ORA-00906: missing left parenthesis` however the following works ``` CREATE UNIQUE INDEX idx_dup_wfc ON WF_WORKFLOW_CLASS (CASE WHEN is_active = 1 THEN NAME ELSE NULL END) ```

Original source