代码之家 › 专栏 › 技术社区 › Brian Ramsay

如何在Oracle中创建唯一索引但忽略空值?

ddl oracle sql

Brian Ramsay · 技术社区 · 17 年前

我试图在一个表中的两个字段上创建一个唯一的约束。然而,很有可能一个将是空的。如果它们都不是空的,我只要求它们是唯一的( name 永远不会是空的)。

create unique index "name_and_email" on user(name, email);

忽略表名和字段名的语义,以及是否有意义——我只是编了一些。

是否有一种方法可以在这些字段上创建唯一约束,以强制两个非空值的唯一性,但如果有多个条目 名称 不是空的 email 是空的吗?

这个问题是针对SQL Server的,我希望答案不一样: How do I create a unique constraint that also allows nulls?

2 回复 | 直到 7 年前

APC 17 年前

我们可以使用基于函数的索引来实现这一点。下面利用 NVL2() 如您所知,如果表达式不为空,则返回一个值;如果为空,则返回另一个值。你可以使用 CASE() 相反。

SQL> create table blah (name varchar2(10), email varchar2(20))
  2  /

Table created.

SQL> create unique index blah_uidx on blah
  2      (nvl2(email, name, null), nvl2(name, email, null))
  3  /

Index created.

SQL> insert into blah values ('APC', null)
  2  /

1 row created.

SQL> insert into blah values ('APC', null)
  2  /

1 row created.

SQL> insert into blah values (null, 'apc@example.com')
  2  /

1 row created.

SQL> insert into blah values (null, 'apc@example.com')
  2  /

1 row created.

SQL> insert into blah values ('APC', 'apc@example.com')
  2  /

1 row created.

SQL> insert into blah values ('APC', 'apc@example.com')
  2  /
insert into blah values ('APC', 'apc@example.com')
*
ERROR at line 1:
ORA-00001: unique constraint (APC.BLAH_UIDX) violated


SQL>

编辑

因为在您的场景中,名称总是被填充的,所以您只需要这样的索引:

SQL> create unique index blah_uidx on blah
  2      (nvl2(email, name, null), email)
  3  /

Index created.

SQL>

broll 7 年前

我不知道还有多少人会被引导到这个答案,但至少在最新版本的Oracle中,在一个唯一索引上允许有多个空行,而接受的答案是不必要的。

推荐文章

Johnny T · 基于当前值的SQL合并表[重复]

1 年前

Shukurullox Komiljonov · 从记录中获得相互和解。使用SQL

1 年前

John D · 需要为NULL或NOT NULL的WHERE子句

1 年前

ojek · 如何对SQL结果进行分组和编号?

1 年前

senek · 如何在PL/SQL中将选择结果(列)放入数组中

1 年前

Sax · 规范化Google表格(第一步)

1 年前

BarÄ±Å UÅaklÄ± · 如何在ON冲突更新中使用CTE中的值

1 年前

Jatin · 检索卷计数的动态sql抛出错误语法错误[关闭]

1 年前

Andrus · 如何在sql中查找第二个匹配项

1 年前

Nebula Tech · 在SQL Server中搜索字符串中单词的多次出现

1 年前