Dùng khóa chính và khóa ngoại để giữ đúng quan hệ đơn hàng

Một đơn hàng không nên vô tình giữ tham chiếu tới khách hàng đã mất, và một dòng hàng không nên trỏ tới đơn chưa từng tồn tại. Khóa chính tạo định danh không nhập nhằng cho một hàng. Khóa ngoại buộc tham chiếu được lưu ở bảng khác tuân theo định danh đó. Kết hợp hai loại khóa giúp bảo vệ quan hệ ngay cả khi worker hoặc script bảo trì ghi dữ liệu mà không đi qua HTTP handler.

Nếu bạn đã viết được lệnh insert và select, bước tiếp theo là đọc schema như tập các thao tác được phép và bị từ chối. Ví dụ PostgreSQL nhỏ này mô hình hóa khách hàng, đơn hàng và các dòng được đánh số. Nó cũng chỉ ra một giới hạn quan trọng: toàn vẹn tham chiếu không quyết định nghiệp vụ có cho phép xóa đơn đã thanh toán hay không.

Đặt định danh ở đúng phạm vi

Chạy đoạn sau trong một cơ sở dữ liệu PostgreSQL trống. Ví dụ cấp trực tiếp các mã số nguyên để dễ nhìn quan hệ; nó chưa triển khai cách sinh mã định danh.

CREATE TABLE customers (
  id bigint PRIMARY KEY,
  display_name text NOT NULL
);
CREATE TABLE orders (
  id bigint PRIMARY KEY,
  customer_id bigint NOT NULL REFERENCES customers(id) ON DELETE RESTRICT,
  state text NOT NULL DEFAULT 'draft' CHECK (state IN ('draft', 'paid'))
);
CREATE TABLE order_items (
  order_id bigint NOT NULL REFERENCES orders(id) ON DELETE CASCADE,
  line_no integer NOT NULL CHECK (line_no > 0),
  sku text NOT NULL,
  quantity integer NOT NULL CHECK (quantity > 0),
  PRIMARY KEY (order_id, line_no)
);

Khóa chính của khách hàng và đơn hàng là duy nhất và không null. Khóa của dòng có hai cột vì dòng số một thuộc một đơn cụ thể. Đơn 418 có thể có dòng một, và đơn 419 cũng có thể có dòng một. Mỗi đơn đều không thể chứa hai hàng cùng số dòng.

Khóa này không yêu cầu số dòng liên tiếp. Dòng một và ba vẫn hợp lệ dù thiếu dòng hai. Nó cũng không yêu cầu SKU duy nhất trong một đơn. Hai dòng riêng có thể dùng cùng SKU nếu số dòng khác nhau. Đó là các quyết định sản phẩm riêng, nên đừng gán cho khóa ghép những ý nghĩa mà nó không khai báo.

Tham chiếu khách hàng bắt buộc nhờ NOT NULL. Một khóa ngoại đơn cột cho phép null sẽ chấp nhận tham chiếu null, trong khi vẫn từ chối mã khác null không có hàng cha. Khóa ngoại và ràng buộc không null trả lời hai câu hỏi khác nhau: quan hệ được cung cấp có hợp lệ không, và có bắt buộc phải cung cấp quan hệ ấy không.

Tài liệu ràng buộc PostgreSQL quy định khóa chính duy nhất, không null và điều kiện của các cột được tham chiếu. Ở đây mọi khóa ngoại đều trỏ tới khóa chính. PostgreSQL còn hỗ trợ ràng buộc unique phù hợp hoặc unique index không phải partial làm đích; index thông thường không bảo đảm duy nhất là chưa đủ.

SKU chủ ý chỉ là văn bản. Schema không có bảng sản phẩm, giá hiện tại, giữ chỗ tồn kho hay quy tắc phân quyền. Lưu chuỗi trông giống mã sản phẩm không thực thi quan hệ tới sản phẩm. Đơn hàng thực tế có thể giữ bản chụp SKU lịch sử, tham chiếu sản phẩm hoặc cả hai, tùy dữ liệu nào phải tồn tại qua những lần đổi danh mục.

Thêm lịch sử đơn hàng nhỏ, dễ kiểm tra

Nạp hai khách hàng, một đơn nháp, một đơn đã thanh toán và ba dòng:

INSERT INTO customers VALUES (73, 'Aster Studio'), (74, 'Unused customer');
INSERT INTO orders (id, customer_id, state)
VALUES (418, 73, 'draft'), (419, 73, 'paid');
INSERT INTO order_items VALUES
  (418, 1, 'CAMERA', 1), (418, 2, 'BATTERY', 2), (419, 1, 'MIC', 1);
SELECT order_id, line_no, sku, quantity
FROM order_items ORDER BY order_id, line_no;

Kết quả có (418, 1), (418, 2) và (419, 1). Hàng cuối cho thấy phạm vi của định danh ghép. Khách hàng 74 chưa có đơn nào và sẽ dùng trong bài tập đồng thời phía sau.

