MySQL は、別の列が空の場合にのみ一意のインデックスを制約します

Question

MySQL (8.0.32) の方言に合わせて Postgres スキーマを書き直そうとしています。ご存知のとおり、MySQL は部分インデックスをサポートしていません。

次の場合、deleted_at が null の場合、customer_group.name に一意のデータを強制するインデックスのみが存在します。

削除されたエントリが一意であることを保証することに意味がないので、これは理にかなっています。ただし、部分的なインデックスを作成せずに同じ制約を実装する方法がわかりません。

リーリー

追記:私は ANSI_QUOTES を使用しています。

誰かが、1 つの列ではなく 2 つの列に一意のインデックスを使用してみることを提案しました。ただし、制約が UNIQUE INDEX "IDX_d12ecf48-302c-4292-8c8d-d2cc7c8149f9" ON "customer_group" ("name", "deleted_at") の場合、希望とは逆の結果が得られます。 deleted_at が NULL の場合、name を繰り返すことができます。

Answer 1

計算列を使用すると、次のことができます:

リーリー

インデックス付き:

リーリー

追記: ANSI_QUOTES は使用しませんでした。

参照: DBFIDDLE

Answer 2

MySQL は部分インデックスをサポートしませんが、式インデックスをサポートします (MySQL 8.0 以降)。デモは次のとおりです:

リーリー

UNIQUE は ANSI の NULL 規則に従うため、一意のインデックスの 2 番目の列が NULL の場合、最初の列には任意の数の重複が存在する可能性があります。 NULL の場合の 2 番目の列は他の行と等しくないため、常に「一意」になります。

したがって、「deleted_at」が NULL の場合にのみ 2 番目の列が NULL 以外の固定値である場合、「deleted_at」が NULL であるすべての行で「name」は一意になります。

リーリー

MySQL では TEXT カラムにインデックスを作成できず、インデックスの 3072 バイト制限には長すぎる可能性があるため、「名前」のタイプを変更する必要がありました。

制約名を省略するために PRIMARY KEY も変更しました。 MySQL は常に主キーに単に PRIMARY という名前を付けます。