Unique Constraint và NULL - Lỗi bất ngờ

Lỗi bẫy kinh điển mà hầu hết developer đều mắc ít nhất một lần: vì NULL ≠ NULL, ràng buộc UNIQUE không chặn được các dòng trùng nhau chứa NULL.

Tình huống: hệ thống đặt hàng sản phẩm giới hạn

-- Mỗi khách hàng chỉ được 1 đơn hàng CHƯA GIAO cho mỗi sản phẩm
-- shipment_id = NULL khi chưa giao, có giá trị khi đã giao
CREATE UNIQUE INDEX one_pending_order ON orders (customer_id, shipment_id);

Kỳ vọng: khách 17 đã có đơn (17, NULL) → không thể tạo thêm (17, NULL). Thực tế:

│ customer_id │ shipment_id │
│     17      │    NULL     │ ← đơn 1, chưa giao
│     17      │    NULL     │ ← đơn 2, chưa giao: KHÔNG BÁO LỖI!
│     17      │    1001     │ ← đơn 3, đã giao
 
Lý do: NULL ≠ NULL (NULL không bằng bất kỳ giá trị nào, kể cả chính nó)
→ (17, NULL) và (17, NULL) được coi là KHÁC NHAU
→ Khách hàng 17 có thể đặt VÔ HẠN đơn chưa giao!

Giải pháp PostgreSQL 15+: NULLS NOT DISTINCT

CREATE UNIQUE INDEX one_pending_order
ON orders (customer_id, shipment_id)
NULLS NOT DISTINCT;
-- Giờ (17, NULL) chỉ xuất hiện được 1 lần → đúng mong đợi

Giải pháp chung cho mọi database

Thay NULL bằng giá trị đặc biệt (ví dụ -1) qua biểu thức CASE:

CREATE UNIQUE INDEX one_pending_order ON orders (
    customer_id,
    (CASE WHEN shipment_id IS NULL THEN -1 ELSE shipment_id END)
);
-- -1 = -1 → đúng → vi phạm ràng buộc → không cho phép → ĐÚNG HÀNH VI

Lưu ý: vì cột shipment_id bị biến đổi trong index, index này không dùng được cho query tìm kiếm bình thường trên (customer_id, shipment_id) - cần tạo thêm index riêng nếu cần tra cứu (Column Transformation).

Liên quan

• NULL và Index - NULL ≠ NULL là gốc rễ
• Functional Index - unique index trên biểu thức CASE
• Database Constraint - bức tranh lớn về ràng buộc