Mở ảnh kích thước đầy đủ ↗

Hình tách quan hệ khỏi chính sách xóa. Nó không hàm ý khách hàng quyết định toàn bộ vòng đời nghiệp vụ của đơn, hay trạng thái đã thanh toán khiến đơn không thể bị xóa.

Thử từng thao tác bị từ chối ở chế độ autocommit, hoặc dùng savepoint khi thực nghiệm trong một transaction. Một câu lệnh thất bại thường khiến transaction PostgreSQL tường minh cần rollback trước khi tiếp tục. Đừng dán hàng loạt câu lệnh dự kiến thất bại vào migration rồi cho rằng các câu phía sau vẫn chạy.

Thao tác với dữ liệu đã nạp	SQLSTATE dự kiến	Lý do thất bại
Thêm đơn 420 cho khách hàng 999	23503	Không có khách hàng được tham chiếu
Thêm đơn 420 với khách hàng null	23502	Khách hàng là bắt buộc
Thêm một đơn 418 nữa	23505	Định danh đơn đã tồn tại
Thêm một dòng (418, 1) nữa	23505	Định danh dòng đã tồn tại
Thêm dòng cho đơn 999	23503	Không có đơn được tham chiếu
Thêm dòng số không hoặc số lượng không	23514	Vi phạm điều kiện giá trị dương
Xóa khách hàng 73	23503	Các đơn hiện có vẫn tham chiếu khách hàng

Bộ kiểm tra PostgreSQL 17.11 cục bộ đã thực hiện các ca này và xác nhận thao tác thất bại giữ nguyên hai khách hàng, hai đơn và ba dòng ban đầu. Nó cũng chấp nhận một chuỗi SKU lạ, xác nhận cột đó không phải tham chiếu danh mục. Trong hợp đồng lỗi API, hãy phân biệt thiếu hàng cha với trùng định danh; upsert được báo thành công chung chung không thể sửa mọi loại vi phạm ràng buộc.

Tra cứu trước không thay thế được khóa ngoại

Handler có thể hỏi khách hàng tồn tại hay không để trả lỗi dễ hiểu. Đó là phản hồi hữu ích, nhưng không phải lời hứa tồn tại lâu dài. Với mức cô lập mặc định Read Committed của PostgreSQL, select thông thường nhìn ảnh chụp dữ liệu đã commit tại thời điểm câu lệnh bắt đầu. Transaction khác có thể xóa khách hàng chưa được tham chiếu ngay sau đó.

Thí nghiệm hai kết nối dùng khách hàng 74. Kết nối A bắt đầu transaction rồi select khách hàng, nhận được một hàng. Kết nối B sau đó xóa khách hàng và commit. Kết nối A thử thêm đơn 420 cho khách hàng 74. Khóa ngoại từ chối insert với mã 23503; không xuất hiện đơn mồ côi.

Mở ảnh kích thước đầy đủ ↗

Gói select sơ bộ và insert trong một transaction không khiến phép tra cứu cụ thể này giữ chỗ hàng cha. Những cách dùng khóa khác có thể phối hợp quy trình dài hơn, nhưng kéo theo yêu cầu riêng về thứ tự và tranh chấp. Với quan hệ đang xét, hãy giữ ràng buộc cơ sở dữ liệu và xử lý khả năng thất bại tại lần ghi cuối.

Làm rõ hành vi xóa

Xóa khách hàng 73 bị chặn vì vẫn còn đơn tham chiếu tới họ. Xóa đơn 418 khác: các dòng là thành phần của đơn và dùng ON DELETE CASCADE. Xóa dây chuyền đi từ đơn bị xóa tới những dòng tham chiếu nó. Nó không xóa khách hàng của đơn.

Chạy transaction này để quan sát xóa dây chuyền rồi hoàn tác:

BEGIN;
DELETE FROM orders WHERE id = 418 AND state = 'draft' RETURNING id;
SELECT count(*) AS remaining_lines FROM order_items WHERE order_id = 418;
ROLLBACK;
SELECT count(*) AS restored_lines FROM order_items WHERE order_id = 418;

Lệnh delete trả về 418. Số đếm đầu bằng không vì cả hai dòng biến mất trong transaction. Sau rollback, số đếm thứ hai bằng hai. Xóa đơn và xóa dây chuyền các dòng cùng thuộc một transaction cơ sở dữ liệu, phù hợp với hướng dẫn transaction PostgreSQL. Rollback khôi phục các hàng này; nó không thu hồi email hay tác động bên ngoài mà ứng dụng đã tạo.

Chọn hành động tham chiếu theo vòng đời của quan hệ. Xóa dây chuyền từ đơn nháp có thể bỏ đi tới các dòng của nó là hợp lý. Xóa dây chuyền từ khách hàng tới mọi đơn lịch sử có thể xóa nhiều hơn ý định của người dùng. Kiểm tra mọi khóa ngoại phía dưới trước khi bật cascade trong schema lớn, vì một lệnh delete có thể ảnh hưởng nhiều bảng và nhiều hàng.

