忽略空格的 Oracle 唯一约束和唯一索引

Question

我使用了Oracle 11g，对于某些表，使用以下命令在特定列上创建一个忽略所有空格的唯一索引。

CREATE UNIQUE INDEX UK_LOCATION_NAME ON LOCATION(UPPER(REGEXP_REPLACE("FARSI_NAME",'s+','')));

最近，Oracle 数据库更新到 12c，执行上述命令会引发错误：

[2019-06-08 19：44：08] [42000][1743] ORA-01743：只能索引纯函数

如何定义忽略空格(空格、制表符等(的唯一索引？

Answer 1

似乎允许您在 Oracle 11g 中基于

函数的索引中使用 REGEXP_REPLACE 的原因是它仅在 Oracle 11g 中是一个错误，也可能在 12.1 中也是如此。自 Oracle 12.2 以来，它已得到修复，因此它不允许您创建直接使用 REGEXP_REPLACE 的索引。原因是它是一个非确定性函数。

约束也存在类似的问题CHECK并且已经在这篇文章中详细讨论了。

在您的情况下，如果您只是替换空格，则使用 REPLACE 的更简单方法应该就足够了。

CREATE UNIQUE INDEX UK_LOCATION_NAME 
     ON LOCATION(UPPER(replace("FARSI_NAME",' ')));

当您的替换模式很复杂时，解决此问题的另一个选项是使用DETERMINISTIC的替代函数。这是一种解决方法，对于复杂方案可能效率不高。

create or replace function my_regex_rep(txt_in VARCHAR2) 
return VARCHAR2 DETERMINISTIC IS
 BEGIN
   return regexp_replace(txt_in,'s+','');
 END;
 /

现在，您可以在 INDEX .

CREATE UNIQUE INDEX UK_LOCATION_NAME ON 
     LOCATION(UPPER(my_regex_rep("FARSI_NAME")));

测试

INSERT INTO LOCATION(FARSI_NAME) values('ABCD EFGH');
1 row inserted.
INSERT INTO LOCATION(FARSI_NAME) values('   ABCD      efgh  ');
                                              --spaces
ORA-00001: unique constraint (HR.UK_LOCATION_NAME) violated
INSERT INTO LOCATION(FARSI_NAME) values('ABCD   EFGh');
                                           --tab
ORA-00001: unique constraint (HR.UK_LOCATION_NAME) violated

甲骨文 18c 演示