UNIQUE ที่ยอมให้ซ้ำ — soft delete กับ NULL ที่ไม่เท่ากับตัวเอง

ตาราง users มี UNIQUE (email, deleted_at) เขียนไว้ชัดเจน แล้วมันรับอีเมลเดียวกัน ซ้ำได้ 3 แถวรวด ทุกแถวยังไม่ถูกลบ

ไม่มี error ไม่มีคำเตือน รหัสออกเป็นศูนย์

ทรงที่พังจริง และมันพังเงียบ

ข้อจำกัดถูกประกาศไว้จริง ฐานข้อมูลก็บังคับมันจริง แต่มันบังคับคนละข้อกับที่คุณคิด

CREATE TABLE users (
  id         bigserial PRIMARY KEY,
  email      text NOT NULL,
  deleted_at timestamptz,
  UNIQUE (email, deleted_at)
);

INSERT INTO users (email) VALUES ('a@shop.test');
INSERT INTO users (email) VALUES ('a@shop.test');
INSERT INTO users (email) VALUES ('a@shop.test');

 id |    email    | deleted_at
----+-------------+------------
  1 | a@shop.test |
  2 | a@shop.test |
  3 | a@shop.test |
(3 rows)

deleted_at ของทั้งสามแถวเป็น NULL และในสายตาของ index NULL ไม่เท่ากับ NULL · สามแถวนี้จึงเป็นคนละคู่กันทั้งหมด ข้อจำกัดไม่ถูกละเมิดสักครั้ง

คำที่หลอกคือคำว่า UNIQUE ไม่ใช่คำว่า NULL

ทุกคนรู้ว่า NULL ไม่เท่ากับอะไรเลย รวมทั้งตัวมันเอง · ที่หลุดคือการเอากฎนั้น ไปวางในบริบทของ index

ใน WHERE กฎนี้ทำให้คุณ หาไม่เจอ · ใน index กฎเดียวกันทำให้คุณ ใส่ได้เพิ่ม · ผลตรงข้ามกันจากกฎข้อเดียว

กฎเดียวกัน ผลตรงข้ามกัน

ตรรกะสามค่าไม่ได้เปลี่ยนพฤติกรรมของมัน สิ่งที่เปลี่ยนคือใครเป็นคนถาม

SELECT count(*) FILTER (WHERE deleted_at =  NULL) AS "= NULL",
       count(*) FILTER (WHERE deleted_at IS NULL) AS "IS NULL"
FROM users;

เขียนว่า	ได้กี่แถว	เพราะ
`WHERE deleted_at = NULL`	0	`NULL = NULL` ให้ค่า `NULL` ซึ่งไม่ใช่ `TRUE` แถวจึงไม่ผ่านตัวกรอง
`WHERE deleted_at IS NULL`	3	`IS NULL` เป็นตัวตรวจสถานะ ไม่ใช่การเปรียบเทียบ

ใน WHERE การที่ NULL ไม่เท่ากับตัวเองทำให้ผลลัพธ์ หายไป · ใน index มันทำให้แถว เพิ่มเข้ามาได้ เพราะ index ตัดสินว่าซ้ำจากการเทียบค่า และการเทียบที่ไม่ให้ TRUE แปลว่าไม่ซ้ำ

ทางแก้ที่หนึ่ง — partial unique index

ประกาศให้ชัดว่าเราหวงความไม่ซ้ำเฉพาะในแถวที่ยังไม่ถูกลบ

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

deleted_at หายไปจาก key แล้ว มันย้ายไปอยู่ในเงื่อนไขแทน · index เก็บเฉพาะแถวที่ยังไม่ถูกลบ และในกลุ่มนั้น email ต้องไม่ซ้ำ

ERROR:  duplicate key value violates unique constraint "users_live_email"
DETAIL:  Key (email)=(a@shop.test) already exists.

SQLSTATE ที่ได้คือ 23505 และชื่อที่ปรากฏคือ users_live_email ซึ่งเป็นชื่อ index ไม่ใช่ชื่อ constraint เพราะรูปนี้ไม่ได้สร้าง constraint ขึ้นมา

ทางแก้ที่สอง — `NULLS NOT DISTINCT`

PostgreSQL 15 ขึ้นไปให้สั่งได้ตรง ๆ ว่าให้ถือว่า NULL เท่ากัน

CREATE TABLE users (
  id         bigserial PRIMARY KEY,
  email      text NOT NULL,
  deleted_at timestamptz,
  UNIQUE NULLS NOT DISTINCT (email, deleted_at)
);

รูปนี้ปิดรูเดิมได้เหมือนกัน และข้อความที่ได้บอกตรง ๆ ว่ามันเทียบ null ด้วย

DETAIL:  Key (email, deleted_at)=(a@shop.test, null) already exists.

อ่านดูแล้วรูปนี้สะอาดกว่า เพราะมันยังเป็น constraint จริง ไม่ใช่ index ที่แขวนไว้เฉย ๆ · แต่พอวัดทรงที่สอง มันเปิดรูที่ทางแรกไม่มี

รูใหม่ที่ทางที่สองเปิดไว้

ลบทั้งชุดในคำสั่งเดียว แล้ว now() ให้ค่าเดียวกันทุกแถว

now() คืนเวลาของ transaction ไม่ใช่เวลาของแต่ละแถว · UPDATE users SET deleted_at = now() หนึ่งคำสั่งจึงเขียนค่า เดียวกันเป๊ะ ลงทุกแถว

