Allow null in unique column

sql postgresql database-design null unique-constraint
liv a picture liv a · Nov 22, 2013 · Viewed 59.3k times · Source

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?

Answer

Erwin Brandstetter picture Erwin Brandstetter · Nov 22, 2013

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

The manual:

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.

Related questions

Create unique constraint with null columns

I have a table with this layout: CREATE TABLE Favorites ( FavoriteId uuid NOT NULL PRIMARY KEY, UserId uuid NOT NULL, RecipeId uuid NOT NULL, MenuId uuid ) I want to create a unique constraint similar to this: ALTER TABLE Favorites ADD …

Read More
How big is too big for a PostgreSQL table?

I'm working on the design for a RoR project for my company, and our development team has already run into a bit of a debate about the design, specifically the database. We have a model called Message that needs to …

Read More
How to implement a many-to-many relationship in PostgreSQL?

I believe the title is self-explanatory. How do you create the table structure in PostgreSQL to make a many-to-many relationship. My example: Product(name, price); Bill(name, date, Products);

Read More