MySQL contraint l'index unique uniquement si une autre colonne est vide

Question

J'essaie de réécrire le schéma Postgres pour l'adapter au dialecte MySQL (8.0.32). Comme vous le savez, MySQL ne prend pas en charge les index partiels.

Seulement si deleted_at 为 null 时，才会存在一个索引，该索引会强制执行 customer_group.name est la seule donnée disponible.

Cela est logique puisqu'il ne sert à rien de garantir que les entrées supprimées sont uniques. Cependant, je ne comprends pas comment implémenter les mêmes contraintes sans indexation partielle.

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;

PS J'utilise ANSI_QUOTES.

Quelqu'un m'a suggéré d'essayer d'utiliser un index unique de 2 colonnes au lieu d'une. Cependant, si la contrainte est UNIQUE INDEX "IDX_d12ecf48-302c-4292-8c8d-d2cc7c8149f9" ON "customer_group" ("name", "deleted_at") 那么我会得到与我想要的相反的结果： deleted_at 为 NULL，则 name, elle peut être répétée.

Answer 1

En utilisant les colonnes calculées, vous pouvez effectuer les opérations suivantes :

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`)
  );

Avec indice :

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

P.S. Je n'ai pas utilisé ANSI_QUOTES.

Voir : DBFIDDLE

Answer 2

MySQL ne prend pas en charge les index partiels, mais prend en charge les index d'expression (à partir de MySQL 8.0). Voici une démo :

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));

Puisque UNIQUE suit les règles NULL de l'ANSI, si la deuxième colonne d'un index unique est NULL, il peut y avoir n'importe quel nombre de doublons dans la première colonne. La deuxième colonne lorsque NULL n'est égale à aucune autre ligne, elle est donc toujours "unique".

Donc, si la deuxième colonne est une valeur fixe non NULL uniquement lorsque "deleted_at" est NULL, alors "name" est unique sur toutes les lignes où "deleted_at" est 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 |
+----+-------+---------------------+

J'ai dû changer le type de "nom" car vous ne pouvez pas créer d'index sur une colonne TEXTE dans MySQL et c'est probablement trop long pour la limite de 3072 octets de l'index.

A également modifié PRIMARY KEY pour omettre les noms de contraintes. MySQL nommera toujours simplement la clé primaire PRIMARY.