Ràng buộc khóa ngoại và hành động tham chiếu (ON DELETE / ON UPDATE)

Dữ liệu sẽ dùng — category và item

Một ràng buộc khóa ngoại (ràng buộc đảm bảo giá trị của một cột luôn tồn tại làm khóa chính ở bảng khác) là cơ chế bảo vệ tính toàn vẹn tham chiếu giữa các bảng (trạng thái mà mọi dòng được tham chiếu đều thực sự tồn tại).

Nó chặn các mâu thuẫn như "một item trỏ tới một category không tồn tại" hoặc "xóa một category để lại các item của nó treo lơ lửng", ngay ở lớp cơ sở dữ liệu.

Trước khi vào bài tập, hãy xem định nghĩa cột và dữ liệu mẫu của hai bảng — category và item.

① Chạy PRAGMA table_info(category); và PRAGMA table_info(item); để kiểm tra định nghĩa cột.

② Xem trước mọi dòng với SELECT * FROM category; và SELECT * FROM item;.

③ Chạy PRAGMA foreign_keys; để xem trạng thái kiểm tra khóa ngoại hiện tại (trả về dưới dạng một số).

Kiểm tra khóa ngoại được bật bằng PRAGMA foreign_keys=ON

Ràng buộc khóa ngoại được khai báo bằng cách viết REFERENCES bảng_cha(cột_cha) trong định nghĩa cột của bảng con.

item.cat_id mang REFERENCES category(cat_id), mã hóa quy tắc: mọi giá trị item.cat_id phải tồn tại trong category.

Tuy nhiên, trong console của khóa học này, kiểm tra khóa ngoại mặc định là tắt (PRAGMA foreign_keys bằng 0).

Khi tắt, `REFERENCES` không thực sự được thực thi và các dòng trỏ tới một category không tồn tại vẫn có thể được chèn.

Để thực sự thực thi ràng buộc, chạy PRAGMA foreign_keys=ON; một lần cho mỗi kết nối.

-- Bật kiểm tra khóa ngoại
PRAGMA foreign_keys=ON;

-- Xác nhận trạng thái (1 nghĩa là đã bật)
PRAGMA foreign_keys;

-- Cố chèn một dòng trỏ tới cat_id=99 (không có trong category)
-- Với foreign_keys=ON, lệnh này bị từ chối với FOREIGN KEY constraint failed
INSERT INTO item VALUES (5,'Mouse',99);

Hãy hình dung yêu cầu: "để cơ sở dữ liệu chặn các sai sót đăng ký mà item tham chiếu tới một category không tồn tại." Với kiểm tra khóa ngoại đã bật, bạn sẽ cố ý chèn một dòng không nhất quán và xem nó báo lỗi. (Chạy đúng thì phần giải thích sẽ hiện ra.)

① Ở dòng 1, chạy PRAGMA foreign_keys=ON; để bật kiểm tra khóa ngoại.

② Sau đó INSERT một dòng vào item với item_id 5, item_name Mouse, và cat_id 99 — một giá trị không tồn tại trong category. INSERT này vi phạm ràng buộc khóa ngoại, nên lỗi là kết quả đúng.

ON DELETE — làm gì với dòng con khi một dòng cha bị xóa

Khi một dòng cha (category) bị xóa, các dòng con (item) đã trỏ tới nó nên xử lý thế nào? Bạn quyết định bằng cách viết một hành động tham chiếu trong mệnh đề REFERENCES.

Có ba hành động tham chiếu phổ biến.

ON DELETE CASCADE nghĩa là xóa cha cũng xóa các con (xóa dây chuyền), ON DELETE SET NULL nghĩa là cột tham chiếu của con được đặt thành NULL và dòng được giữ lại, và ON DELETE RESTRICT nghĩa là bản thân việc xóa cha bị từ chối khi các con vẫn còn tham chiếu.

Nếu bạn không chỉ định hành động, mặc định cũng giống RESTRICT (một cha được tham chiếu không thể bị xóa).

Hành vi nào phù hợp tùy thuộc vào yêu cầu nghiệp vụ.

-- Ví dụ khai báo với ON DELETE SET NULL
CREATE TABLE cat_demo(cat_id INTEGER PRIMARY KEY, cat_name TEXT NOT NULL);
CREATE TABLE item_demo(
  item_id INTEGER PRIMARY KEY,
  item_name TEXT NOT NULL,
  cat_id INTEGER REFERENCES cat_demo(cat_id) ON DELETE SET NULL
);
INSERT INTO cat_demo VALUES (1,'Stationery'),(2,'Electronics');
INSERT INTO item_demo VALUES (1,'Pen',1),(2,'Phone',2),(3,'Cable',2);

-- Xóa cha cat_id=2 biến cat_id của Phone / Cable thành NULL
DELETE FROM cat_demo WHERE cat_id=2;
SELECT item_id, item_name, cat_id FROM item_demo ORDER BY item_id;

