Question 20041 in Mysql Question posted in
The official MySQL documentation can be found here.

MySQL unique index constraint only when another column is null

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

In the following situation, there exists an index that enforces unique data on customer_group.name only when deleted_at is null.

This makes sense because it is pointless to ensure deleted entries are unique. However, I don’t understand how to achieve the same constraint without partial indexes.

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 am using ANSI_QUOTES indeed.

I was suggested to try with a unique index of 2 columns instead of one. But if the constraint was instead UNIQUE INDEX "IDX_d12ecf48-302c-4292-8c8d-d2cc7c8149f9" ON "customer_group" ("name", "deleted_at") then I get the opposite of what I want: when deleted_at is NULL, then name can be duplicated.

Tags:

2

Answers


  1. 0

    Using a computed column, you can do this:

    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 an index:

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

    P.S. I am NOT using ANSI_QUOTES indeed.

    see: DBFIDDLE

    Login or Signup to reply.
  2. 0

    MySQL doesn’t support partial indexes, but it does support expression indexes (starting in MySQL 8.0). Here’s 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));

    Since UNIQUE follows ANSI rules for NULL, if the second column of the unique index is NULL, then there may be any number of duplicates in the first column. The second column being NULL counts as not equal to any other row, therefore it’s always "unique".

    So if the second column is a fixed non-NULL value only when "deleted_at" is NULL, that makes "name" 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 make an index on a TEXT column in MySQL, it’s potentially too long for the 3072 byte limit on an index.

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

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search