MySQL constrains unique index only if another column is empty

Question

I'm trying to rewrite the Postgres schema to fit the MySQL (8.0.32) dialect. As you know, MySQL does not support partial indexes.

In the following case, there will only be an index that enforces unique data on customer_group.name if deleted_at is null.

This makes sense since there is no point in ensuring that deleted entries are unique. However, I don't understand how to implement the same constraints without partial indexing.

CREATE TABLE
  "customer_group" (
    "id" integer NOT NULL AUTO_INCREMENT,
    "created_at" datetime NOT NULL DEFAULT NOW(),
    "updated_at" datetime NOT NULL DEFAULT NOW(),
    "deleted_at" datetime,
    "name" text NOT NULL,
    "metadata" text,
    CONSTRAINT "PK_142c3338-da81-4d0c-8cd8-490bb41cd187" PRIMARY KEY ("id")
  );

-- solve this
-- ensure name is unique when one customer_group is not deleted
CREATE UNIQUE INDEX "IDX_d12ecf48-302c-4292-8c8d-d2cc7c8149f9" ON "customer_group" ("name")
WHERE
  "deleted_at" IS NULL;

P.S. I do use ANSI_QUOTES.

Someone suggested that I try using a unique index on 2 columns instead of one. However, if the constraint is UNIQUE INDEX "IDX_d12ecf48-302c-4292-8c8d-d2cc7c8149f9" ON "customer_group" ("name", "deleted_at") then I get the opposite result of what I want: deleted_at is NULL, then name can be repeated.

Answer 1

Using calculated columns, you can do the following:

CREATE TABLE
  `customer_group` (
    `id` integer NOT NULL AUTO_INCREMENT,
    `created_at` datetime NOT NULL DEFAULT NOW(),
    `updated_at` datetime NOT NULL DEFAULT NOW(),
    `deleted_at` datetime,
    `name` text NOT NULL,
    `metadata` text,
    `deleted_row` bit as (CASE WHEN deleted_at is null THEN false ELSE true END),
    CONSTRAINT `PK_142c3338-da81-4d0c-8cd8-490bb41cd187` PRIMARY KEY (`id`)
  );

With index:

CREATE UNIQUE INDEX `IDX_d12ecf48-302c-4292-8c8d-d2cc7c8149f9` ON 
  `customer_group` (`deleted_row`,`name`(100),`id`);

P.S. I did not use ANSI_QUOTES.

See: DBFIDDLE

Answer 2

MySQL does not support partial indexes, but does support expression indexes (starting with MySQL 8.0). Here is a demo:

CREATE TABLE
  "customer_group" (
    "id" integer NOT NULL AUTO_INCREMENT,
    "created_at" datetime NOT NULL DEFAULT NOW(),
    "updated_at" datetime NOT NULL DEFAULT NOW(),
    "deleted_at" datetime,
    "name" VARCHAR(50) NOT NULL,
    "metadata" text,
    PRIMARY KEY ("id")
  );

CREATE UNIQUE INDEX "IDX_d12ecf48-302c-4292-8c8d-d2cc7c8149f9" ON "customer_group"
  ("name", (CASE WHEN "deleted_at" IS NULL THEN true ELSE NULL END));

Because UNIQUE follows ANSI's NULL rules, if the second column of a unique index is NULL, there may be any number of duplicates in the first column. The second column when NULL is not equal to any other row, so it is always "unique".

So if the second column is a fixed non-NULL value only when "deleted_at" is NULL, then "name" is unique on all rows where "deleted_at" is NULL.

INSERT INTO customer_group SET name = 'name1', deleted_at = NULL
Query OK, 1 row affected (0.00 sec)

INSERT INTO customer_group SET name = 'name1', deleted_at = NULL
ERROR 1062 (23000): Duplicate entry 'name1-1' for key 'customer_group.IDX_d12ecf48-302c-4292-8c8d-d2cc7c8149f9'

INSERT INTO customer_group SET name = 'name2', deleted_at = '2018-01-01'
Query OK, 1 row affected (0.00 sec)

INSERT INTO customer_group SET name = 'name2', deleted_at = '2018-01-01'
Query OK, 1 row affected (0.00 sec)

SELECT id, name, deleted_at FROM customer_group;
+----+-------+---------------------+
| id | name  | deleted_at          |
+----+-------+---------------------+
|  1 | name1 | NULL                |
|  3 | name2 | 2018-01-01 00:00:00 |
|  4 | name2 | 2018-01-01 00:00:00 |
+----+-------+---------------------+

I had to change the type of "name" because you can't create an index on a TEXT column in MySQL and it might be too long for the 3072 byte limit of the index.

Also changed the PRIMARY KEY to omit the constraint name. MySQL will always name the primary key simply PRIMARY.