Allow Null in Unique Column

Create unique constraint with null columns

Postgres 15 or newer

Postgres 15 (currently beta) adds the clause NULLS NOT DISTINCT. The release notes:

• Allow unique constraints and indexes to treat NULL values as not distinct (Peter Eisentraut)

  Previously NULL values were always indexed as distinct values, but
  this can now be changed by creating constraints and indexes using
  UNIQUE NULLS NOT DISTINCT.

With this clause NULL is treated like just another value, and a UNIQUE constraint does not allow more than one row with the same NULL value. The task is simple now:

ALTER TABLE favorites
ADD CONSTRAINT favo_uni UNIQUE NULLS NOT DISTINCT (user_id, menu_id, recipe_id);

There are examples in the manual chapter "Unique Constraints".

The clause switches behavior for all index keys. You can't treat NULL as equal for one key, but not for another.

NULLS DISTINCT remains the default (in line with standard SQL) and does not have to be spelled out.

The same clause works for a UNIQUE index, too:

CREATE UNIQUE INDEX favo_uni_idx
ON favorites (user_id, menu_id, recipe_id) NULLS NOT DISTINCT;

Note the position of the new clause after the key fields.

Postgres 14 or older

Create two partial indexes:

CREATE UNIQUE INDEX favo_3col_uni_idx ON favorites (user_id, menu_id, recipe_id)
WHERE menu_id IS NOT NULL;

CREATE UNIQUE INDEX favo_2col_uni_idx ON favorites (user_id, recipe_id)
WHERE menu_id IS NULL;

This way, there can only be one combination of (user_id, recipe_id) where menu_id IS NULL, effectively implementing the desired constraint.

Possible drawbacks:

