Tại sao lỗi này xảy raBạn đã cố gắng xóa sạch một bảng bằng lệnh TRUNCATE, nhưng PostgreSQL đã ngăn lại. Điều này xảy ra vì một bảng khác đang phụ thuộc vào dữ liệu bạn muốn xóa thông qua ràng buộc khóa ngoại (foreign key). Postgres bảo vệ bạn khỏi việc tạo ra các bản ghi "mồ côi" — các hàng con trỏ tới ID cha không còn tồn tại.
Khác với lệnh DELETE vốn quét từng hàng để kiểm tra các phụ thuộc, TRUNCATE là một công cụ can thiệp thô. Nó bỏ qua các bước kiểm tra cấp độ hàng và chỉ đơn giản là giải phóng các trang dữ liệu. Vì nó không xem xét từng hàng riêng lẻ, nó không thể xác minh xem một bản ghi cụ thể có an toàn để xóa hay không. Để duy trì tính toàn vẹn dữ liệu, Postgres chặn hoàn toàn thao tác này nếu có bất kỳ khóa ngoại nào trỏ đến bảng.
Giải pháp 1: Sử dụng CASCADECách khắc phục nhanh nhất là thêm từ khóa CASCADE. Điều này yêu cầu PostgreSQL tự động truncate bất kỳ bảng nào tham chiếu đến bảng mục tiêu của bạn, và cả các bảng tham chiếu đến các bảng đó, v.v.
TRUNCATE TABLE users CASCADE;
Hãy cẩn thận: lệnh này có tính đệ quy. Nếu users liên kết với orders, và orders liên kết với order_items, cả ba bảng sẽ bị xóa sạch ngay lập tức. Trên một cơ sở dữ liệu lớn với hơn 10 bảng phụ thuộc, một lệnh duy nhất có thể xóa hàng triệu hàng trong toàn bộ schema của bạn.
Giải pháp 2: Truncate nhiều bảng cùng lúcNếu bạn muốn kiểm soát tốt hơn, hãy liệt kê mọi bảng liên quan trong một lệnh duy nhất. Postgres cho phép thực hiện TRUNCATE nếu tất cả các bên của mối quan hệ khóa ngoại được xóa tại cùng một thời điểm.
TRUNCATE TABLE users, orders, order_items;
Cách tiếp cận này thường an toàn hơn CASCADE. Nó buộc bạn phải xác nhận chính xác những bảng nào đang bị xóa sạch, giúp ngăn chặn việc vô tình xóa dữ liệu trong một bảng mà bạn quên rằng nó có liên kết.
Giải pháp 3: Mẹo sử dụng Role 'Replica' (Chỉ dành cho người dùng nâng cao)Đôi khi bạn cần xóa bảng cha trong khi vẫn giữ nguyên các bảng con — có lẽ trong quá trình di cư dữ liệu phức tạp hoặc khi nạp lại các ID cụ thể. Bạn có thể bỏ qua các kiểm tra ràng buộc bằng cách thay đổi session_replication_role thành replica.
BEGIN;
-- Tạm thời bỏ qua các khóa ngoại
SET LOCAL session_replication_role = 'replica';
TRUNCATE TABLE users;
-- Trở lại hành vi bình thường
SET LOCAL session_replication_role = 'origin';
COMMIT;
Hãy thận trọng khi sử dụng cách này. Nếu bạn không nạp lại bảng users với các ID chính xác ngay lập tức, cơ sở dữ liệu của bạn sẽ ở trạng thái không nhất quán. Ứng dụng của bạn có khả năng sẽ bị lỗi khi cố gắng join bảng orders với users mà không tìm thấy gì.
Giải pháp 4: Sử dụng DELETE cho các tập dữ liệu nhỏNếu bảng của bạn tương đối nhỏ — ví dụ: dưới 100.000 hàng — bạn có thể không cần dùng đến TRUNCATE. Một câu lệnh DELETE tiêu chuẩn sẽ tuân thủ các ràng buộc khóa ngoại và trigger.
DELETE FROM users;
Trong khi TRUNCATE chỉ mất vài mili giây bất kể kích thước bảng, hiệu suất của DELETE sẽ thay đổi theo số lượng hàng. Trên một bảng có 5 triệu hàng, DELETE có thể mất 30 giây và làm phình transaction log, trong khi TRUNCATE sẽ gần như tức thời.
Cách xác định các bảng phụ thuộcTrước khi bạn chạy CASCADE, việc biết bảng nào thực sự đang cản trở bạn là rất hữu ích. Hãy chạy truy vấn này để tìm mọi bảng hiện đang tham chiếu đến mục tiêu của bạn:
SELECT
rel_tco.table_name AS referencing_table,
rel_tco.constraint_name
FROM
information_schema.referential_constraints rco
JOIN
information_schema.table_constraints rel_tco
ON rco.constraint_name = rel_tco.constraint_name
JOIN
information_schema.table_constraints pk_tco
ON rco.unique_constraint_name = pk_tco.constraint_name
WHERE
pk_tco.table_name = 'users';