SQL 在唯一列中允许空值

Allow null in unique column

提问by liv a

I've created the following table:

我创建了下表：

CREATE TABLE MMCompany (
   CompanyUniqueID BIGSERIAL PRIMARY KEY NOT NULL, 
   Name VARCHAR (150) NOT NULL,
   PhoneNumber VARCHAR(20) NOT NULL UNIQUE, 
   Email VARCHAR(75) UNIQUE,
   CompanyLogo BYTEA
 );

The email column is unique and it causes a "bug" in my scenario since there could only be one record with null. I'm trying to achieve records of companies without the same email but at the same time allow a companies to have no email.

email 列是唯一的，它会在我的场景中导致“错误”，因为只有一个记录为空。我试图在没有相同电子邮件的情况下实现公司记录，但同时允许公司没有电子邮件。

How can I achieve that?

我怎样才能做到这一点？

回答by Erwin Brandstetter

This is a misunderstanding.
The UNIQUEconstraint does exactlywhat you want. Multiple NULLvalues can coexist in a column defined UNIQUE.

这是一种误解。
该UNIQUE约束不正是你想要的。多个NULL值可以在定义的列中共存UNIQUE。

Quoting the manual about UNIQUE constraints:

引用有关 UNIQUE 约束的手册：

In general, a unique constraint is violated when there is more than one row in the table where the values of all of the columns included in the constraint are equal. However, two null values are not considered equal in this comparison. That means even in the presence of a unique constraint it is possible to store duplicate rows that contain a null valuein at least one of the constrained columns. This behavior conforms to the SQL standard, but we have heard that other SQL databases might not follow this rule. So be careful when developing applications that are intended to be portable.

通常，当表中有多于一行且约束中包含的所有列的值相等时，就会违反唯一约束。但是，在此比较中，两个空值不被视为相等。这意味着即使存在唯一约束，也可以在至少一个受约束的列中存储包含空值的重复行。这种行为符合 SQL 标准，但我们听说其他 SQL 数据库可能不遵循此规则。所以在开发可移植的应用程序时要小心。

Bold emphasis mine.

大胆强调我的。

Be aware that character typesallow an empty string (''), which is nota NULLvalue and would trigger a unique violation just like any other non-null value when entered in more than one row.

请注意，字符类型允许空字符串（''），这是不是一个NULL值，在多行输入的时候会触发一个独特违反就像任何其他非空值。

回答by Basil Bourque

No Such Problem In Postgres

Postgres 中没有这样的问题

In Erwin Brandstetter's correct answer, he explains that you should indeed be seeing the behavior you want (multiple NULLs allowed in a Unique constraint). You should see this behavior in Postgres in particular as well as any SQL standard compliant database in general.

在 Erwin Brandstetter 的正确答案中，他解释说您确实应该看到您想要的行为（在唯一约束中允许多个 NULL）。您应该在 Postgres 以及任何一般的符合 SQL 标准的数据库中看到这种行为。

Workaround for Other Databases

其他数据库的解决方法

However, the Postgres doc cautions about portability because some databases are known to be in violation of this feature. For such a non-compliant system I suggest replacing the use of a NULL value in such fields with a bogus value. The bogus value would be a string such as "unknown_" plus some arbitrary value that is virtually certain to be unique. That arbitrary value could be something like the current date-time plus a random number.

但是，Postgres 文档对可移植性提出了警告，因为已知某些数据库违反了此功能。对于这样一个不合规的系统，我建议在这样的字段中用一个假值替换一个 NULL 值。虚假值将是一个字符串，例如“unknown_”加上一些几乎可以肯定是唯一的任意值。该任意值可能类似于当前日期时间加上随机数。

UUID

用户名

But, rather than roll your own arbitrary value, generate a UUID. The original Version 1 UUID is indeed a combination of the current date-time, a random number, and the computer's virtually unique MAC address.

但是，与其滚动您自己的任意值，不如生成一个UUID。原始版本 1 UUID 确实是当前日期时间、随机数和计算机虚拟唯一MAC 地址的组合。

A UUID presented as a hex string with canonical formatting using hyphens looks like this:

使用连字符以规范格式显示为十六进制字符串的 UUID 如下所示：

93e6f268-5c2d-4c63-9d9c-40e6ac034f88

93e6f268-5c2d-4c63-9d9c-40e6ac034f88

So my suggestion is to combine an arbitrary string such as "unknown_" plus a UUID, to look like this:

所以我的建议是组合一个任意字符串，比如“unknown_”加上一个 UUID，看起来像这样：

unknown_93e6f268-5c2d-4c63-9d9c-40e6ac034f88

未知_93e6f268-5c2d-4c63-9d9c-40e6ac034f88

So my suggestion for non-compliant databases is to generate such a value and use it in place of NULL, use it where you do not yet have a known value in that column for a particular row. Instead of writing queries that look for rows that have (or do not have) a NULL value in that column, write queries that look for rows that have (or do not have) a value beginning with the arbitrary string, "unknown_" in this example. Each row would then satisfy the constraint of having a unique value.

因此，我对不合规数据库的建议是生成这样的值并使用它代替 NULL，在特定行的该列中还没有已知值的情况下使用它。与其编写查询来查找该列中具有（或不具有）NULL 值的行，不如编写查询来查找具有（或不具有）以任意字符串开头的值的行，在此例子。然后每一行将满足具有唯一值的约束。

Indeed, I would assign this "unknown_" + UUID value as the default for that column.

