我可以在 Oracle 中有一个可延迟的唯一功能索引吗？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/837306/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Can I have a deferrable unique functional index in Oracle?
提问by Chris
I'd like to create a deferrable unique functional index in Oracle 10g.
我想在 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?
但是我收到“ORA-00904：无效标识符”错误。要么我语法错误，要么 Oracle 不支持可延迟函数索引？有人可以为我提供解决方案或明确的答案吗？
回答by Jeffrey Kemp
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:
不错的尝试，但根据 Oracle 10g 文档， CREATE INDEX 和 ALTER TABLE ADD CONSTRAINT 的语法在这方面不可互换，这就是您遇到语法错误的原因：
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".
因此 CREATE INDEX 允许 column_expression，它基本上是一个“基于函数的索引”。
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.
因此，UNIQUE 约束定义只能是列名，而不能是列表达式。
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).
您可以在 11g 中使用虚拟列来执行此操作，在 10g 和更早版本中，大多数人倾向于创建派生列（以及以编程方式保持它们最新的负担）。
回答by Gary Myers
I think you need the 11g virtual columnsfunctionality. You'd create the function as a virtual column, then add the cosntraint on that.
我认为您需要 11g虚拟列功能。您可以将该函数创建为虚拟列，然后在其上添加 cosntraint。
