How to Create a Unique Index on a Null Column

How to create a unique index on a NULL column?

Pretty sure you can't do that, as it violates the purpose of uniques.

However, this person seems to have a decent work around:
http://sqlservercodebook.blogspot.com/2008/04/multiple-null-values-in-unique-index-in.html

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.

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.

How to make unique index constraints on multiple NULLABLE columns?

Why? Because NULL = NULL is not true.

Uniqueness means it doesn't allow another row to have the same value in a column. But in SQL, NULL compared to any other value — including another NULL — is "unknown." That's how three-valued boolean logic is defined.

https://dev.mysql.com/doc/refman/8.0/en/create-index.html#create-index-unique says:

A UNIQUE index permits multiple NULL values for columns that can contain NULL.

What do you expect to happen if you do your INSERT twice? Should it result in a duplicate key conflict even though the "duplicate" is a NULL?

You'll have to create an functional index (available on MySQL 8.0.13 or later):

mysql> CREATE TABLE `routes` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `firstname` varchar(255) NOT NULL,
  `lastname` varchar(255) NOT NULL,
  `departure` date DEFAULT NULL,
  `returndate` date DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq` (`firstname`,`lastname`,(coalesce(`departure`, '1900-01-01')))
);

mysql> INSERT INTO `routes` (`id`, `firstname`, `lastname`, `departure`, `returndate`) 
    -> VALUES (NULL, 'john', 'doe', NULL, NULL);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO `routes` (`id`, `firstname`, `lastname`, `departure`, `returndate`) 
    ->  VALUES (NULL, 'john', 'doe', NULL, NULL);
ERROR 1062 (23000): Duplicate entry 'john-doe-1900-01-01' for key 'uniq'

Re your comment: No, coalesce() in this functional index doesn't affect the data value stored in the column, it only affects what's indexed.

mysql> select * from routes;
+----+-----------+----------+-----------+------------+
| id | firstname | lastname | departure | returndate |
+----+-----------+----------+-----------+------------+
|  1 | john      | doe      | NULL      | NULL       |
+----+-----------+----------+-----------+------------+

---

If you use MySQL 5.7+ you can do something similar, but you have to create a virtual column to use for the index:

CREATE TABLE `routes` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `firstname` varchar(255) NOT NULL,
  `lastname` varchar(255) NOT NULL,
  `departure` date DEFAULT NULL,
  `departure_notnull` date GENERATED ALWAYS AS (coalesce(`departure`, '1900-01-01')) VIRTUAL,
  `returndate` date DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq` (`firstname`,`lastname`,`departure_notnull`)
)

---

If you use MySQL 5.6 or earlier, you can't use virtual columns or functional indexes. You'll have to make the column NOT NULL, and use a special value to signify that it's a non-date.

Preferred way to create composite unique index with NULL values

As requested, my suggestion (untested):

ALTER TABLE main_itemmaster ADD COLUMN uniquer BIGINT NOT NULL;
DELIMITER $$
CREATE TRIGGER add_uniquer BEFORE INSERT ON main_itemmaster
FOR EACH ROW
  BEGIN
    SET NEW.uniquer = (SELECT UUID_SHORT());
  END $$
DELIMITER ;
ALTER TABLE main_itemmaster ADD UNIQUE KEY (tv_series_id, tv_season_number, tv_episode_number, name, uniquer);

The algorithm behind the uniquer is entirely up to you. I chose a short UUID here. You might want a long UUID. Or UUID might not be right for you. Time-based might work. You could base it on the MD5 of the values in the inserted row.

Nulls in SQL Server unique index?

Yeah, that is an issue where SQL Server is not following standard SQL practices.
There is a solution, though. Use separate indices. You can FILTER indices.

• A UNIQE index where the field is NOT NULL
• A non unique index where the field IS NULL

Done.

How to create unique index on fields with possible null values (Oracle 11g)?

You want to only enforce uniqueness on the rows where both UNIQUE_VALUE and UNIQUE_GROUP_ID are not null. To do this, you can use a unique function-based index:

CREATE UNIQUE INDEX func_based_index ON the_table
  (CASE WHEN unique_value IS NOT NULL
         AND unique_group_id IS NOT NULL
        THEN UNIQUE_VALUE || ',' || UNIQUE_GROUP_ID
   END);

---

Related Topics