Partial Unique Index ใน PostgreSQL ใช้ยังไง คู่กับ soft delete

ตาราง users ที่ใช้ soft delete มักเจอเรื่องเดียวกัน ผู้ใช้ลบบัญชีไปแล้ว พอกลับมาสมัครใหม่ด้วย email เดิม ฐานข้อมูลกลับฟ้องว่า email ซ้ำ เพราะแถวเก่ายังอยู่ในตาราง แค่มี deleted_at กำกับไว้ PostgreSQL แก้เรื่องนี้ได้ด้วย Partial Unique Index คือ unique index ที่มี WHERE ต่อท้าย ให้เช็คค่าซ้ำเฉพาะแถวที่ตรงเงื่อนไข

Partial index คือ index ที่เก็บแค่บางแถว#

Partial index เก็บเฉพาะแถวที่ผ่านเงื่อนไขใน WHERE เขียนเงื่อนไขได้เหมือนใน SELECT ทั่วไป

ข้อดีอย่างแรกคือ index เล็กลง เอกสารของ PostgreSQL บอกว่าค่าที่มีในตารางเกินไม่กี่เปอร์เซ็นต์ของทุกแถว query ที่ค้นค่านั้นก็ไม่ใช้ index อยู่ดี เราจึงตัดแถวพวกนั้นออกจาก index ไปเลยได้ index ที่เล็กลงทำให้ query ที่ใช้ index เร็วขึ้น และตอน insert หรือ update บางแถว PostgreSQL ก็ไม่ต้องแก้ index ด้วย

CREATE INDEX orders_unbilled_index ON orders (created_at)
    WHERE billed_at IS NULL;

ตัวอย่างนี้ index เก็บเฉพาะ order ที่ยังไม่ออกบิล ซึ่งมักมีไม่กี่แถวเมื่อเทียบกับ order ทั้งหมด

ใส่ WHERE ให้ unique index แล้ว เช็คซ้ำเฉพาะแถวที่ยังไม่ลบ#

สมมติตาราง users หน้าตาแบบนี้

CREATE TABLE users (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name varchar(255) NOT NULL,
    email varchar(255) NOT NULL,
    password varchar(255) NOT NULL,
    deleted_at timestamp(0) without time zone
);

เราอยากให้ email ไม่ซ้ำกันเฉพาะ user ที่ยังใช้งานอยู่ ส่วน user ที่ soft delete ไปแล้ว (มีค่าใน deleted_at) จะซ้ำกับใครก็ได้ ก็สร้าง unique index ที่มี WHERE แบบนี้

CREATE UNIQUE INDEX users_email_active_unique
    ON users (email)
    WHERE deleted_at IS NULL;

ทีนี้ลอง insert ดู แถวที่ลบไปแล้วกับแถวที่ยังใช้งานมี email เดียวกันได้ แต่ถ้ามีแถวที่ยังใช้งานอยู่สองแถว PostgreSQL จะไม่ยอมบันทึก

INSERT INTO users (name, email, password, deleted_at)
VALUES ('สมชาย', 'somchai@example.com', 'x', now());

INSERT INTO users (name, email, password)
VALUES ('สมชาย', 'somchai@example.com', 'x');  -- ผ่าน

INSERT INTO users (name, email, password)
VALUES ('สมชาย 2', 'somchai@example.com', 'x');  -- error

ERROR:  duplicate key value violates unique constraint "users_email_active_unique"
DETAIL:  Key (email)=([email protected]) already exists.

สร้าง partial unique index ใน migration ของ Laravel#

Schema builder ของ Laravel ไม่มีคำสั่งใส่ WHERE ให้ index ใน Laravel 13 $table->unique() บน PostgreSQL สร้างเป็น unique constraint ธรรมดา เราจึงเขียน SQL เองด้วย DB::statement() ในตัวอย่างนี้ users มีคอลัมน์ deleted_at อยู่แล้ว และมี unique บน email จาก migration ตั้งต้นของ Laravel

<?php

use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Schema;

return new class extends Migration
{
    public function up(): void
    {
        Schema::table('users', function (Blueprint $table) {
            $table->dropUnique(['email']);
        });

        DB::statement('CREATE UNIQUE INDEX users_email_active_unique ON users (email) WHERE deleted_at IS NULL');
    }

    public function down(): void
    {
        Schema::table('users', function (Blueprint $table) {
            $table->dropIndex('users_email_active_unique');
            $table->unique('email');
        });
    }
};

