Kann ich einen aufschiebbaren eindeutigen funktionalen Index in Oracle haben?

Question

Ich möchte einen aufschiebbaren eindeutigen funktionalen Index in Oracle 10g erstellen.

Ich weiß, wie man einen eindeutigen funktionalen Index erstellt:

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);

Ich weiß, wie man einen aufschiebbaren eindeutigen Index erstellt:

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

Da ich diese beiden Dinge kannte, habe ich dies versucht:

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;

Aber ich erhalte die Fehlermeldung "ORA-00904: Ungültiger Bezeichner". Entweder habe ich die Syntax falsch verstanden, oder vielleicht unterstützt Oracle keine aufschiebbaren funktionalen Indizes? Könnte mir jemand eine Lösung oder eine endgültige Antwort geben?

Answer 1

Netter Versuch, aber laut der Oracle 10g-Dokumentation sind die Syntax für CREATE INDEX und ALTER TABLE ADD CONSTRAINT in dieser Hinsicht nicht austauschbar, weshalb Sie diesen Syntaxfehler erhalten haben:

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 }

Deshalb erlaubt CREATE INDEX den Ausdruck column_expression, der im Grunde ein "funktionsbasierter Index" ist.

Andererseits:

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 ]

Daher kann eine UNIQUE-Beschränkungsdefinition nur aus Spaltennamen und nicht aus Spaltenausdrücken bestehen.

Sie können dies in 11g mit virtuellen Spalten tun, in 10g und früher neigen die meisten Leute dazu, abgeleitete Spalten zu erstellen (zusammen mit der Last, sie programmatisch aktuell zu halten).

Answer 2

Ich glaube, Sie brauchen die 11g virtuelle Spalten Funktionalität. Sie erstellen die Funktion als virtuelle Spalte und fügen dann die cosntraint-Funktion hinzu.

Answer 3

Fragen Sie Tom befasst sich mit diesem Thema. Die Antwort lautet: Nein, Sie können keine eindeutigen Beschränkungen auf der Grundlage funktionaler Indizes erstellen.