Hãy hình dung yêu cầu: "khi một category bị xóa, cũng xóa mọi item thuộc về nó." Dựng một cặp cha/con tự chứa với ON DELETE CASCADE và xem việc xóa cha lan ra các con.

① Bật kiểm tra khóa ngoại, rồi DROP TABLE IF EXISTS và tạo lại cat_x (cat_id, cat_name) và item_x (item_id, item_name, cat_id) trong đó item_x.cat_id có khóa ngoại ON DELETE CASCADE vào cat_x.

② Chèn 2 dòng vào cat_x (Stationery / Electronics) và 3 dòng vào item_x (một dòng với cat_id 1, hai dòng với cat_id 2).

③ Xóa category có cat_id 2 khỏi cat_x, rồi SELECT mọi dòng từ item_x và xác nhận các item trỏ tới cat_id=2 đã bị xóa dây chuyền.

SET NULL và RESTRICT — chọn giữa giữ và từ chối

ON DELETE SET NULL giữ dòng con khi cha bị xóa, chỉ thay cột tham chiếu bằng NULL.

Dùng nó khi bạn không muốn xóa bản ghi con, như "giữ item là 'chưa phân loại'."

Nếu cột tham chiếu của con là NOT NULL, không thể ghi NULL, nên các cột dùng với SET NULL phải cho phép NULL.

ON DELETE RESTRICT từ chối bản thân việc xóa cha khi các con vẫn còn tham chiếu.

Dùng nó khi bạn muốn fail-safe (đừng vô tình loại một dòng master còn được tham chiếu).

Khi bạn đã xóa hoặc gán lại mọi con để không còn tham chiếu, cha có thể bị xóa.

Ở bài tập tiếp theo, bạn sẽ thiết lập SET NULL và RESTRICT cạnh nhau trong một console để so sánh.

-- Ví dụ RESTRICT: cha có con không thể bị xóa
CREATE TABLE cat_r(cat_id INTEGER PRIMARY KEY, cat_name TEXT NOT NULL);
CREATE TABLE item_r(
  item_id INTEGER PRIMARY KEY,
  item_name TEXT NOT NULL,
  cat_id INTEGER REFERENCES cat_r(cat_id) ON DELETE RESTRICT
);
INSERT INTO cat_r VALUES (1,'Stationery'),(2,'Electronics');
INSERT INTO item_r VALUES (1,'Pen',1);

-- cat_id=1 được item_r tham chiếu, nên việc xóa bị từ chối
DELETE FROM cat_r WHERE cat_id=1;

-- cat_id=2 không có tham chiếu, nên có thể bị xóa
DELETE FROM cat_r WHERE cat_id=2;
SELECT * FROM cat_r ORDER BY cat_id;

Hãy hình dung yêu cầu: "nếu chúng ta ngừng một category, hãy giữ các item của nó là 'chưa phân loại' nhưng không động chút nào đến item ở các category khác." Với 3 category và 5 item dưới ON DELETE SET NULL, bạn sẽ thấy chỉ con của cha bị ngừng thành NULL còn các con khác không bị động.

① Bật kiểm tra khóa ngoại, rồi DROP TABLE IF EXISTS và tạo lại cat_s (cat_id, cat_name) và item_s (item_id, item_name, cat_id) trong đó item_s.cat_id có khóa ngoại ON DELETE SET NULL vào cat_s. item_s.cat_id phải cho phép NULL.

② Chèn 3 dòng vào cat_s (Office=1 / Gadget=2 / Drink=3) và 5 dòng vào item_s (Marker và Tape trong Office, Tablet và Charger trong Gadget, Coffee trong Drink).

③ Chỉ xóa Gadget (cat_id=2) khỏi cat_s, rồi SELECT mọi dòng từ item_s và xác nhận chỉ cat_id của Tablet và Charger thành NULL còn các item dưới Office / Drink giữ cat_id ban đầu — cả 5 dòng vẫn còn.

Hãy hình dung yêu cầu: "ngăn chặn việc vô tình xóa một category vẫn còn nhiều item thuộc về nó." Dựng một cặp cha/con tự chứa với ON DELETE RESTRICT và xem nó báo lỗi khi bạn cố xóa một cha có 3 con.

① Bật kiểm tra khóa ngoại, rồi DROP TABLE IF EXISTS và tạo lại cat_t (cat_id, cat_name) và item_t (item_id, item_name, cat_id) trong đó item_t.cat_id có khóa ngoại ON DELETE RESTRICT vào cat_t.

② Chèn Stationery (cat_id 1) vào cat_t, và 3 item dưới nó (ví dụ Pen / Pencil / Notebook) vào item_t.

③ Cố DELETE category có cat_id 1 khỏi cat_t. Vì 3 con vẫn đang tham chiếu nó, lỗi là kết quả đúng.