Allow null in unique column
database-design, null, postgresql, sql, unique-constraint
Solution
This is a misunderstanding. The `UNIQUE` constraint does exactly what you want. `NULL` values can coexist in multiple rows 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 can be changed by adding the clause `NULLS NOT DISTINCT` [...]
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.
Postgres 15 allows the clause `NULLS NOT DISTINCT` to alter this behavior (the opposite of what you requested). I updated the above quote. See:
- Create unique constraint with null columns
Problem
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?