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. Dans le cas suivant, il y aura un index qui appliquera des données uniques sur customer_group.name uniquement si delete_at est nul. 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. CREATETABLE"personnalisé

P粉463291248 · Answer

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

P粉311563823 · Answer

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.