MySQL mengekang indeks unik hanya jika lajur lain kosong

Question

Saya cuba menulis semula skema Postgres agar sesuai dengan dialek MySQL (8.0.32). Seperti yang anda ketahui, MySQL tidak menyokong indeks separa.

Hanya jika deleted_at 为 null 时，才会存在一个索引，该索引会强制执行 customer_group.name adalah satu-satunya data.

Ini masuk akal kerana tidak ada gunanya memastikan entri yang dipadamkan adalah unik. Walau bagaimanapun, saya tidak faham cara melaksanakan kekangan yang sama tanpa pengindeksan separa.

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 saya memang guna ANSI_QUOTES.

Seseorang mencadangkan saya cuba menggunakan indeks unik 2 lajur dan bukannya satu. Walau bagaimanapun, jika kekangan adalah UNIQUE INDEX "IDX_d12ecf48-302c-4292-8c8d-d2cc7c8149f9" ON "customer_group" ("name", "deleted_at") 那么我会得到与我想要的相反的结果： deleted_at 为 NULL，则 name ia boleh diulang.

Answer 1

Menggunakan lajur yang dikira, anda boleh melakukan perkara berikut:

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

Dengan indeks:

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

P.S. Saya tidak menggunakan ANSI_QUOTES.

Lihat juga: DBFIDDLE

Answer 2

MySQL tidak menyokong indeks separa, tetapi menyokong indeks ekspresi (bermula dengan MySQL 8.0). Berikut ialah 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));

Memandangkan UNIQUE mengikut peraturan NULL ANSI, jika lajur kedua indeks unik ialah NULL, mungkin terdapat sebarang bilangan pendua dalam lajur pertama. Lajur kedua apabila NULL tidak sama dengan mana-mana baris lain, jadi ia sentiasa "unik".

Jadi jika lajur kedua ialah nilai bukan NULL tetap hanya apabila "deleted_at" ialah NULL, maka "name" adalah unik pada semua baris dengan "deleted_at" ialah 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 |
+----+-------+---------------------+

Saya terpaksa menukar jenis "nama" kerana anda tidak boleh mencipta indeks pada lajur TEXT dalam MySQL dan ia mungkin terlalu panjang untuk had indeks 3072 bait.

Juga menukar KUNCI PRIMER untuk menghilangkan nama kekangan. MySQL akan sentiasa menamakan kunci utama dengan mudah PRIMARY.