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

问题描述

我创建了下表:

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?

推荐答案

这是个误会.
UNIQUE 约束正是你想要的.多个 NULL 值可以在定义 UNIQUE 的列中共存.

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

手册:

一般来说，当有超过表中的一行，其中包含所有列的值在约束中是相等的.但是，两个空值不是在这个比较中被认为是平等的.这意味着即使在场的唯一约束可以存储重复的行至少在受约束的列之一中包含空值.这行为符合 SQL 标准，但我们听说其他SQL 数据库可能不遵循此规则.所以要小心的时候开发可移植的应用程序.

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.

粗体强调我的.

请注意字符类型允许空字符串('')，它不是一个 NULL 值，并且在输入多于一行时会触发唯一违规，就像任何其他非空值一样.

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屋！