Sửa lỗi PostgreSQL 'could not create unique index' Khi Dữ Liệu Bị Trùng Lặp

Lỗi Gặp Phải

Bạn chạy một migration để thêm ràng buộc UNIQUE, và PostgreSQL chặn ngay lập tức:

ERROR: could not create unique index "users_email_key"
DETAIL: Key (email)=(user@example.com) is duplicated.

PostgreSQL quét toàn bộ bảng trước khi xây dựng index. Ngay khi phát hiện hai hàng có cùng giá trị, nó hủy bỏ — ràng buộc không bao giờ được tạo.

Ba tình huống thường gây ra lỗi này nhất:

• Một migration thêm UNIQUE vào cột trước đó không có ràng buộc
• Dữ liệu từ nhiều nguồn được gộp lại mà không kiểm tra tính duy nhất trước
• Một lỗi trong code ứng dụng cho phép chèn dữ liệu trùng lặp trước khi có bất kỳ biện pháp kiểm soát nào

Tìm Các Bản Ghi Trùng Lặp Trước

Chưa cần động vào gì cả. Hãy bắt đầu bằng cách xem chính xác tình trạng hiện tại:

SELECT email, COUNT(*) AS cnt
FROM users
GROUP BY email
HAVING COUNT(*) > 1
ORDER BY cnt DESC;

Câu lệnh này liệt kê tất cả email trùng lặp cùng với số lần xuất hiện. Với bảng có hàng triệu hàng, thêm LIMIT 100 để nắm nhanh quy mô trước khi quyết định chiến lược dọn dẹp.

Để xem toàn bộ các hàng — không chỉ giá trị vi phạm — hãy dùng window function:

SELECT *
FROM (
  SELECT *,
         ROW_NUMBER() OVER (PARTITION BY email ORDER BY id) AS rn
  FROM users
) ranked
WHERE rn > 1;

Các hàng có rn > 1 là bản sao trùng lặp. Hàng có rn = 1 là hàng bạn giữ lại.

Cách Nhanh: Xóa Các Hàng Trùng Lặp

Nếu bạn có thể loại bỏ các bản sao một cách an toàn, hãy giữ hàng có id nhỏ nhất và xóa phần còn lại:

DELETE FROM users
WHERE id IN (
  SELECT id
  FROM (
    SELECT id,
           ROW_NUMBER() OVER (PARTITION BY email ORDER BY id) AS rn
    FROM users
  ) ranked
  WHERE rn > 1
);

Đặt trong một transaction để bạn có thể kiểm tra kết quả trước khi xác nhận:

BEGIN;

DELETE FROM users
WHERE id IN (
  SELECT id
  FROM (
    SELECT id,
           ROW_NUMBER() OVER (PARTITION BY email ORDER BY id) AS rn
    FROM users
  ) ranked
  WHERE rn > 1
);

-- Phải trả về 0 hàng nếu việc dọn dẹp thành công
SELECT email, COUNT(*)
FROM users
GROUP BY email
HAVING COUNT(*) > 1;

-- Sạch rồi? COMMIT. Có gì đó sai? ROLLBACK.
COMMIT;

Một bảng users thực tế với 500.000 hàng và vài trăm bản sao thường hoàn thành lệnh xóa này trong chưa đến một giây. Các bảng lớn hơn với hàng triệu bản sao có thể cần xóa theo lô để tránh khóa bảng quá lâu.

Khi Không Thể Xóa — Hợp Nhất hoặc Đặt NULL

Xóa không phải lúc nào cũng là lựa chọn khả thi. Các bảng khác có thể tham chiếu đến những hàng trùng lặp đó qua khóa ngoại, hoặc quy tắc nghiệp vụ có thể yêu cầu giữ lại mọi bản ghi.

Phương án A: Gán lại tham chiếu khóa ngoại, rồi xóa

-- Chuyển tất cả đơn hàng từ user trùng lặp (id=99) sang user chính (id=42)
UPDATE orders SET user_id = 42 WHERE user_id = 99;

-- Bây giờ có thể xóa bản sao an toàn
DELETE FROM users WHERE id = 99;

Phương án B: Đặt NULL cho các giá trị trùng lặp

Index unique của PostgreSQL hoàn toàn bỏ qua các giá trị NULL — nhiều NULL trong cùng một cột không xung đột với nhau. Vì vậy nếu cột cho phép NULL, bạn có thể xóa trắng các bản sao thay vì xóa hàng:

UPDATE users
SET email = NULL
WHERE id IN (
  SELECT id
  FROM (
    SELECT id,
           ROW_NUMBER() OVER (PARTITION BY email ORDER BY id) AS rn
    FROM users
  ) ranked
  WHERE rn > 1
);

Thêm Ràng Buộc

Bảng đã sạch. Đến lúc tạo ràng buộc:

-- Khuyến nghị: ràng buộc có tên để có thể xóa hoặc tham chiếu sau này
ALTER TABLE users ADD CONSTRAINT users_email_key UNIQUE (email);

-- Thay thế: index độc lập
CREATE UNIQUE INDEX users_email_key ON users (email);

Có bảng production lớn? Dùng CONCURRENTLY. Nếu không, PostgreSQL giữ AccessShareLock trong suốt quá trình xây dựng index — chặn các thao tác ghi trong toàn bộ thời gian đó, chẳng hạn với bảng 50 triệu hàng.

CREATE UNIQUE INDEX CONCURRENTLY users_email_key ON users (email);

Lưu ý: CONCURRENTLY không thể chạy bên trong một transaction block. Hãy chạy nó như một câu lệnh độc lập.

Ngăn Lỗi Tái Diễn Trong Các Migration

Nếu lỗi này xuất hiện trong một migration script, hãy đóng gói bước loại bỏ trùng lặp trực tiếp vào migration — đừng phụ thuộc vào việc dữ liệu đã sạch trước khi migration chạy:

-- Migration: 20240501_add_unique_email.sql

-- Bước 1: Xóa các bản sao trùng lặp
DELETE FROM users
WHERE id IN (
  SELECT id
  FROM (
    SELECT id,
           ROW_NUMBER() OVER (PARTITION BY email ORDER BY id) AS rn
    FROM users
  ) ranked
  WHERE rn > 1
);

-- Bước 2: Thêm ràng buộc
ALTER TABLE users ADD CONSTRAINT users_email_key UNIQUE (email);

Đóng gói cả hai bước lại với nhau có nghĩa là migration sẽ xử lý được bất kỳ trạng thái dữ liệu nào. Không có bất ngờ ở môi trường tiếp theo.

Xác Minh Kết Quả

-- 1. Xác nhận không còn bản sao nào
SELECT email, COUNT(*)
FROM users
GROUP BY email
HAVING COUNT(*) > 1;
-- Kết quả mong đợi: 0 hàng

-- 2. Xác nhận ràng buộc tồn tại trong catalog
SELECT conname, contype
FROM pg_constraint
WHERE conrelid = 'users'::regclass
  AND conname = 'users_email_key';
-- Kết quả mong đợi: 1 hàng, contype = 'u'

-- 3. Xác nhận ràng buộc thực sự từ chối dữ liệu trùng lặp
INSERT INTO users (email) VALUES ('user@example.com');
INSERT INTO users (email) VALUES ('user@example.com'); -- phải thất bại
-- Kết quả mong đợi: ERROR: duplicate key value violates unique constraint