ถ้ามีสองแถวที่ email เท่ากัน แถวหนึ่งเคยถูกลบไปแล้วและอีกแถวเพิ่งสมัครใหม่ พอลบทั้งชุด ทั้งคู่จะมี (email, deleted_at) ชุดเดียวกัน

ERROR:  duplicate key value violates unique constraint "users_email_deleted_at_key"
DETAIL:  Key (email, deleted_at)=(a@shop.test, 2026-08-29 16:04:16.001299+00) already exists.

ทางแก้	แถวซ้ำตอนยังไม่ลบ	ลบทั้งชุดในคำสั่งเดียว
`UNIQUE (email, deleted_at)` เดิม	ยอมให้ซ้ำ	ผ่าน
partial index	ปฏิเสธ	ผ่าน · เหลือ 2 แถว `deleted_at` ค่าเดียว
`NULLS NOT DISTINCT`	ปฏิเสธ	ล้ม SQLSTATE 23505

ทางแก้ที่อ่านแล้วสะอาดกว่า ไม่ได้แปลว่าปลอดภัยกว่า

NULLS NOT DISTINCT บังคับให้ (email, deleted_at) ไม่ซ้ำ ทั้งตาราง ไม่ใช่เฉพาะแถวที่ยังไม่ถูกลบ · การลบซ้ำที่เวลาเดียวกันจึงกลายเป็นการละเมิด

partial index ไม่สนใจแถวที่ถูกลบไปแล้วเลย ลบกี่ครั้งที่เวลาไหนก็ได้

ถ้าคุณจะใช้ NULLS NOT DISTINCT กับ soft delete ต้องยอมรับว่า เวลาที่ลบกลายเป็น ส่วนหนึ่งของ key และงานที่ลบเป็นชุดต้องเขียนให้แต่ละแถวได้เวลาไม่ซ้ำกัน

ราคาที่จ่ายเป็นไบต์

ตารางที่ใช้ soft delete มานาน แถวส่วนใหญ่คือแถวที่ถูกลบไปแล้ว

วัดบนตาราง 100,000 แถว ที่เหลือแถวยังไม่ถูกลบ 5,000 แถว

index	ไบต์
partial · `WHERE deleted_at IS NULL`	221,184
เต็ม · `(email, deleted_at)`	4,988,928

index เต็มต้องเก็บทุกแถวที่ถูกลบไปแล้วด้วย ทั้งที่ไม่มี query ไหนถามถึงมันในฐานะ ข้อจำกัดความไม่ซ้ำ · ส่วนที่ใหญ่กว่าคือส่วนที่ไม่ได้ทำงาน

สรุป

ข้อจำกัดที่ประกาศไว้ กับข้อจำกัดที่ได้จริง เป็นคนละข้อกันได้

UNIQUE (email, deleted_at) ไม่ได้ บังคับว่าอีเมลที่ยังไม่ถูกลบต้องไม่ซ้ำ · มันบังคับว่าคู่ของสองค่าต้องไม่ซ้ำ และคู่ที่มี NULL ไม่เคยซ้ำกับใคร
ตรรกะสามค่าข้อเดียวกัน ทำให้ WHERE หาไม่เจอ และทำให้ index ยอมรับเพิ่ม
partial index ย้าย deleted_at จาก key ไปเป็นเงื่อนไข · เล็กกว่าและไม่ผูกกับเวลาที่ลบ
NULLS NOT DISTINCT ปิดรูเดิมได้ แต่ทำให้เวลาที่ลบกลายเป็นส่วนหนึ่งของ key

🔗 วิธีวัดซ้ำ

ทุกตัวเลขในหน้านี้มาจาก PostgreSQL 18.6 ตัวจริงใน container ที่คุมตัวแปรแล้ว

scripts/pg-transaction-spike/up.sh
python3 scripts/unique-permits-duplicates/probe.py
npm run verify:unique-duplicates

probe มีช่องควบคุมสองช่อง คือทรงที่ต้องถูกปฏิเสธต้องถูกปฏิเสธจริง และทรงที่ควรผ่าน ต้องผ่านจริง · ถอด partial index ออกแล้วมันแดงทันที · และเมื่อฐานข้อมูลไม่ขึ้น มันออกด้วย รหัส 2 ซึ่งแปลว่าวัดไม่ได้ ไม่ใช่รหัส 1 ที่แปลว่าข้ออ้างผิด

PostgreSQL — CREATE TABLE · UNIQUE (เข้าถึง 2026-08-29)
PostgreSQL — Partial Indexes (เข้าถึง 2026-08-29)
PostgreSQL — Comparison Functions and Operators (เข้าถึง 2026-08-29)

UNIQUE ที่​ยอม​ให้​ซ้ำ — soft delete กับ NULL ที่​ไม่​เท่ากับ​ตัว​เอง

ทรง​ที่​พัง​จริง และ​มัน​พัง​เงียบ

กฎ​เดียวกัน ผล​ตรง​ข้าม​กัน

ทาง​แก้​ที่​หนึ่ง — partial unique index

ทาง​แก้​ที่​สอง — NULLS NOT DISTINCT

รู​ใหม่​ที่ทาง​ที่​สอง​เปิด​ไว้

ราคา​ที่​จ่าย​เป็น​ไบต์