实际上，我会将这个“unknown_”+ UUID 值指定为该列的默认值。

You could also add a NOT NULL constraint to this column.

您还可以向此列添加 NOT NULL 约束。

Generating UUID Values

生成 UUID 值

Postgres has built-in support for the data type of UUID, but that's irrelevant in this answer here. What you need is to generate a UUID value.

Postgres 内置了对 UUID 数据类型的支持，但这与这里的答案无关。您需要的是生成一个 UUID值。

For generating UUIDs you need an extension (plugin) that adds this capability to Postgres. Most Postgres installers include such an extension. This extension is called uuid-ossp. Usually the extension is not activated by default. To do so in recent versions of Postgres, use the CREATE EXTENSIONcommand. For instructions, see my blog post on installing in Postgres 9.1 and lateror my other post on Postgres 9.0 and earlier. Both the new and old way of installation is easy provided the extension/plugin was compiled and bundled with your Postgres installation.

要生成 UUID，您需要一个扩展（插件）来将此功能添加到 Postgres。大多数 Postgres 安装程序都包含这样的扩展。这个扩展名为uuid-ossp。通常默认情况下不激活扩展。要在最新版本的 Postgres 中执行此操作，请使用CREATE EXTENSION命令。有关说明，请参阅我关于在 Postgres 9.1 及更高版本中安装的博客文章或我关于 Postgres 9.0 及更早版本的其他文章。如果扩展/插件已编译并与您的 Postgres 安装捆绑在一起，则新旧安装方式都很容易。

Summary

概括

Let me be clear that for Postgres alone, there is no need for this workaroundbecause Postgres complies with the SQL standard. But if:

让我明确一点，仅对于 Postgres，不需要这种变通方法，因为 Postgres 符合 SQL 标准。但如果：

• You are concerned about portability of your code to some other non-compliant database system, or
• You need to exchange data with a non-compliant database system, or
• You agree with Dr. Chris Datethat NULL is the work of the devil and should be avoided

• 您担心您的代码可移植到其他一些不合规的数据库系统，或者
• 您需要与不合规的数据库系统交换数据，或者
• 您同意Chris Date 博士的观点，即 NULL 是魔鬼的杰作，应该避免

…then a workaround such as this is necessary.

……那么像这样的解决方法是必要的。

回答by dumbledad

Some databases do not allow multiple null values, for example the SQL Server documentationstates that "multiple null values are considered duplicates". On databases that do not allow nullable UNIQUE constraints you could try this (from GuidoG's answerto another question):

某些数据库不允许多个空值，例如SQL Server 文档声明“多个空值被视为重复”。在不允许为空的 UNIQUE 约束的数据库上，您可以尝试这个（来自GuidoG对另一个问题的回答）：

CREATE UNIQUE NONCLUSTERED INDEX IDX_Email
ON MMCompany (Email)
WHERE Email IS NOT NULL;

回答by nvogel

Drop the email column from the table. Put it in a new table where it can be NOT NULL and UNIQUE:

从表中删除电子邮件列。将它放在一个新表中，它可以是 NOT NULL 和 UNIQUE：

CREATE TABLE CompanyEmail
 (
    CompanyUniqueID INT NOT NULL PRIMARY KEY
       REFERENCES MMCompany (CompanyUniqueID),
    Email VARCHAR(75) NOT NULL UNIQUE
 );

Avoid nullable UNIQUE constraints.

避免可为空的 UNIQUE 约束。

回答by Denis de Bernardy

Unique and null don't get along much, since null is undefined by definition — you can't know if two nulls are the same unknown.

Unique 和 null 不太相处，因为根据定义 null 是未定义的——你无法知道两个 null 是否是相同的未知数。

In this sense, your current unique constraint on email is the right thing to do and should work as is.

从这个意义上说，您当前对电子邮件的独特约束是正确的做法，应该按原样工作。

---

---

In case you ever need to make it otherwise, though, a partial index works:

但是，如果您需要以其他方式进行设置，则部分索引可以工作：

create unique index on MMCompany((email is null)) where (email is null);

Another approach is to define a constraint trigger. Something like:

另一种方法是定义一个约束触发器。就像是：

create function email_chk() returns trigger as $$
begin
  if exists (
    select 1 from mmcompany where email is null and companyuniqueid <> new.id
  ) then
    raise 'dup null found';
  end if;
  return null;
end;
$$ language plpgsql;

create constraint trigger after insert or update on mmcompany
for each row when (new.email is null)
execute procedure email_chk();

回答by Ron16

And just in case you're generating your DB Tables using EF Code First, edit your Migration Class' Up method like the following to enforce your UNIQUE KEY constraint to ignore NULL.

以防万一您使用 EF Code First 生成数据库表，请编辑您的迁移类的 Up 方法，如下所示，以强制您的 UNIQUE KEY 约束忽略 NULL。

migrationBuilder.Sql(@"CREATE UNIQUE NONCLUSTERED INDEX[IX_Employees_TaskId] ON[dbo].[Employees]([TaskId] ASC)
                                WHERE [TaskId] IS NOT NULL"
                                );

And then you could test your Unique Constraint by logging into your DB through SQL Server Management Studio or something similar. Like in this case Employee Table happily accepts 2 NULL values in TaskId although its an UNIQUE column.

然后你可以通过 SQL Server Management Studio 或类似的东西登录你的数据库来测试你的唯一约束。就像在这种情况下，员工表愉快地接受 TaskId 中的 2 个 NULL 值，尽管它是一个 UNIQUE 列。