• You cannot have a foreign key referencing (user_id, menu_id, recipe_id). (It seems unlikely you'd want a FK reference three columns wide - use the PK column instead!)
• You cannot base CLUSTER on a partial index.
• Queries without a matching WHERE condition cannot use the partial index.

If you need a complete index, you can alternatively drop the WHERE condition from favo_3col_uni_idx and your requirements are still enforced.

The index, now comprising the whole table, overlaps with the other one and gets bigger. Depending on typical queries and the percentage of NULL values, this may or may not be useful. In extreme situations it may even help to maintain all three indexes (the two partial ones and a total on top).

This is a good solution for a single nullable column, maybe for two. But it gets out of hands quickly for more as you need a separate partial index for every combination of nullable columns, so the number grows binomially. For multiple nullable columns, see instead:

• Why doesn't my UNIQUE constraint trigger?

Aside: I advise not to use mixed case identifiers in PostgreSQL.

MSSQL: Add Unique Constraint at Create Table and allow NULLs

Assuming that you want multiple rows with the value NULL, you won't be able to use a UNIQUE CONSTRAINT, as NULL is still a value (even if an unknown one). For example:

CREATE TABLE dbo.YourTable (UserIdentifier nvarchar(100) NULL,
                            CONSTRAINT UC_UI UNIQUE (UserIdentifier));
GO
INSERT INTO dbo.YourTable (UserIdentifier)
VALUES(NULL);
GO
INSERT INTO dbo.YourTable (UserIdentifier)
VALUES(NULL);
GO
DROP TABLE dbo.YourTable;

Notice the second INSERT fails.

You can, instead, however, use a conditional UNIQUE INDEX:

CREATE TABLE dbo.YourTable (UserIdentifier nvarchar(100) NULL);

CREATE UNIQUE NONCLUSTERED INDEX UI_UI ON dbo.YourTable(UserIdentifier) WHERE UserIdentifier IS NOT NULL;

GO
INSERT INTO dbo.YourTable (UserIdentifier)
VALUES(NULL); -- Success
GO
INSERT INTO dbo.YourTable (UserIdentifier)
VALUES(NULL); --Success
GO
INSERT INTO dbo.YourTable (UserIdentifier)
VALUES(N'Steve'); --Success
GO
INSERT INTO dbo.YourTable (UserIdentifier)
VALUES(N'Jayne'); --Success
GO
INSERT INTO dbo.YourTable (UserIdentifier)
VALUES(N'Steve'); --Fails
GO
DROP TABLE dbo.YourTable;

As Jeroen Mostert stated in the comments though, you cannot create a unique index as part of creating the table; it must be created in a separate statement. There is no syntax to create an UNIQUE INDEX as part of a CREATE TABLE statement.

You can create this inline (it was undocumented at the time of this answer was originally written) with either of the following syntax in SQL Server 2016+:

CREATE TABLE dbo.YourTable (UserIdentifier nvarchar(100) NULL INDEX UI_UI UNIQUE WHERE UserIdentifier IS NOT NULL);

CREATE TABLE dbo.SomeTable (UserIdentifier nvarchar(100) NULL,
                            INDEX UI_UI UNIQUE (UserIdentifier)  WHERE UserIdentifier IS NOT NULL);

db<>fiddle 2014, db<>fiddle 2016

How do I create a unique constraint that also allows nulls?

SQL Server 2008 +

You can create a unique index that accept multiple NULLs with a WHERE clause. See the answer below.

Prior to SQL Server 2008

You cannot create a UNIQUE constraint and allow NULLs. You need set a default value of NEWID().

Update the existing values to NEWID() where NULL before creating the UNIQUE constraint.

Does MySQL ignore null values on unique constraints?

Yes, MySQL allows multiple NULLs in a column with a unique constraint.

CREATE TABLE table1 (x INT NULL UNIQUE);
INSERT table1 VALUES (1);
INSERT table1 VALUES (1);   -- Duplicate entry '1' for key 'x'
INSERT table1 VALUES (NULL);
INSERT table1 VALUES (NULL);
SELECT * FROM table1;

Result:

x
NULL
NULL
1

This is not true for all databases. SQL Server 2005 and older, for example, only allows a single NULL value in a column that has a unique constraint.

can a unique constraint column have 2 or more null values? (oracle)

Easy to check: (The answer is YES)

create table t1 (col1 number unique);

Table T1 created.

insert into t1 values (1);

1 row inserted.

insert into t1 values (null);

1 row inserted.

insert into t1 values (null);

1 row inserted.

select rownum, col1 from t1;

    ROWNUM       COL1
---------- ----------
         1          1
         2           
         3           

3 rows selected.

Edit: While what I show above is the answer when only one column is involved in a unique constraint, one can also have composite unique keys (constraints defined at the table level, rather than column level - involving two or more columns). In that case, if say the unique key is on (col1, col2, col3), then (1, 1, 0) and (1, 1, 3) are not duplicates, because they aren't identical in every position. In this case, (1, 1, null) is allowed, but only once. The correct "generalization" of null "value" in a column, however, is for ALL values in ALL THREE columns to be null. In that regard, rows with "null values" in the unique key columns are still allowed any number of times.

That is: While (1, 1, null) is allowed, but not more than once, a row with values (null, null, null) in the three columns that make up the unique key are allowed any number of times - just like in the single-column case.

Does EF Core allow a unique column to contain multiple nulls?

Yes, you can do that with EF Core, as a Unique index by default is created as a filtered index (WHERE ... IS NOT NULL)

config.Entity<Product>()
        .HasIndex(b => b.ProductId)
        .IsUnique();

https://github.com/aspnet/EntityFramework/pull/2868

Unique Constraint Allow Null for One column in Oracle

I expect that you could do this by placing a unique constraint on:

(
  Nvl2(RollNo, Year    , null),
  Nvl2(RollNo, ExamCode, null),
  RollNo
)

Not tested

How to allow NULL value for one column in unique index on multiple columns

Is there any way to make the unique constraint only apply when the quote is not null?

Actually, this is the only way. You can have multiple "identical" entries with one or more of the columns in a multicolumn index being NULL, because Postgres does not consider two NULL values identical for this purpose (like in most contexts).

The sequence of columns in the index doesn't matter for this. (It matters for other purposes, though.)

Make sure the underlying columns in the table itself can be NULL. (Sometimes confused with the empty string ''.)

After re-reading your question, the more appropriate scenario seems to be this related answer:

Create a multicolumn index to enforce uniqueness

This one may be of help, too, but the question asks for the opposite of yours:

How to add a conditional unique index on PostgreSQL

If you actually want a partial index with only non-null value, you can do that too:

CREATE INDEX price_uni_idx ON price (quote_id, line_item_id)
WHERE  quote_id     IS NOT NULL
AND    line_item_id IS NOT NULL;

You do not need that for your purpose, though. It may still be useful to exclude rows with NULL values from the index to make it faster. Details here:

Indexed ORDER BY with LIMIT 1

---

Related Topics