SQL 如何在 Oracle 中创建唯一索引但忽略空值？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1374737/
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
How can I create a unique index in Oracle but ignore nulls?
提问by Brian Ramsay
I am trying to create a unique constraint on two fields in a table. However, there is a high likelihood that one will be null. I only require that they be unique if both are not null (namewill never be null).
我正在尝试对表中的两个字段创建唯一约束。然而，一个人很可能为空。如果两者都不为空，我只要求它们是唯一的（name永远不会为空）。
create unique index "name_and_email" on user(name, email);
Ignore the semantics of the table and field names and whether that makes sense - I just made some up.
忽略表和字段名称的语义以及这是否有意义 - 我只是编造了一些。
Is there a way to create a unique constraint on these fields that will enforce uniqueness for two not null values, but ignore if there are multiple entries where nameis not null and emailis null?
有没有办法在这些字段上创建一个唯一约束，该约束将强制两个非空值的唯一性，但忽略是否有多个name非空且email为空的条目？
This question is for SQL Server, and I'm hoping that the answer is not the same: How do I create a unique constraint that also allows nulls?
这个问题是针对 SQL Server 的，我希望答案不一样： 如何创建一个也允许空值的唯一约束？
回答by APC
We can do this with a function-based index. The following makes use of NVL2()which, as you know, returns one value if the expression is not null and a different value if it is null. You could use CASE()instead.
我们可以使用基于函数的索引来做到这一点。NVL2()如您所知，以下使用which，如果表达式不为 null，则返回一个值，如果表达式为 null，则返回一个不同的值。你可以用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, '[email protected]')
2 /
1 row created.
SQL> insert into blah values (null, '[email protected]')
2 /
1 row created.
SQL> insert into blah values ('APC', '[email protected]')
2 /
1 row created.
SQL> insert into blah values ('APC', '[email protected]')
2 /
insert into blah values ('APC', '[email protected]')
*
ERROR at line 1:
ORA-00001: unique constraint (APC.BLAH_UIDX) violated
SQL>
Edit
编辑
Because in your scenario name will always be populated you will only need an index like this:
因为在您的方案名称中将始终填充您只需要这样的索引：
SQL> create unique index blah_uidx on blah
2 (nvl2(email, name, null), email)
3 /
Index created.
SQL>
回答by broll
I don't know how many people still get directed to this answer, but at least in the latest version of oracle, you're allowed to have multiple rows with null on a unique index and the accepted answer isn't necessary
我不知道有多少人仍然被定向到这个答案，但至少在最新版本的 oracle 中，您可以在唯一索引上有多行 null，并且不需要接受的答案
