Oracle数据库中的唯一不区分大小写约束

Question

我在我的表格中有一个varchar列用来存储URL值。我需要让它不区分大小写在记录之间唯一。

我找到了两种方法来实现。

1. 在该字段上创建唯一索引。

   create unique index <index_name> on <tablename>(lower(<column_name>))
   

2. 在该字段上添加唯一约束，如下所示：

   ALTER TABLE person ADD CONSTRAINT person_name_unique
   UNIQUE(LOWER(first_name),LOWER(last_name));
   

如何从上述选择中高效地采纳？

- Awesome

---

这是一个相当全面的解释，没有明确评论效率：https://dev59.com/eGsz5IYBdhLWcg3w_NED#7522004 - davek

唯一约束使用唯一索引作为其基础，它们之间没有区别，唯一的区别在于语法。 - krokodilko

我更喜欢使用约束；两者的区别在于语义。未来的开发者可能会将约束视为一种文档形式：“这必须是不区分大小写的唯一值”。另一方面，唯一索引可能被解释为仅仅出于性能考虑而创建：“这一列恰好始终是不区分大小写的唯一值”。 - Jeffrey Kemp

Answer 1

更加高效的方法是第一种方法。但是，它之所以更高效，仅仅因为后一种语法不起作用。不幸的是，你不能像创建唯一索引那样创建基于函数的约束。

唯一约束不起作用。

SQL> create table person (
  2    first_name varchar2(10),
  3    last_name  varchar2(10)
  4  );

Table created.

SQL> ALTER TABLE person ADD CONSTRAINT person_name_unique
  2  UNIQUE(LOWER(first_name),LOWER(last_name));
UNIQUE(LOWER(first_name),LOWER(last_name))
       *
ERROR at line 2:
ORA-00904: : invalid identifier

但是，基于函数的唯一索引确实可以工作。

SQL> create unique index idx_uniq_name
  2      on person( lower(first_name), lower(last_name) );

Index created.

- Justin Cave

---

谢谢。那么，如果我们创建一个基于函数的唯一索引，那么我们就不需要/不应该在列上添加唯一约束吗？ - user1116119

@user1116119 - 你不能在函数值上创建唯一约束，只能创建一个唯一的基于函数的索引。根据你使用的Oracle版本，你也可以创建一个计算列lower(first_name)并在其上创建唯一约束。 - Justin Cave

有一件值得补充的事情 - 你不能直接在ON CONFLICT ON CONSTRAINT中引用唯一索引，但是你可以引用条件（例如ON CONFLICT（LOWER（first_name），LOWER（last_name）））。今天我被绊倒了，而这是谷歌上第一个问题的结果，所以我想提一下... - Adam Bethke

Answer 2

• 1 可能会出现重复错误。
• 2 不可能实现。（约束中不支持函数）

- RoutesMaps.com