Can I have a deferrable unique functional index in Oracle?

Question

I'd like to create a deferrable unique functional index in Oracle 10g.

I know how to create a unique functional index:

create unique index LIST_ITEM_ENTRY_NO_UNIQ
on LIST_ITEM (case status when 'cancel' then null else LIST_KEY end,
              case status when 'cancel' then null else ENTRY_NO end);

I know how to create a deferrable unique index:

alter table LIST_ITEM add constraint LIST_ITEM_ENTRY_NO_UNIQ
unique (LIST_KEY,ENTRY_NO) deferrable initially deferred;

Knowing these two things, I tried this:

alter table LIST_ITEM add constraint LIST_ITEM_ENTRY_NO_UNIQ
unique (case STATUS when 'cancel' then null else LIST_KEY end,
        case STATUS when 'cancel' then null else ENTRY_NO end)
deferrable initially deferred;

But I get an "ORA-00904 : invalid identifier" error. Either I've got the syntax wrong, or perhaps Oracle doesn't support deferrable functional indices? Could someone provide me with a solution or else a definitive answer?

Answer 1

I think you need the 11g virtual columns functionality. You'd create the function as a virtual column, then add the cosntraint on that.

Answer 2

Ask Tom addresses this issue. The answer is no, you can't create unique constraints based upon functional indexes.

Answer 3

Nice try, but according to the Oracle 10g Documentation, the syntax for CREATE INDEX and ALTER TABLE ADD CONSTRAINT are not interchangeable in this regard, which is why you got that syntax error:

CREATE INDEX ::=

    CREATE [ UNIQUE | BITMAP ] INDEX [ schema. ]index
      ON { cluster_index_clause
         | table_index_clause
         | bitmap_join_index_clause
         } ;

table_index_clause ::=

    [ schema. ]table [ t_alias ]
    (index_expr [ ASC | DESC ]
      [, index_expr [ ASC | DESC ] ]...)
    [ index_properties ]

index_expr ::= { column | column_expression }

Therefore CREATE INDEX allows column_expression, which is basically a "function-based index".

On the other hand:

ALTER TABLE ::=
ALTER TABLE [ schema. ]table
  [ alter_table_properties
  | column_clauses
  | constraint_clauses
  | alter_table_partitioning
  | alter_external_table_clauses
  | move_table_clause
  ]
  [ enable_disable_clause
  | { ENABLE | DISABLE }
    { TABLE LOCK | ALL TRIGGERS }
    [ enable_disable_clause
    | { ENABLE | DISABLE }
      { TABLE LOCK | ALL TRIGGERS }
    ]...
  ] ;

constraint_clauses ::=
{ ADD { out_of_line_constraint
        [ out_of_line_constraint ]...
      | out_of_line_REF_constraint
      }
| MODIFY { CONSTRAINT constraint
         | PRIMARY KEY
         | UNIQUE (column [, column ]...)
         }
         constraint_state
| RENAME CONSTRAINT old_name TO new_name
| drop_constraint_clause
}

out_of_line_constraint ::=
[ CONSTRAINT constraint_name ]
{ UNIQUE (column [, column ]...)
| PRIMARY KEY (column [, column ]...)
| FOREIGN KEY (column [, column ]...)
     references_clause
| CHECK (condition)
}
[ constraint_state ]

Therefore a UNIQUE constraint definition may only be column names, and cannot be column expressions.

You can do this in 11g using virtual columns, in 10g and earlier most people tend to create derived columns (along with the burden of keeping them up-to-date programmatically).