dropUnique(['email']) ลบ constraint users_email_unique ตัวเดิม ส่วนตัวใหม่เป็น index ไม่ใช่ constraint ตอนลบใน down() จึงใช้ dropIndex() ถ้าตอนนั้นมี email ซ้ำในแถวที่ลบไปแล้ว down() จะสร้าง unique กลับไม่ได้ ต้องเคลียร์ข้อมูลก่อน

validate ด้วย withoutTrashed() ให้ตรงกับ index#

index กันข้อมูลซ้ำที่ฐานข้อมูล แต่ผู้ใช้ควรเห็นข้อความ error ในฟอร์มก่อน เราบอก rule unique ให้ข้ามแถวที่ soft delete ไปแล้วด้วย withoutTrashed() ซึ่งเติม whereNull('deleted_at') ให้ เงื่อนไขจึงตรงกับ index

use App\Models\User;
use Illuminate\Validation\Rule;

$request->validate([
    'email' => [
        'required',
        'email',
        Rule::unique(User::class)->ignore($user)->withoutTrashed(),
    ],
]);

upsert() หา partial unique index ไม่เจอ#

upsert() บันทึกข้อมูลทีละมาก ๆ ได้ใน query เดียว แต่ User::upsert($rows, ['email'], ['name']) บน PostgreSQL สร้าง SQL เป็น ON CONFLICT ("email") DO UPDATE ซึ่งเอกสารของ PostgreSQL บอกว่า ON CONFLICT จะใช้ partial unique index ได้ก็ต่อเมื่อเราใส่เงื่อนไขของ index ต่อท้ายด้วย Laravel ไม่มีที่ให้ใส่ เลยได้ error นี้

ERROR:  there is no unique or exclusion constraint matching the ON CONFLICT specification

ถ้าบันทึกทีละแถว updateOrCreate() ใช้ได้ตามปกติ เพราะ Laravel ค้นด้วย SELECT ก่อนแล้วค่อย insert หรือ update และ query ของ model ที่ใช้ SoftDeletes ก็ข้ามแถวที่ลบไปแล้วให้อยู่แล้ว ถ้าต้อง upsert ทีละมาก ๆ ให้เขียน SQL เองแล้วใส่เงื่อนไขเดียวกับ index

INSERT INTO users (name, email, password)
VALUES ('สมชาย', 'somchai@example.com', 'x')
ON CONFLICT (email) WHERE deleted_at IS NULL
DO UPDATE SET name = excluded.name;

เพิ่ม unique ให้ตารางที่มีข้อมูลซ้ำอยู่แล้ว#

partial unique index ช่วยได้ตอนระบบใช้งานไปนานแล้วด้วย สมมติตอนออกแบบตาราง orders เราไม่ได้ทำ unique ที่ order_no ไว้ แล้วข้อมูลปี 2023 มีเลขซ้ำกันอยู่บ้าง ถ้าสร้าง unique index ธรรมดาจะสร้างไม่ผ่าน เพราะข้อมูลเก่าซ้ำ เราไม่ต้องไล่เคลียร์ข้อมูลเก่าก่อน ให้บังคับเฉพาะ order ตั้งแต่วันที่ 1 มกราคม 2024 เป็นต้นไป

CREATE UNIQUE INDEX orders_order_no_unique
    ON orders (order_no)
    WHERE created_at >= '2024-01-01';

order ที่สร้างตั้งแต่ 2024-01-01 จะมี order_no ซ้ำกันไม่ได้ ส่วน order ปี 2023 ที่ซ้ำกันอยู่ก่อนแล้วก็ปล่อยไว้ได้

แล้วบน MySQL ล่ะ#

MySQL ใส่ WHERE ให้ index ไม่ได้ แต่ unique index ของ MySQL ยอมให้มี NULL ซ้ำกันได้หลายแถว เราจึงทำ column ที่คำนวณจาก email โดยให้เป็น NULL เมื่อแถวนั้นลบไปแล้ว แล้วทำ unique ที่ column นั้นแทน ใน migration ใช้ virtualAs() ได้เลย

Schema::table('users', function (Blueprint $table) {
    $table->dropUnique(['email']);
    $table->string('active_email')->nullable()->virtualAs('if(deleted_at is null, email, null)');
    $table->unique('active_email');
});

ถ้าใช้ MySQL 8.0.13 ขึ้นไป จะสร้าง functional index จาก expression ตรง ๆ โดยไม่ต้องมี column เพิ่มก็ได้ (ใส่วงเล็บสองชั้น)

CREATE UNIQUE INDEX users_email_active_unique
    ON users ((IF(deleted_at IS NULL, email, NULL)));

ใครใช้ MariaDB เช่นตัวที่มากับ XAMPP วิธี virtualAs() ก็ใช้ได้ เพราะ MariaDB ทำ index บน virtual column ได้ เช่นกัน