在唯一列中允许为空 [英] Allow null in unique column

问题描述

我创建了下表：

  CREATE TABLE MMCompany（
 CompanyUniqueID BIGSERIAL PRIMARY KEY NOT NULL， 
名称VARCHAR（150）NOT NULL，
 PhoneNumber VARCHAR（20）NOT NULL UNIQUE，
电子邮件VARCHAR（75）UNIQUE，
 CompanyLogo BYTEA 
）; 
  

电子邮件列是独一无二的，在我的情景中会导致错误，因为只能有一个记录为null。我试图在没有相同电子邮件的情况下实现公司记录，但同时允许公司没有电子邮件。

如何实现？

解决方案

这是一个误会。

UNIQUE 约束确切地你想要什么多个 NULL 值可以在定义为 UNIQUE 的列中共存。

引用有关UNIQUE约束的手册：

一般来说，当表中的行多于
一行时，唯一的约束被违反，列中包含
的约束是相等的。但是，在此比较中，两个空值不是
。这意味着即使在唯一约束的
的存在下，也可以在至少一个约束列中存储
包含空值的重复行。这个
行为符合SQL标准，但是我们听说其他
SQL数据库可能不遵循此规则。因此，当
开发旨在便携式的应用程序时，请小心。

粗体强调我的。
$ b

请注意，字符类型允许空字符串（''），这是不 a NULL 值，并会触发唯一的违规就像在多行中输入的任何其他非空值一样。

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.

How can I achieve that?

解决方案

This is a misunderstanding.
The UNIQUE constraint does exactly what you want. Multiple NULL values can coexist in a column defined UNIQUE.

Quoting the manual about UNIQUE constraints:

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 value in 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.

Bold emphasis mine.

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

这篇关于在唯一列中允许为空的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！