RESTRICT và NO ACTION mặc định không thay thế nhau trong mọi cấu hình. Lựa chọn sau có thể hỗ trợ kiểm tra ràng buộc trì hoãn khi được cấu hình tương ứng, còn hành động xóa restrict không thể trì hoãn. Ví dụ này khai báo các ràng buộc kiểm tra ngay và không minh họa thứ tự trì hoãn. SET NULL là chính sách khác cho quan hệ tùy chọn, nhưng sẽ xung đột với cột khách hàng bắt buộc ở đây nếu mô hình không đổi.

Toàn vẹn tham chiếu không phải chính sách giữ đơn đã trả tiền

Cột trạng thái giới hạn tên được lưu thành draft hoặc paid. Nó không cấm xóa hàng paid. So sánh lệnh delete có điều kiện với delete trực tiếp, rồi rollback thí nghiệm thứ hai:

DELETE FROM orders WHERE id = 419 AND state = 'draft' RETURNING id;
SELECT count(*) AS paid_lines FROM order_items WHERE order_id = 419;
BEGIN;
DELETE FROM orders WHERE id = 419 RETURNING id;
SELECT count(*) AS paid_lines_after_direct_delete
FROM order_items WHERE order_id = 419;
ROLLBACK;

Lệnh delete đầu không trả về hàng nào, và số dòng của đơn đã thanh toán vẫn bằng một. Tài liệu DELETE của PostgreSQL nêu rõ xóa không hàng nào vẫn là thực thi SQL thành công. Ứng dụng phải xem kết quả; kết quả rỗng không chứng minh thao tác hủy được yêu cầu đã xảy ra.

Delete trực tiếp sau đó trả về 419 và số dòng của nó thành không. Các khóa ngoại vẫn hoàn toàn hợp lệ: xóa cả đơn và dòng không để lại tham chiếu hỏng. Rollback khôi phục dữ liệu ví dụ sau đó. Đây là minh họa có chủ đích về quy tắc nghiệp vụ còn thiếu, không phải cách được khuyên dùng để xóa lịch sử tài chính.

Nếu phải giữ đơn đã thanh toán, hãy ràng buộc mọi đường ghi được phép bằng quyền cơ sở dữ liệu phù hợp, thủ tục được kiểm soát hoặc thiết kế thực thi rõ ràng khác. Điều kiện trạng thái trong một handler không ràng buộc người ghi khác có quyền delete không hạn chế. Tương tự, khóa ngoại không chứng minh người gọi sở hữu khách hàng được tham chiếu. Phân quyền và quy tắc lưu giữ cần ranh giới riêng.

Chọn index mà không lặp lại công việc đã có

PostgreSQL tạo unique B-tree index cho mỗi khóa chính. Nó không tự động tạo index trên mọi cột tham chiếu. Trước lệnh sau, bộ dữ liệu nhỏ chỉ có ba index khóa chính:

CREATE INDEX orders_customer_id_idx ON orders (customer_id);
SELECT indexname FROM pg_indexes
WHERE schemaname = 'public' AND tablename IN ('customers', 'orders', 'order_items')
ORDER BY indexname;

Sau đó kết quả có thêm orders_customer_id_idx. Index này có thể hỗ trợ tra đơn theo khách hàng và tìm các đơn tham chiếu khi hàng cha thay đổi. Giá trị sử dụng phụ thuộc mẫu truy cập và kích thước bảng; ví dụ nhỏ không cung cấp số đo hiệu năng hay yêu cầu phổ quát phải có index này.

Bảng dòng đã có index khóa chính bắt đầu bằng order_id. Hướng dẫn index nhiều cột của PostgreSQL giải thích vì sao điều kiện trên cột đầu có thể dùng B-tree hiệu quả. Đừng tự động thêm index khác trên cùng cột đầu chỉ vì vừa thêm khóa ngoại. Trước hết hãy xem index hiện có và kế hoạch thực thi thực tế.

Với cơ sở dữ liệu đã có dữ liệu, migration còn cần kiểm kê tham chiếu mồ côi, định danh trùng, giá trị null và kế hoạch phối hợp người ghi đồng thời. Bài tập tạo schema mới này không xác lập quy trình migration trực tuyến an toàn. Giữ định danh ổn định tách khỏi nhãn có thể đổi: thí nghiệm đổi tên hiển thị của khách hàng 73 trong khi cả hai tham chiếu đơn vẫn là 73.

Ở bài tập cuối, commit việc xóa đơn nháp rồi kiểm tra cả ba bảng. Chỉ đơn đã thanh toán và dòng duy nhất của nó còn lại, cùng khách hàng vẫn tồn tại. Xóa khách hàng đó vẫn phải thất bại. Schema đang làm công việc hữu ích khi từng thao tác được chấp nhận, bị từ chối và xóa dây chuyền khớp với một quan hệ bạn giải thích được, và khi bạn nêu rõ các quy tắc nghiệp vụ mà nó chưa thực thi.